Примеры реализации заданий: Работа со справочниками и сводными таблицами Excel
Основные функции Excel, используемые в работе:
Сумм(число1:числоN) – суммирует данные из диапазона;
СуммЕсли(диапазон;критерий;диапазон_суммирования) – просматривает ячейки из диапазона, и суммирует ячейки с тем же индексом из диапазона сумирования, если условие выполняется;
Если(логическое_выражение;значение_если_истинно; значение_если_ложь) – если логическое условие истинно, выполняется значение если истино, иначе значение ложь;
И(логическое_выражение1;логическое_выражение2;…) – истина, если все логические выражения истинны, иначе ложь;
Поискпоз(искомое_значение;просматриваемый_массив; тип_сопоставления) – ищет значение в массиве (0 – ищется точное совпадение) и возвращает номер строки, где оно встретилось;
Просмотр(искомое_значение;просматриваемый_вектор; вектор_результатов) – ищет значение в диапазоне просмотра и возвращает соответствующее ему значение из диапазона результатов;
Индекс(массив;номер_строки;номер_столбца) – выбирает значение элемента из массива по номеру строки и номеру столбца;
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просморт) – ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из указанного столбца массива;
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр) – выполняет поиск значения в верхней строке таблицы или массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.) значений и возвращает значение того же столбца в заданной строке таблицы или массива. Функция ГПР используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые – на несколько строк ниже. Если сравниваемые значения находятся в столбце слева от искомых данных, следует использовать функцию ВПР.
Буква Г в аббревиатуре «ГПР» означает «горизонтальный».
Задача на сортировку, выборку и функции Если, Месяц, День
БД городской телефонной сети. Основная таблица содержит поля – фамилия, адрес, номер телефона, дата последней оплаты. Таблица сортируется по фамилиям. Вторая таблица содержит ту же самую информацию, но упорядочена по номерам телефонов. Разработать две квитанции для расчета оплаты по заданному номеру телефона и по фамилии. В квитанции вводится месяц, включая который производится оплата. Учитывается пеня, если оплата производится после 10 числа текущего месяца.
На листе «Таблица 1» (рис. 56) абоненты упорядочены по номеру телефона, на листе «Таблица 2» (рис. 57) – по фамилии. Так как расчетные формулы однотипны, ниже приводятся комментарии для первого листа. Номера строк и столбцов листа приведены непосредственно в документе Excel.
Ячейки C5 и C6 предназначены для ввода исходных данных – номера телефона, который нужно найти в базе и номера месяца, до которого производится оплата (включительно).
Рис. 56. Лист Таблица 1
В ячейке C8 выводится результат поиска абонента по номеру телефона или значение Н/Н, если абонент не найден. Поиск абонента реализуется с помощью формулы:
=ПРОСМОТР(C5;C21:C1000;A21:A1000)
Здесь C5 – значение искомой строки (номера телефона), C21:C1000 – диапазон просмотра базы данных, A21:A1000 – диапазон, из соответствующей ячейки которого берется результирующее значение.
Для удобства в ячейке С9 выводится дата последнего взноса платы абонентом. Аналогично, поиск реализуется по формуле:
=ПРОСМОТР(C5;C21:C1000;D21:D1000)
Рис. 57. Лист Таблица 2
Сумма оплаты рассчитывается в ячейке С11 по следующей формуле:
=ЕСЛИ(МЕСЯЦ(C9)>=C6;0;(C6-МЕСЯЦ(C9))*C16+ ЕСЛИ(И(ДЕНЬ(C9)>10;МЕСЯЦ(C9)<=C6);C17;0))
Здесь МЕСЯЦ(C9), ДЕНЬ(C9) – месяц и день, когда абонент последний раз вносил плату, С16 – ячейка со значением помесячной платы, С17 – ячейка со значением пени.
Если месяц, когда абонент вносил плату больше либо равен текущего месяца – результат формулы равен 0. Иначе плата вычисляется по формуле:
(Месяц, по который оплачиваем – Текущий месяц) *
*Помесячная плата + Пеня.
Пеня вычисляется по следующему правилу:
ЕСЛИ(И(ДЕНЬ(C9)>10;МЕСЯЦ(C9)<=C6);C17;0))
Если день, когда абонент последний раз вносил плату, больше 10 и месяц, до которого вносилась плата, не превышает текущего, то начисляется пеня, иначе ее значение равно 0.
Построение графика функций
Создайте новый лист Excel и назовите его Построение графика функции.
В ячейки столбца А, начиная с первой, введите значения констант. В следующую ячейку этого столбца введите численное значение шага. В ячейку В1 – начало отсчета.
В ячейку В2 введите формулу изменения шага в виде =В1+А$6, где А$6 – ячейка, в которую введено значение шага. В ячейку С1 введите формулу вычисления функции, в виде =А$1*(A$2+B1), заметьте, что все значения, кроме В1, пишутся через $, поскольку В1 – относительный адрес, а А$1 и A$2 – абсолютные адреса.
Копируйте ячейку В2 в столбец В от В3 до конца вашего диапазона (в зависимости от значения, указанного в таблице), а С1 – в столбец С от С2 до конца интервала соответственно.
На ленте Вставка в группе Диаграммы выберите тип График и вид – График с маркерами (рис. 58).
Рис. 58. Выбор графика с маркерами
в группе Диаграмма ленты Вставка
После выбора вида графика появится дополнительная лента Конструктор. В группе инструментов Данные необходимо нажать кнопку Выбрать данные (рис. 59).
В открывшемся диалоговом окне Выбор источника данных необходимо указать диапазон данных, по которому нужно построить график (рис. 60). После выбора диапазона данных и изменения подписей осей и рядов нажмите кнопку ОК.
Рис. 59. Кнопка Выбрать данные на ленте Конструктор
Рис. 60. Выбор данных в диалоговом окне Выбор источника данных
С помощью дополнительной ленты Макет (рис. 61) оформите название диаграммы, названия осей, легенду для диаграммы, подписи данных и другое.
Рис. 61. Оформление диаграммы с помощью ленты Макет
На дополнительной ленте Формат с помощью блока инструментов Стили фигур (рис. 62) поменяйте заливку фигуры, контур фигуры и настройте эффекты для фигуры.
Рис. 62. Оформление диаграммы с помощью ленты Формат
Поиск решения
Поиск решения – это нахождение оптимального значения исследуемой функции. Ячейка, в которой находится функция, может ссылаться на изменяемые ячейки, в которых содержатся ее аргументы. При этом для каждого аргумента можно задать ограничения. Для запуска поиска решения нужно перейти на ленту Данные и выбрать команду Поиск решения (рис. 63).
Рис. 63. Команда Поиск решения на ленте Данные
После запуска Поиска решения появится диалоговое окно Поиск решения (рис. 64). В этом диалоговом окне необходимо указать целевую ячейку, в которой хранится целевая функция задачи. После этого, выбрав в окне диалога критерий оптимизации (минимальное, максимальное или фиксированное значение целевой функции), необходимо сослаться на зависимые ячейки и ввести ограничения в соответствующих полях окна диалога.
Ячейки, значения которых нужно найти, записываются в текстовое поле Изменяя ячейки.
Ограничения вводятся в поле Ограничения при нажатии на кнопку Добавить, после чего откроется диалоговое окно Добавление ограничения (рис. 65). Ограничения указываются в виде:
Зависимая ячейка Знак Выражение,
где Знак может быть <=, =, >= или ограничение до целого числа, если задача целочисленна.
Рис. 64. Диалоговое окно Поиск решения
Рис. 65. Диалоговое окно Добавить ограничения
При этом начальные значения зависимых ячеек должны быть таковы, чтобы численный метод оптимизации сходился.
Для правильного расчета необходимо задать определенные параметры решения (рис. 66), например, линейная модель и неотрицательные значения, и нажать кнопку ОК.
После добавления всех ограничений и настройки параметров необходимо нажать кнопку Выполнить, при этом откроется диалоговое окно Результаты поиска решения (рис. 67), в котором будет предложено сохранить результаты решения или восстановить исходные значения. Для вывода решения задачи нужно нажать кнопку ОК.
Пример: определить длины сторон a, b, h прямоугольного бака заданного объема V, минимизируя длину сварного шва, которая вычисляется по формуле: L=2(a+2b)+h. Математическая модель этой задачи вместе с ограничениями такова: L→min – оптимизируемая функция; V=const; a,b,h>0 – ограничения.
Рис. 66. Диалоговое окно Параметры поиска решения
Рис. 67. Диалоговое окно Результаты поиска решения
Решение: Введем начальные значения зависимых переменных а=1, b=1, c=1 в ячейки B3, C3, D3, а ограничение для них – число 0 – в ячейку B4. Зависимую переменную V=a*b*c введем в ячейку C7 в виде формулы =B3*C3*D3, а ограничение на нее в виде значения 2 – в ячейку E7. Целевую формулу для вычисления L в виде =2*(B3+2*C3)+D3 введем в ячейку C8. После этого в окне Поиск решения укажем $C$8 в качестве целевой ячейки, выберем поиск минимального значения, в поле «Изменяя ячейки» укажем ссылку $B$3:$D$3, в поле ограничения введем ограничения вида $B$3>=$B$4, $C$3>=$B$4,$D$3>=$B$4, $C$7=$E$7 (рис. 68). Применим к ячейкам B3, C3, D3 и C8 числовой формат ячеек с двумя знаками после запятой. Найденное решение должно быть таким: A=1,26, B=0,63, C=2,52 (рис. 69).
Рис. 68. Таблица значений и диалоговое окно Поиск решения
Рис. 69. Результаты решения задачи