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

Янв!Б12+ФеврD12+Март!В12

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

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

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

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

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

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

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

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

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов		=С4*0,01	=С4-D4-$D$2	=E4
		Петров		=С5*0,01	=С5-D5-$D$2	=E5
		Сидоров		=С5*0,01	=С6-D6-$D$2	=E6
			=СУММ (С4:С6)	=СУММ (D4:D6)	=СУММ (E4:E6)	=СУММ (F4:F6)

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

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

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

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

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

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

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов			1412,5	1412,5
		Петров			1709,5	1709,5
		Сидоров			1907,5	1907,5
					5029,5	5029,5

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

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

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

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов		=С4*0,01	=С4-D4-$D$2	=E4+Янв!Е4
		Петров		=С5*0,01	=С5-D5-$D$2	=E5+Янв!Е5
		Сидоров		=С5*0,01	=С6-D6-$D$2	=E6+Янв!Е6
			=СУММ (С4:С6)	=СУММ (D4:D6)	=СУММ (E4:E6)	=СУММ (F4:F6)

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

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов			917,5
		Петров			1115,5
		Сидоров			1313,5
					3346,5

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

	А	В	С	Е	F
		Минимальный оклад
	№	Ф.И.О.	Начислено	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов		=С4-D4-$D$2	=E4+СУММ (Янв:Февр!Е4)
		Петров		=С5-D5-$D$2	=E5+ СУММ (Янв:Февр!Е5)
		Сидоров		=С6-D6-$D$2	=E6+ СУММ (Янв:Февр!Е6)
			=СУММ(С4:С6)	=СУММ(E4:E6)	=СУММ(F4:F6)

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

	А	В	С	D	Е	F
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Текущий облагаемый доход	Облагаемый доход с начала года
		Иванов			1511,5	3841,5
		Петров			1709,5	4534,5
		Сидоров			2105,5	5326,5
					5326,5	13702,5

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

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

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

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

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

	А	С	D	Е
			Ведомость
			72,5
	№	Начислено	Пенсионный фонд	Облагаемый доход с начала года
		=СУММ (Янв:Март!С4)	=СУММ(Янв:Март!D4)	=СУММ(Янв:Март!E4)
		=СУММ (Янв:Март!С5)	=СУММ(Янв:Март!D5)	=СУММ(Янв:Март!E5)
		=СУММ (Янв:Март!С6)	=СУММ(Янв:Март!D6)	=СУММ(Янв:Март!E6)
		=СУММ(C13:С21)	=СУММ(D13:D21)	=СУММ(E13:E21)

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

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

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

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

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

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

	А	В	С	D	Е
				Ведомость
		Минимальный оклад		72,5
	№	Ф.И.О.	Начислено	Пенсионный фонд	Облагаемый доход с начала года
		Иванов			3841,5
		Петров			4534,5
		Сидоров			5326,5
					13702,5

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

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

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

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

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

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

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

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

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

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

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

Задание 1.

Переименуйте первые три листа книги соответственно «Январь», «Февраль», «Март». Внесите на них данные, как показано на рис. 10.1, 10.3, 10.5.
На новом листе «Квартал» консолидируйте данные с трех рабочих листов.

Задание 2.

Четыре цеха предприятия выпускают три вида продукции. Известны объемы производства продукции в месяц по цехам (табл. 10.1) и расходы сырья на единицу продукции (табл. 10.2). Разработать электронную таблицу для расчета потребности цехов и всего предприятия в сырье на месяц (табл. 10.3).

Каждую таблицу разместить на отдельном листе электронной книги, названном соответственно названию таблицы.

Формулы для расчета данных в табл. 10.3 должны вклю­чать в себя частично абсолютные ссылки. При защите ла­бораторной работы следует объяснить, почему выбрана та или иная частично абсолютная ссылка.

Таблица 10.1

Дата добавления: 2014-10-31