Сумм(янв:март. D12).

С помощью первой формулы вычисляется произведение чи­сел, содержащихся в ячейке ВЗ текущего рабочего листа и ячейке С3 листа «Лист2». Между именем рабочего листа и адресом ячей­ки ставится восклицательный знак.

Во второй и третьей формулах вычисляется сумма чисел, нахо­дящихся в ячейках D12 на рабочих листах «Янв», «Февр», «Март».

Например, формула =СУММ(Лист1: Лист3! $A$1) суммирует содержимое ячеек А1 в трех подряд идущих листах одной рабочей книги.

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

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

Решение. Переименуем рабочие листы «Лист1», «Лист2», «Лист3» и «Лист4» соответственно в «Янв», «Февр», «Март», «Итого» с помощью контекстно-зависимого меню.

Выделим рабочие листы «Янв», «Февр», «Март», «Итого» с по­мощью мыши и клавиши «Shift». При этом ярлычки выделенных рабочих листов станут белыми. Это свидетельствует о том, что мы включили групповой режим работы. Все, что мы будем набирать на рабочем листе «Янв», автоматически будет набираться на всех выделенных рабочих листах. В целях экономии времени имеет смысл в групповом режиме создать таблицу «Ведомость» с общи­ми для всех выделенных рабочих листов элементами. Это прежде всего заголовок, сведения о минимальной зарплате, список работников, шапка таблицы, формулы подсчета итоговых сумм по столбцам, а для первых трех таблиц и формулы для расчета отчис­лений в пенсионный фонд и текущего облагаемого дохода.

В колонку «Пенсионный фонд» (ячейка D4) занесем формулу = С4*0, 01 и размножим ее в ячейки D5: D6 в таблице, представленной на рис. 10.1.

Рис. 10.1. Рабочий лист с формулами за январь

В ячейку Е4 («Текущий облагаемый доход») внесем формулу =C4-D4-$D$2 и размножим ее в ячейки Е5: Е6.

В ячейках C7: F7 вычислим суммы. В ячейках A3: F6 установим сетку.

Отменим выделение группы. Для этого достаточно щелкнуть мышью по невыделенному листу или с помощью контекстно-за­висимого меню «Разгруппировать листы». Обратите внимание, что таблица одновременно создавалась на всех выделенных рабо­чих листах.

Запишем формулу для облагаемого дохода с начала года. В ян­варе она равна текущему облагаемому доходу, т.е. в клетке F4 за­пишем формулу =Е4, в клетке F5 – формулу =Е5 и т. д.

В результате получим данные об облагаемом доходе за январь (рис. 10.2).

Рис. 10.2. Рабочий лист со значениями за январь

В феврале облагаемый доход равен текущему облагаемому до­ходу плюс облагаемый доход за январь. Формула в феврале в клет­ке F4 будет иметь вид =Е4+Янв! Е4. Скопируем данную формулу в диапазон F5: F6 (рис. 10.3). В результате получим данные об облагаемом доходе за февраль (рис. 10.4).

В марте аналогичная формула в клетке F4 будет иметь вид =Е4+СУММ(Янв: Март! Е4) (рис. 10.5). Результаты вычислений за март представлены на рис. 10.6.

Рис. 10.3. Рабочий лист с формулами за февраль

Рис. 10.4. Рабочий лист со значениями за февраль

Рис. 10.5. Рабочий лист с формулами за март (столбец D спрятан)

Рис. 10.6. Рабочий лист со значениями за март

В рабочем листе «Итого» запишем формулы для подсчета ито­говых сумм (рис. 10.7):

– по колонке «Начислено» – =СУММ(Янв: Март! С4);

– по колонке «Пенсионный фонд» – =СУММ(Янв: Март! D4);

– по колонке «Облагаемый доход с начала года» – =СУММ
(Янв: Март! Е4).

Рис. 10.7. Рабочий лист «Итого» с формулами (столбец В спрятан)

Размножим данные формулы. Результаты вычислений в рабочем листе «Итого» представлены на рис. 10.8.

Связывание рабочих листов можно осуществлять тремя спосо­бами:

– с помощью формул, прямо осуществляя их написание;

– с помощью копирования и специальной вставки с использова­нием кнопки «Вставить ссылку»;

– с помощью консолидации рабочих листов.

Рис. 10.8. Рабочий лист «Итого» со значениями

Консолидация рабочих листов – мощное средство для получения различных отчетов путем использования данных, находящихся в различных рабочих листах и даже файлах. С помощью консолидации можно вычислять итоговые суммы, среднее арифметическое и выпол­нять другие функции.

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

На вкладке Данные в группе Работа с данными выберите команду Консолидация (рис. 10.9, 10.10).

Рис. 10.9. Окно «Консолидация»

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

Рис. 10.10. Диалоговое окно «Консолидация»

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

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

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

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