Как сделать формулу в excel с одного листа на другой?

Функция ЛИСТЫ и формулы для работы с другими листами в Excel

Функция ЛИСТ предназначена для возвращения номера конкретного листа с промежутком, который открывает доступ ко всей рабочей книге в MS Excel. Функция ЛИСТЫ предоставляет пользователю информацию о количестве листов, содержащихся в рабочей книге.

Формулы с использованием ссылок на другие листы Excel

Предположим у нас имеется фирма DecArt в которой работают сотрудники и им ежемесячно начисляется зарплата. У данной фирмы имеются сведения о среднемесячной зарплате в Excel, а данные по ней размещены на разных листах: на листе 1 размещены данные о зарплате, на листе 2 премия в процентах. Нам необходимо вычислить размер премии в рублях, при том чтобы данные эти были размещены на втором листе.

Для начала рассмотрим пример работы с листами в формулах Excel. Пример 1:

  1. Создадим на листе 1 рабочей книги табличного процессора Excel таблицу, как показано на рисунке. Сведения о среднемесячной заработной плате:
  2. Далее на листе 2 рабочей книги подготовим область для размещения нашего результата — размера нашей премии в рублях, как показано на рисунке:
  3. Далее нам необходимо будет ввести в ячейку В2 формулу, изображенную на рисунке ниже:

Ввод данной формулы происходил следующим образом: для начала в ячейке В2 мы установили знак «=», затем кликнули на «Лист1» в нижнем левом углу рабочей книги и перешли на ячейку C3 на листе 1, далее ввели операцию умножения и перешли вновь на «Лист2» для того чтобы добавить процент.

Таким образом мы получили при расчете премии каждого сотрудника, причем исходные данные находились на одном листе, а расчет был произведен на другом листе. Данная формула окажется весьма полезной при работе с более длинными массивами данных в крупных организациях.

Функция ЛИСТЫ для подсчета количества листов в рабочей книге

Рассмотрим теперь пример работы функции ЛИСТЫ. Довольно часто бывает так, что в рабочей книге Excel расположено слишком много листов. Выяснить их точное количество визуально не представляется возможным, именно с этой целью и создана функция ЛИСТЫ.

В данной функции всего 1 аргумент – «Ссылка» да и то необязательный для заполнения. Если его не заполнять тогда функция возвращает общее количество листов, созданных в текущей рабочей книге файла Excel. При необходимости можно заполнить аргумент. Для этого в нем необходимо указать ссылку на рабочую книгу, в которой необходимо подсчитать общее количество листов, созданных в ней.

Пример2. Предположим у нас имеется фирма по производству мягкой мебели, и у нее есть множество документов, которые содержатся в рабочей книге Excel. Нам необходимо вычислить точное число этих документов, так как каждый из них имеет свое название, то для того чтобы визуально вычислить их количество потребуется время.

На рисунке ниже показано примерное количество листов:

Чтобы организовать подсчет всех листов, необходимо воспользоваться функцией ЛИСТЫ. Просто ставим знак равенства «=» и вписываем функцию, не заполняя ее аргументов в скобках. Вызов данной функции показан ниже на рисунке:

В результате получим следующее значение: 12 листов.

Таким образом мы узнали, что в нашей фирме имеется 12 документов, содержащихся в рабочей книге Excel. Этот простой пример наглядно иллюстрирует работу функции ЛИСТЫ. Данная функция может стать полезной для руководителей, офисных сотрудников, менеджеров по продажам.

Ссылки на другие листы в шаблонах документов

Пример 3. Имеются данные о расходах на банкет компании занимающейся выездным обслуживанием. Необходимо произвести расчет общей стоимости банкета, а также общий выход порций блюд, и вычислить общее количество листов в документе.

  1. Создадим таблицу «Банкетное меню», общий вид которой представлен на рисунке ниже:
  2. Аналогичным образом создадим таблицы на разных листах «Общая стоимость» и «Общий выход»:
  3. При помощи формулы со ссылками на другие листы произведем расчет общей стоимости банкетного меню:
  4. Перейдем на лист «Общий выход» и путем умножения ячеек веса одной порции, находящегося на листе 2 и общего количества, находящегося на листе 1 произведем расчет общего выхода:

В результате у нас получился простейший шаблон для подсчета расходов на 1 банкет.

Excel At Excel вып.3: Собираем данные с разных листов

Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.

Читать еще:  Формат по образцу excel как сделать

Задача: сделать сводную таблицу с данными всех 22 таблиц

Итак, решение. Есть три варианта решения данной задачи. Первый — использовать встроенную (начиная с excel 2013) надстройку PowerPivot (об этом методе мы поговорим позднее). Второй — посредством сводной таблицы через несколько диапазонов консолидации. Не люблю этот метод, т.к. такая сводная таблица имеет существенно более ограниченный функционал по сравнению с простой сводной таблицей. Наконец, третий вариант — это создать общую таблицу с использованием функции INDIRECT (ДВССЫЛ). Этому методу и посвятим сегодня наш пост.

Что мы имеем? 22 одинаковых по формату таблицы на отдельных листах и с разным количеством строк в каждой. Соответственно, для построения общей таблицы нам надо решить следующие «проблемы»:

1) как заставить общую таблицу «переключаться» с одного листа данных на другой?;

2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?

Начнем со второго вопроса, т.к. ответ на него мы уже знаем. Это циклы, о которых мы подробно говорили в выпуске 2 «Циклы в Excel без VBA». Для решения проблемы достаточно в наш файл добавить лист с перечнем всех обществ, соответствующими названиями листов и количеством подсчетом количества строк на каждом таком листе.

Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для «линковки» каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.

Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.

Функция имеет, по сути, единственный параметр — ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, «А1» в ссылку и возвращает значение ячейки А1.

Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).

Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:

Чтобы понять, что означает текст внутри нее, давайте вспомним, как выглядит в Excel ссылка на ячейку на другом листе. Вот так:

Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для «автоматизации» формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:

где все, что подчеркнуто — текст, а выделено жирным — ссылки.

ВАЖНО! Обратите внимание на кавычки и конкатенацию («склеивание») при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &.

Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.

Перейдем к формированию общей таблицы. Как вы уже наверное прикинули, у нас будет два вложенных цикла: один — количество обществ, второй — количество записей на листе данных по соответствующему обществу. Соответственно, нам потребуются два вспомогательных столбца (определим их в столбцы А и В). Ровно так же, как мы делали на примере в выпуске 2 «Циклы в Excel без VBA», пишем формулу первого цикла:

=IF(A2=»»;»»;IF(COUNTIF($A$2:A2;A2)=OFFSET(‘Список обществ’!$E$2;A2-1;0);IF(A2+1>COUNTA(‘Список обществ’!A:A)-1;»»;A2+1);A2))

Затем пишем формулу второго цикла:

Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.

Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).

OFFSET (СМЕЩ) передает в формулу название листа, а конструкция B2+1 — номер строки в столбце Е на том листе.

ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот.

Как посчитать сумму с разных листов в Excel?

Изучим возможности для суммирования данных из ячеек с разных листов книги с помощью использования трехмерных ссылок в Excel.

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

Практически каждый из нас при работе в Excel хотя бы раз наверняка применял операцию сложения с помощью оператора плюс «+» и различных функций (СУММ, СУММЕСЛИ, СУММЕСЛИМН и т.п.).

В связи с этим часто можно столкнуться с задачей, когда нужно просуммировать данные из одних и тех же ячеек с нескольких листов. К примеру, подвести общий итог для всех филиалов или отделов компании (коих может быть очень много), посчитать сумму за год по всем месяцам, где каждая часть с данными расположена на отдельном листе книги.

Читать еще:  Как сделать технологическую карту в excel?

К решению этой задачи можно подходить по-разному, рассмотрим несколько вариантов как мы это можно сделать — от медленного и ручного до быстрого и полуавтоматического.

Как сложить листы в Excel?

Вариант 1. Ручной ввод.

Начнем с одной стороны с медленного варианта, но с другой стороны с более понятного и наглядного способа.

Для примера возьмем простую задачу и сложим данные ячейки A1 с трех листов: Лист1, Лист2 и Лист3. Для этого поочередно в формулу записываем ссылки на ячейку с разных листов и знаки сложения «+»:

Это совсем простой вариант. Еще один вариант записи можно аналогично реализовать с помощью функции СУММ:

В реализуемых нами вариантах в общем и целом все хорошо, за исключением большой ручной работы при прощелкивании всех нужных для суммирования ячеек.

Как это часто бывает если нужно посчитать сумму для двух или трех листов, то вышеописанный способ отлично подойдет. Но если усложнить задачу и представить подобный пример не для 3, а, например, для 30 листов, то процесс уже несколько затянется.

Поэтому перейдем к чуть более сложному на первый взгляд варианту, но гораздо более быстрому с точки зрения трудовых и временных затрат.

Вариант 2. Полуавтоматический ввод.

Повторно воспользуемся функцией СУММ, но в этот раз запишем ссылку на листы через двоеточие:

В этом случае общее количество суммируемых листов для нас уже не так важно, главное знать только начальный и конечный.

Поэтому запись для сложения 10 или 20 листов будет примерно такой же — ИмяПервогоЛиста:ИмяПоследнегоЛиста!Диапазон.

Чуть более подробно остановимся на логике формирования формулы. Напомню, что оператор двоеточие в Excel служит для объединения диапазона, который образует ссылку на все ячейки находящиеся между первой и последней (включая сами ячейки). Например, диапазон A1:A10 обозначает двумерную ссылку, в которую попадают все ячейки между первой (A1) и последней (A10).

При добавлении еще одной размерности в виде листов мы получаем так называемую трехмерную ссылку. К примеру, диапазон Лист1:Лист3!A1:A10 содержит все ячейки от Лист1!A1:A10 до Лист3!A1:A10.

Таким образом в нашем примере суммируются все ячейки A1 от Лист1 до Лист3. Но при применении такого способа суммирования есть некоторые тонкости, которые желательно нужно помнить, чтобы случайно не допустить ошибку, давайте подробнее про них поговорим.

Особенности трехмерных ссылок

Как мы уже говорили ранее, при применении трехмерных ссылок нужен только первый и последний лист, промежуточные листы между ними в записи никак не участвуют. Отсюда и возникают следующие тонкости:

  • Добавление/копирование листа. Если добавить новый лист или скопировать уже существующий и вставить его между первым и последним (используемых в формуле), то он автоматически будет участвовать в расчете.
    Т.е. сама формула не поменяется, а значение вполне может измениться, если на новом листе в тех же ячейках содержатся данные;
  • Удаление листа. Аналогичный случай примеру выше. При удалении листа формула не изменится, а расчет поменяется в зависимости от удаленных данных (если удалить начальный или конечный листы, то формула изменится, промежуточный — формула останется неизменной);
  • Перемещение листа. При перемещении листа в другое место книги формула не изменится, но поменяется набор листов между ними (либо добавятся новые, либо удалятся текущие).

Если описание покажется несколько сложным, в этом случае попробуйте поэкспериментировать с формулой и перестановкой листов на конкретном примере и поймите по какому принципу она работает.

Использование трехмерных ссылок не ограничивается только функциями для сложения данных, а можно использовать большинство математических функций. Поэтому если стоит аналогичная задача для нахождения, к примеру, среднего арифметического или наибольшего с наименьшим, то без проблем применяйте.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите и спрашивайте в комментариях.

Excel. Урок 11. Работа с несколькими листами: однотипные данные, формулы

Предположим, некоторая компания реализует свой товар через 3 магазина. Для каждого из магазинов известна сумма выручки по кварталам. И известны статьи расхода. Допустим, требуется определить суммарные затраты и прибыль. Я не использую в своих уроках реальные бухгалтерские документы, я лишь рассматриваю разные приемы работы. Особенность данной задачи состоит в том, что для каждого из трех магазинов необходимо выполнить однотипные действия и при этом удобно для каждого магазина иметь отдельный рабочий лист.

1:20 Добавление листа

В рабочей книге три рабочих листа. Добавим новый лист для итога. Можно сделать это нажатием на ярлычок вставки листа (рядом с ярлычками существующих листов).

1:52 Переименование листа

Двойной щелчок по имени листа. Имя листа выделилось, можно вводить новое название. (Другой способ — правый щелчок по имени листа — команда «Переименовать«). Называем листы:

Читать еще:  Округление чисел в excel как сделать

«Итог», «Магазин 3», «Магазин 2», «Магазин 1»

2:50 Перемещение листов

Я намеренно перепутала порядок названий листов, чтобы показать как можно легко изменить порядок листов. Хватаем и перемещаем ярлык туда где хотим его видеть. Получаем правильный порядок:

«Магазин 1», «Магазин 2», «Магазин 3», «Итог»

3:18 Приемы работы с листами

Сразу рассмотрим все приемы работы с листами. Листы можно удалять, если выбрать соответствующую команду из контекстного меню (по правому щелчку). Можно поменять цвет ярлычка. Если окажется что листов слишком много, они могут не помещаться в области отображения листов. Можно расширить эту область, ухватив за ее границу. Если листов совсем уж много, есть кнопки для проматывания списка листов, хотя излишне перегружать книгу листами не стоит.

4:16 Работа со всеми листами одновременно

Четыре листа у нас есть. Теперь приступим к заполнению листов однотипной информацией. Можно воспользоваться обычным приемом копирования вставки, а можно все сделать еще быстрее. Выделяем все листы — щелкаем правой кнопкой и даем команду «Выделить все листы». Все ярлычки листов стали светлыми, и активны все 4 листа. Начинаем вводить данные сразу во все таблицы (вводим на одном листе, на других они дублируются).

В А1 пишем «Магазин № 1».

В B1 пишем: «Данные по кварталам».

5:23

Кварталы можно заполнять при помощи автозаполнения. Вводим кв. 1, хватаем за правый нижний угол тащим это еще один из списков автозаполнения.

Вводим боковые заголовки в столбце 1:


6:28 Улучшаем внешний вид

Изменяем размер столбца. Заголовок в строке 1 увеличиваем и центрируем. Заголовок в строке 2 центрируем. Заголовки столбцов и первый стлбец таблицы делаем полужирными.

7:11 Добавляем заливку ячеек, чтобы таблица лучше воспринималась.

7:35 Очерчиваем границы

7:50

Сейчас все таблицы заполнены одинаковыми данными, и необходимо подредактировать каждый лист. Перемещаемся в область ярлычков. Нажимаем правую кнопку и выбираем команду «Разгруппировать листы«. Ярлычки приняли прежний цвет. Перемещаясь между листами, редактируем каждый из них. В заголовочных строках меняем номер магазина и в итоговом листе «Магазин № 1» заменяем на «Итог».

8:45 Можно было бы группировать листы несколько иным способом. Выделяем первый лист зажимаем клавишу Ctrl, щелкаем на второй лист и третий лист. В данном случае я выделяю не все листы а лишь часть из них (лист «Итог» пока не выделяем).

9:20 в выделенных листах вводим формулы. Здесь пока нет исходных данных но как только появятся числа, появится результат. В В8 выполняем автосуммирование.

Подстановка данных в автосумму не сработала, потому что нет чисел, и поэтому получилась пустая скобка. Указываем с помощью выделения с нажатой кнопкой диапазон В5:В6.

Выполняю автозаполнение вправо до столбца Е включительно.

Прибыль это выручка от продаж минус суммарные затраты.

= B4-B8

10:32

Определим эти данные как финансовые (выделим и нажмем на кнопку «Финансовый числовой формат» на ленте) Теперь листы можно разгруппировать и вводить данные и сразу будем получать результат вычисления по формулам.

11:00

Данные введены по всем магазинам введены числовые данные и как только они появились сразу же в ячейках, где записаны формулы, появились результаты.

11:20 Переходим на лист «Итог» и подсчитаем сумму по 3-м магазинам по каждому кварталу.

Активируем ячейку В4 вводим знак «=» (начало формулы) и переходим на ярлычок первого магазина. Щелчок по ячейке В4 на этом листе. В строке формул появляется имя ячейки, которому предшествует название листа

Отсутствие наглядности при работе с несколькими листами иногда сбивает. Поэтому смотрите на строку формул. Жмем на знак «+», переходим на следующий лист «Магазин 2» жмем на ячейку B4. Опять жмем на «+», переходим на лист «Магазин 3», Нажимаем на B4 и затем на Enter. Никаких лишних кликов мышью ни в коем случае совершать нельзя!

=’Магазин 1′!B4+’Магазин 2′!B4+’Магазин 3′!B4

Если по ней дважды кликнуть можно увидеть формулу, но возможности увидеть входящие в нее ячейки так как они находятся на других листах.

13:17 Поскольку ссылки в формуле относительные, можно выполнять автозаполнение вправо и вниз. если не знаете про абсолютные и относительные ссылки смотрите в предыдущих уроках.

14:10

Результат формулы не помещается в ячейку числа отображаютс #####

Двойной щелчок по границе между ячейками и формула приобретает удобочитаемый вид.

Единственное что произошло это сбился формат, и нужно еще раз восстановить цвет ячеек.

Теперь вы умеете работать с несколькими листами, выполнять расчеты на листах, используя данные хранящиеся на других листах. Точно так же можно использовать данные из различных рабочих книг. Но постарайтесь чтобы все книги хранились в одной папке, чтобы не запутаться.

Ссылка на основную публикацию
Adblock
detector