Как сделать формулы в excel с разных листов?

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

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

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

Практически каждый из нас при работе в 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

В этом курсе:

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Читать еще:  Как сделать файл excel с паролем?

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Создание формулы, ссылающейся на значения в других ячейках

Введите знак равенства «=».

Примечание: Формулы в Excel начинаются со знака равенства.

Выберите ячейку или введите ее адрес в выделенной.

Введите оператор. Например, для вычитания введите знак «минус».

Выберите следующую ячейку или введите ее адрес в выделенной.

Нажмите клавишу ВВОД. В ячейке с формулой отобразится результат вычисления.

Просмотр формулы

При вводе в ячейку формула также отображается в строке формул.

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

Ввод формулы, содержащей встроенную функцию

Выделите пустую ячейку.

Введите знак равенства «=», а затем — функцию. Например, чтобы получить общий объем продаж, нужно ввести «=СУММ».

Введите открывающую круглую скобку «(«.

Выделите диапазон ячеек, а затем введите закрывающую круглую скобку «)».

Нажмите клавишу ВВОД, чтобы получить результат.

Скачивание книги «Учебник по формулам»

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

Подробные сведения о формулах

Чтобы узнать больше об определенных элементах формулы, просмотрите соответствующие разделы ниже.

Формула также может содержать один или несколько таких элементов, как функции, ссылки, операторы и константы.

1. Функции. Функция ПИ() возвращает значение числа пи: 3,142.

2. Ссылки. A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы. Оператор ^ (крышка) применяется для возведения числа в степень, а * (звездочка) — для умножения.

Константа представляет собой готовое (не вычисляемое) значение, которое всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. выражение или его значение константами не являются. Если формула в ячейке содержит константы, а не ссылки на другие ячейки (например, имеет вид =30+70+110), значение в такой ячейке изменяется только после редактирования формулы. Обычно лучше помещать такие константы в отдельные ячейки, где их можно будет легко изменить при необходимости, а в формулах использовать ссылки на эти ячейки.

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

Стиль ссылок A1

По умолчанию Excel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, не более 16 384 столбцов), а строки — номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ячейка или диапазон

Ячейка на пересечении столбца A и строки 10

Диапазон ячеек: столбец А, строки 10-20.

Диапазон ячеек: строка 15, столбцы B-E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек: столбцы А-E, строки 10-20

Создание ссылки на ячейку или диапазон ячеек с другого листа в той же книге

В приведенном ниже примере функция СРЗНАЧ вычисляет среднее значение в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек от B1 до B10

3. Восклицательный знак (!) отделяет ссылку на лист от ссылки на диапазон ячеек.

Примечание: Если название упоминаемого листа содержит пробелы или цифры, его нужно заключить в апострофы (‘), например так: ‘123’!A1 или =’Прибыль за январь’!A1.

Различия между абсолютными, относительными и смешанными ссылками

Относительные ссылки . Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.

Скопированная формула с относительной ссылкой

Абсолютные ссылки . Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.

Скопированная формула с абсолютной ссылкой

Смешанные ссылки Смешанная ссылка содержит абсолютный столбец и относительную строку, а также абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет форму $A 1, $B 1 и т. д. Абсолютная ссылка на строку имеет форму $1, B $1 и т. д. При изменении положения ячейки, содержащей формулу, относительная ссылка будет изменена, а абсолютная ссылка не изменится. Если вы копируете или заполните формулу в строках или столбцах, относительная ссылка автоматически корректируется, а абсолютная ссылка не изменяется. Например, при копировании и заполнении смешанной ссылки из ячейки a2 в ячейку B3 она корректируется с = A $1 на = B $1.

Читать еще:  Как сделать раскрывающийся список в excel 2003?

Скопированная формула со смешанной ссылкой

Стиль трехмерных ссылок

Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. В Microsoft Excel используются все листы, указанные между начальным и конечным именами в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.

Трехмерные ссылки нельзя использовать в формулах массива.

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

Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

Вставка или копирование. Если вставить листы между листами 2 и 6, Microsoft Excel прибавит к сумме содержимое ячеек с A2 по A5 на новых листах.

Удаление . Если удалить листы между листами 2 и 6, Microsoft Excel не будет использовать их значения в вычислениях.

Перемещение . Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.

Перемещение конечного листа . Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Удаление конечного листа . Если удалить лист 2 или 6, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Стиль ссылок R1C1

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. При использовании стиля R1C1 в Microsoft Excel положение ячейки обозначается буквой R, за которой следует номер строки, и буквой C, за которой следует номер столбца.

Функция ЛИСТЫ и формулы для работы с другими листами в 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. Нам необходимо вычислить точное число этих документов, так как каждый из них имеет свое название, то для того чтобы визуально вычислить их количество потребуется время.

Читать еще:  Как сделать из горизонтальной таблицы excel вертикальную?

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

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

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

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

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

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

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

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

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

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

Задача: сделать сводную таблицу с данными всех 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 (ДВССЫЛ), а не наоборот.

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