Задание №5. Создание таблиц заданной структуры Общая формулировка задания

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

Кроме вычислений, относящихся к отдельным объектам (они находятся в строках), следует получить некоторые сводные характеристики, для чего возможно придется воспользоваться функциями СЧЕТЕСЛИ0, СУММЕСЛИ(), MAKC(), МИН(), НАИБОЛЬШИЙ(), НАИМЕНЬШИЙ() и др., а если их возможностей не хватает, то и функциями, использующими массивы: {=СУММ(ЕСЛИ(...))} и {=СЧЁТ(ЕСЛИ(...))}. Итоговые функции должны строиться так, чтобы допускались вставки новых данных в любом месте области обработки без их редактирования.

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

При создании таблицы необходимо организовать:

• Встроенный контроль ввода (пункт Контроль в формулировке задачи).

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

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

• Полученные таблицу и график, поместить на одном рабочем листе, напечатать.

Исходные данные в таблицах отображены символами XXX, а вычисляемые - символами ???. Для этих вычисляемых ячеек следует составить точные функции. Хотя в некоторых случаях для наглядности данные заданы конкретными числами, при построении таблицы имеется в виду, что ВСЕ исходные данные могут иметь переменные значения. Длина таблицы может быть произвольной.

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

Создание таблицы «Назначение пенсии»

Постановка задачи

Определить пенсию по старости. Пусть она назначается в размере четырех минимальных зарплат по достижении женщинами 55-ти лет и мужчинами 60-ти лет. Пусть также пенсионерам полагается прибавка к пенсии в размере 5% при наличии двадцати лет трудового стажа, 20% при наличии тридцати лет и 50% - при сорока. Кроме того, на размер пенсии влияет военный стаж. За каждый год военного стажа к пенсии прибавляется 30% от минимальной зарплаты. Однако сумма пенсии и всех доплат не должна превышать пяти минимальных зарплат. В этом случае пенсия, конечно, сохраняется полностью, а доплата (если остается) определяется разностью между пенсией и указанным пределом. Таким образом, человек, имеющий высокую пенсию может не получить помощи совсем. В клетке Е8 подсчитать общее число человек, в Е9 - число женщин, в Е10 – число мужчин.

Рисунок 5.1. Структура таблицы с данными для вычислений

Контроль С5:С7: Стаж трудовой=<70лет.

Форматирование А5:А7: если Пол=Ж.

График: Фамилия - Прибавка.

Решение задачи.

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

1). Для начала в ячейке В1 для предъявления текущей даты установим функцию СЕГОДНЯ(). Установим для ячейки В1 маску формата ГГГГ«г», чтобы отобразить текущую дату как показано в исходной таблице (рисунок 5.1). Для этого нажимаем Формат/Ячейки/вкладка Число/(все форматы)/Тип (устанавливаем самостоятельно) ГГГГ«г» (рисунок 5.2).

Рисунок 5.2. Установка формата для ячейки В1

2). В ячейки А5:А13 вносим фамилии женщин и мужчин. В ячейки В5:В13 вводим дату рождения каждого человека в формате ДЦ.ММ.ГГГГ (таблица 5.1). Маску формата устанавливаем ГГГГ, при этом в ячейках В5:В13 остается видимым только год рождения как того требует формат рисунка 5.1.

Таблица 5.1. Назначение пенсии

Фамилия И.О.	Год рождения
Иванова М.С.	15.12.1936
Петров Ю.Р.	14.11.1945
Сидорова И.Д.	17.12.1950
Зайцев В.С.	12.09.1940
Волков Г.И.	15.20.1955
Медведева К.Ю.	13.02.1938
Галеева И.Т.	15.11.1948
Алимов Ф.Р.	13.12.1940
Погодин В.Л.	11.08.1925

Таким образом, у нас каждый день происходит предъявление текущей даты и имеются даты рождения каждого человека из списка (в том формате, который установлен в условии задачи), что позволяет вычислять с помощью функции ДНЕЙ360(начальная_дата;конечная_дата;метод) количество дней между двумя датами на основе 360-дневного года. Поделив результат на 360, получим количество лет между двумя датами.

Сравнивая количество лет от рождения до текущей даты с возрастами выхода на пенсию у женщин (55 лет) и у мужчин (60 лет), мы можем установить сам факт начала вычисления пенсии. В последствии, применив эту формулу расчетах в ячейках F5:F13 мы будем получать в колонке Пенсия значение 0, если женщина или мужчина не достиг пенсионного возраста.

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

Замечание. Оговорим лишь то, что вводить данные в ячейки В5:В13 следует не так, как они представлены маской формата год (1936), а в виде даты 15.12.1936, чтобы избежать ошибочных вычислений.

Чтобы предупредить пользователя при вводе данных в ячейки В5:В13, установим проверку вводимых значений: Данные/Проверка/окно Проверка вводимых значений/вкладка Параметры, Тип данных: Любое значение/вкладка Сообщение для ввода – заполняем «Внимание! Введите дату рождения в виде ДД.ММ.ГГГГ, при этом маска формата отобразит значение в ячейке в виде ГГГГ». Смотрите на примере организации встроенного контроля вводимых значений в ячейке диапазона С5:С13 (рисунки 5.3, 5.4, 5.5).

Сообщение об ошибке не отображаем, так как невозможно отобразить ограничения, наложенные на формат.

После этого, как только ячейка из диапазона В5:В13 становится текущей, будет отображаться установленная подсказка.

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

3). Следующий шаг: заполнение ячеек С5:С13 (трудовой стаж), D5: D13 (военный стаж), Е5: Е13 (пол). Заполняем их по своему усмотрению и согласно фамилии человека. В ячейках С5:С13, D5:D13 самостоятельно устанавливаем формат 00лет. Для этого также проделываем путь: Формат/Ячейки/окно Формат ячеек/вкладка Число/(все форматы)/Тип (устанавливаем самостоятельно) ООлет (рисунк 5.2).

4). Теперь в ячейке С14 подсчитаем всего лет трудового стажа с помощью функции СУММ(С5:С13);

• в ячейке D14 - всего лет военного стажа с помощью функции СУММ(В5:В13);

• в ячейке Е14 подсчитаем общее число человек с помощью функции СЧЕТЗ(Е5:Е13), которая подсчитывает количество значений в списке аргументов непустых ячеек, тип значений любой, Е14 = СЧЕТЗ(Е5:Е13);

- в ячейке Е15 – число женщин с помощью функции СЧЕТЕСЛИ(Е5:Е13; «Ж»), которая подсчитывает количество непустых ячеек в заданном диапазоне, удовлетворяющих заданному условию, Е15= СЧЕТЕСЛИ(Е5:Е13;«Ж»);

- в ячейке Е16 - число мужчин с помощью этой же функции, Е16= СЧЕТЕСЛИ(Е5:Е13;«М»).

5). Следующий шаг — вычисление пенсий как по каждому человеку так и всего.

• Для этого в ячейке H1 зададим сумму минимальной заработной платы, формат ячейки установим Денежный. H1 = 1000р;

• В ячейке F5 вычислим сумму пенсии. Как сказано в условии задачи, пусть она назначается в размере четырех минимальных зарплат, ... кроме того, на размер пенсии влияет военный стаж – за каждый год военного стажа к пенсии прибавляется 30% от минимальной зарплаты. Математически это выглядит так: Н$1*4 + Н$1*30%*D5, где ячейка Н$1 в формуле зафиксирована по строкам, чтобы была возможность ее копирования вниз без искажения сути в ячейках F6: F13.

Замечание. Ячейку Н$1 можно задавать также абсолютной ссылкой, то есть $Н$1.

• На ячейку F5 необходимо наложить условие: если женщина достигла 55-летнего возраста, мужчина - 60-летного, то пенсия начисляется по приведенной выше формуле, в противном случае пенсия не начисляется и в ячейке F5 появляется значение 0 (нуль). Для этого используем функции ЕСЛИ() и ДНЕЙ360(): F5 =

Таким образом, если между двумя датами - датой рождения и текущей датой - количество лет меньше 55 то значение = 0 и не важно женщина это или мужчина, т. к в этом возрасте ни у женщин ни у мужчин пенсионный возраст еще не наступил;

если разность между двумя датами больше 55, то проверяем больше ли она или равна 60, если больше или равна, то ячейке производится расчет суммы пенсии по формуле Н$1*4 + H$l*30%*D5, так как в этом возрасте и у женщин и у мужчин уже наступил пенсионный возраст;

если же второе условие также на выполнилось, это значит, что значение возраста человека находится в интервале [55;60) и начисление пенсии уже зависит от того женщина это или мужчина, поэтому проверяем условие: если в ячейке Е5 стоит символ Ж, то пенсия начисляется по формуле Н$1*4 + H$l*30%*D5, а если ячейке Е5 стоит не символ Ж (символ М), то значит перед нами мужчина и его пенсионный возраст еще не наступил - значение пенсии равно нулю.

Копируем содержимое ячейки F5 в ячейки F6: F13.

6). Переходя к столбцу «Прибавка» нужно отметить, что вычисления в ячейках G5:G13 целесообразно производить в последнюю очередь, после вычислений общей суммы пенсии (диапазон Н5:Н13), т.к. как по условиям задачи сумма пенсии и всех доплат не должна превышать пяти минимальных зарплат, в этом случае пенсия сохраняется полностью, а доплата (если остается) определяется разностью между пенсией и указанным пределом. Используя функцию ЕСЛИ() вычислим значение ячейки G5 по формуле:

G5 = ECЛИ(F5>=H$1*5;0;H5-F5)

Таким образом, если значение пенсии рассчитанное в ячейке F5, больше либо равно установленного предела, равного пяти минимальным зарплатам, то значение доплаты в ячейке G5 будет равно нулю, в противном случае, учитывая, что в ячейке Н5 пенсия будет рассчитываться точно, доплата равна разности между пенсией всего (ячейка Н5) и пенсией (ячейка F5).

7). Следующий шаг - расчет пенсии всего. Расчет общего начисления пенсии с учетом прибавки за трудовой стаж, надбавки за военный стаж, установленного предела равного пяти минимальных зарплат, при помощи функции ЕСЛИ() и фиксации ячейки с суммой минимальной зарплаты Н$1 произведем по формуле: Н5 =

Таким образом, если значение ячейки F5 равно нулю, а оно будет равно нулю, если мужчина или женщина не достигли пенсионного возраста, то и значение пенсии всего (ячейка Н5) будет равно нулю;

Если это условие не выполняется и F5 не равно 0, то ячейка Н5 будет иметь значение, отличное от нуля, а именно:

• если пенсия в ячейке F5 больше или равна пяти минимальным зарплатам, то «пенсия всего» будет равна этому большому значению (ECJIH(F5>=H$1*5;F5)), при этом прибавки не будет;

• в противном случае, если пенсия в ячейке F5 меньше пяти минимальных зарплат, значит «пенсия всего» может состоять из самой пенсии и прибавки; при этом значение прибавки, зависящей от трудового стажа, вычисляется по следующей части формулы:

ЕСЛИ(С5<20;0;ЕСЛИ(С5<30;Н$1*4*5%;ЕСЛИ(С5<40;Н$1*4*20%; H$l*4*50%))) – если трудовой стаж (значение ячейки С5) меньше 20 лет, то прибавка равна нулю, если С5 меньше 30 лет (в интервале [20;30) лет), то прибавка равна 5% от базовой пенсии в четыре минимальных зарплаты, если С5 меньше 40 лет (в интервале [30;40) лет), то прибавка равна 20% от базовой пенсии в четыре минимальных зарплаты, если С5 не соответствует ни одному из этих интервалов трудового стаже, т.е. больше или равна 40 лет, то прибавка равна 50% от четырех минимальных зарплат.

Так что, если сумма пенсии (ячейка F5) и прибавки (рассчитанной по вышеприведенной формуле) не превышает предела в пять минимальных зарплат, значит это меньшее значение будет установлено в ячейке Н5, в противном случае, если сумма прибавки и пенсии будет больше установленного предела, то в ячейке установится значение, равное пяти минимальным зарплатам.

Из описанного выше можно сделать вывод, что в ячейке Н5 может быть четыре варианта значений:

• нулевое, если пенсионный возраст еще не наступил;

• больше чем установленный предел в пять минимальных зарплат, если значение пенсии благодаря военному стажу больше, чем установленный предел;

• меньше чем установленный предел в пять минимальных зарплат, если пенсия и прибавка не превышают установленный предел;

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

Скопируем значение ячейки Н5 в ячейки Н6:Н13.

В ячейках F14, G14, Н14 подсчитаем итоговые значения по всему списку представленных мужчин и женщин с помощью функции СУММ(): F14 = CУMM(F5:F13); G14 = CУMM(G5:G13); Н14 = СУММ(Н5:Н13).

Таким образом, мы нашли, то, что требовалось в задаче - определили пенсию по старости для каждого человека и для всего списка. В заключении организуем:

• встроенный контроль ввода в ячейках С5:С13: Стаж трудовой=<70лет.;

• условное форматирование для ячеек А5:А13: если Пол=Ж;

• построение графика: Фамилия - Прибавка.

Организация встроенного контроля вводимых значений

Для того чтобы организовать встроенный контроль вводимых значений в ячейках С5:С13, а именно контроль того чтобы значение трудового стажа было меньше либо равно 70 лет, необходимо выполнить команды Данные/Проверка.

В возникшем окне Проверка вводимых значений выбираем вкладку Параметры (рисунок 5.3). Устанавливаем условия проверки: Тип данных – Целое число, значение — меньше или равно, максимум — 70. Устанавливаем галочку, чтобы распространить изменения на другие ячейки с тем же условием и нажимаем на клавишу ОК.

Рисунок 5.3. Окно Проверка вводимых значений,

установка условий проверки

Затем переходим на вкладку Сообщение для ввода (рисунок 5.4) и вводим текст сообщения-подсказки. Теперь, когда ячейка из диапазона С5:С13, становится текущей будет появляться установленная подсказка (рисунок 5.5).

Рисунок 5.4. Окно Проверка вводимых значений, установка сообщения для ввода

Рисунок 5.5. Отображаемая подсказка для ввода.

Затем переходим на вкладку Сообщение об ошибке (рисунок 5.6) и вводим текст сообщения, которое будет возникать, если значение в ячейке из диапазона С5:С13 не будет удовлетворять заданным параметрам, т.е. будет больше 70.

Рисунок 5.6. Окно Проверка вводимых значений,

установка сообщения об ошибке

Организация условного форматирования

Для того чтобы наложить условие, при выполнение которого осуществлялось бы автоматическое форматирование ячеек А5:А13 выделяем диапазон ячеек А5:А13, на панели инструментов нажимаем Формат/Условное форматирование. В появившемся окне Условное форматирование отображаем условие: формулу = Е5:Е13 = «Ж», и формат, который приобретут ячейки при выполнении условия. Для выбора формата нажимаем Формат в окне Условное форматирование. Выбираем для отображения содержимого ячеек А5:А13 (Фамилия И.О.), если пол, установленный в ячейках Е5:Е13 будет женским (Ж) шрифт - полужирный и вид - заливка ячеек голубым цветом (рисунок 5.7).

Рисунок 5.7. Окно Условное форматирование

с установленным условием и форматом

Построение диаграммы Фамилия–Прибавка

Для построения графика нажимаем на панели инструментов Мастер диаграмм. В появившемся окне Мастер диаграмм (шаг 1 из 4): тип диаграммы выбираем Линейчатая, нажимаем кнопку Далее. Затем в появившемся окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы выбираем диапазоны (А5:А13) и (G5:G13) и помечаем ряды в столбцах, нажимаем Далее. В появившемся окне Мастер диаграмм (шаг 3 из 4): параметры диаграммы устанавливаем названия диаграммы («Прибавка к пенсии») и осей X (размер прибавки, руб.) и Y (Фамилия И.О.), нажимаем Далее. В последнем появившемся окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы указываем на то, что диаграмму нужно поместить на имеющемся листе, нажимаем Готово.

Полученные таблица Начисление пенсии и график Фамилия-Прибавка изображены на рисунках 5.8 и 5.9.

Рисунок 5.8. Таблица Начисление пенсии

Рисунок 5.9. Диаграмма Фамилия-Прибавка