Компьютерные информационные технологии Учебное пособие

Министерство образования Республики Беларусь

УДК 681.3(075.8)

УЧРЕЖДЕНИЕ ОБРАЗОВАНИЯ «ГРОДНЕНСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИМЕНИ ЯНКИ КУПАЛЫ»

Рецензенты:

ББК 32.973 Р32

кандидат т ех н ич ес к их наук , доцент, зам. директора Гродненского филиала УО ИСЗ В.И.Варнаков; кандидат ф и з ико-м ат ем ат ич е ск их наук, до це нт кафедры дифференциальных уравнений и о птим а льного управления ГрГ У им. Я. Купалы З. М.Нар к ун .

Рекомендовано советом ИПО ГрГУ им. Я.Купалы. Ревчук И.Н.

И.Н.РЕВЧУК, В.К.ПЧЕЛЬНИК

КОМПЬЮТЕРНЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ Учебное пособие для студентов специальности Э.01.04.00П «Финансы и кредит»

Р32

Компьютерные информационные технологии : учеб. пособие/И.Н.Ревчук, В.К.Пчельник. – Гродно: ГрГУ, 2005. — 201 с. ISBN 985–417–692–4 В учебном пособии содержится теоретический материал, примеры и задания по следующим разделам курса: введение в электронные таблицы, составление формул, построение графиков и диаграмм, использование надстройки «Поиск решения» для решения задач оптимизации, сортировка данных, анализ данных средствами «итоги», «форма», «автофильтр», «расширенный фильтр» и с помощью сводных таблиц.

УДК 681.3(075.8) ББК 32.973

Гродно 2005 ISBN 985–417–692–4

© Ревчук И.Н., Пчельник В.К., 2005

1. Введение в электронные таблицы MS Excel Вычисления, выполняемые инженерами, научными сотрудниками, менеджерами, финансовыми аналитиками могут быть как простыми, так и очень сложными. С простыми расчетами значений функций они сталкиваются практически ежедневно, но и сложные задачи, например, оптимизационные, не является для них чем-то исключительным. Любой инструмент, способный упростить численные расчеты, значительно повышает производительность научного работника, инженера или менеджера. Одним из таких инструментов являются электронные таблицы, обеспечивающие средства для выполнения вычислений, построения графиков и диаграмм, а также глубокого анализа данных. Широкими возможностями проведения расчетов обладает пакет электронных таблиц Excel, являющийся составной частью пакета Microsoft Office. Microsoft Excel является мощным программным средством для работы с таблицами данных, позволяющим анализировать, упорядочивать и графически представлять различные виды данных. Начало работы 1. Запустите MS EXCEL. 2. Разверните окно EXCEL на весь экран и внимательно рассмотрите его. EXCEL предоставляет несколько вариантов настройки экрана, однако при изучении основных операций с электронной таблицей будем предполагать, что окно EXCEL выглядит так, как показано на рисунке 1. Верхняя строка окна MS EXCEL – заголовок окна с кнопками управления. Вторая строка – меню процессора MS EXCEL. Третья и четвертая строки – панели инструментов СТАНДАРТНАЯ и ФОРМАТИРОВАНИЕ. Назначения кнопок панели инструментов СТАНДАРТНАЯ можно прочитать, медленно перемещая курсор мыши по кнопкам. 3

Пятая строка – строка ввода и редактирования или строка формул. Строки между пятой и последней заняты рабочим листом электронной таблицы. Строки и столбцы таблицы имеют определенные обозначения. Нижняя строка – строка состояния.

Рисунок 1

В крайней левой позиции нижней строки отображается индикатор режима работы MS EXCEL. Например, когда EXCEL ожидает ввода данных, то находится в режиме «готов» и индикатор режима показывает: ГОТОВО. 3. Рассмотрим, как произвести настройку экрана, используя меню EXCEL. Работа с меню С меню MS EXCEL удобно работать при помощи мыши. Выбрав необходимый пункт, нужно навести на него курсор и сделать щелчок по левой кнопке мыши. Левой кнопкой мыши выбираются необходимые команды подменю и раскрываются вкладки, а также устанавлива4

ются флажки и переключатели. Щелчок мыши вне меню приводит к выходу из него и закрытию подменю. 3.1.Разверните окно приложения MS EXCEL во весь экран. Окно документа, с которым вы работаете, также должно быть полноэкранным. 3.2. В меню ВИД выберите команду ПАНЕЛИ ИНСТРУМЕНТОВ (рисунок 2). В раскрывшемся диалоговом окне установите флажки [9] так, как показано на рисунке 2. Установка и снятие флажков производится щелчком левой кнопки мыши в соответствующем меню. Для вывода панелей инструментов можно использовать контекстнозависимое меню, которое появляется после щелчка правой кнопкой мыши в зоне расположения панелей инструментов или меню.

Далее в тексте подобные действия по работе с меню будут описываться в краткой форме: СЕРВИС/ПАРАМЕТРЫ/ ВИД/СТРОКА ФОРМУЛ.

Рисунок 3 Рисунок 2

3.3. В меню СЕРВИС выберите команду ПАРАМЕТРЫ и раскройте вкладку Вид. 3.4. Проверьте, установлены ли флажки [9], как указано на рисунке 3. 3.5. Если нет, то произведите соответствующую установку. 3.6. Раскройте вкладку ОБЩИЕ. 3.7. Проверьте, установлен ли флажок [9] Стиль ссылок R1C1. Если нет, то произведите соответствующую установку и на рабочем листе установите, как изменился его внешний вид. После этого снимите флажок Стиль ссылок R1C1. 5

4. Ознакомимся с основными понятиями электронных таблиц. Для этого внимательно прочитайте нижеследующий текст и выполните указанные действия. Строки, столбцы, ячейки Рабочее поле электронной таблицы состоит из строк и столбцов. Максимальное количество строк равно 65 536, столбцов – 256. Каждое пересечение строки и столбца образует ячейку, в которую можно вводить данные (текст, число, дату или формулу). Номер строки определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля. 6

Буква столбца определяет колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Колонки нумеруются в следующем порядке: А – Z, затем АА – AZ, затем ВА – BZ и т. д. Ячейка – первичный элемент таблицы, содержащий данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки. Например, адрес ВЗ определяет ячейку на пересечении столбца В и строки номер 3. Указатель ячейки – прямоугольник, определяющий текущую ячейку. Указатель можно перемещать по таблице как при помощи клавиатуры, так и мышью. Текущая ячейка помечена указателем. Ввод данных и некоторые другие действия по умолчанию относятся к текущей ячейке. 4.1. Сделайте текущей ячейку D4 при помощи мыши. 4.2. Вернитесь в ячейку А1 при помощи клавиш перемещения курсора. Блок (область) Блок представляет собой прямоугольную область смежных ячеек. Блок может состоять из одной или нескольких ячеек, строк или столбцов. Адрес блока состоит из координат противоположных углов, разделенных двоеточием. Например: В13:С19, A12:D27 или D:F. Блок можно задать при выполнении различных команд или вводе формул посредством указания координат или выделения на экране. Рабочий лист, книга Окно с заголовком Книга1 состоит из нескольких рабочих листов (по умолчанию таких листов 3). При открытии рабочей книги автоматически загружаются все ее рабочие листы. На экране виден только один лист – Лист1. Нижняя часть листа содержит ярлычки других листов. Щелкая мышью на ярлычках листов, можно перейти к другому листу. 4.3. Сделайте текущим Лист2. 4.4. Вернитесь к Листу1. 4.5. Выделите строку 3. 7

4.6. Снимите выделение. 4.7. Выделите столбец D. Выделение столбцов, строк, блоков таблицы Для быстроСледует выполнить действия: го выделения столбца щелкнуть мышью на букве — имени столбца; нескольких щелкнуть мышью на букве — имени первого столбцов столбца и, не отпуская кнопку после щелчка, протянуть мышь по именам выделяемых столбцов. строки щелкнуть мышью на числе – имени строки. нескольких щелкнуть мышью на числе – имени первой стрострок ки и, не отпуская кнопку после щелчка, протянуть мышь по номерам выделяемых строк. блока щелкнуть мышью на начальной ячейке блока и, не отпуская кнопку, протянуть мышь на последнюю ячейку. рабочего щелкнуть мышью на пересечении имен столбцов листа и строк (левый верхний угол таблицы). блока с по- удерживая клавишу Shift, нажимать на соответмощью кла- ствующие клавиши перемещения курсора, или, виатуры нажав клавишу F8, войти в режим выделения и произвести выделение при помощи клавиш перемещения курсора. Esc – выход из режима выделения. нескольких выделить первую ячейку или блок смежных яченесмежных ек, нажать и удерживать клавишу Ctrl, выделить блоков следующую ячейку или блок и т. д., отпустить клавишу Ctrl. Для снятия достаточно щелкнуть мышью по любому невывыделения деленному участку рабочего листа. Новое выделение снимает предыдущее. 4.8. Выделите блок А2: А13 при помощи мыши. 4.9. Выделите столбцы A,B,C,D. 4.10. Снимите выделение. 8

4.11. Выделите блок С4: F10 при помощи клавиатуры. 4.12. Выделите рабочий лист. 4.13. Снимите выделение. 4.14. Выделите одновременно следующие блоки: F5:G10, Н15:I15, C18:F20,Н20. 5. Ознакомимся с основными приемами заполнения таблиц. Содержимое ячеек В MS EXCEL существуют следующие типы данных, вводимых в ячейки таблицы: текст, число, дата, время и формула. Для ввода данных необходимо переместиться в нужную ячейку и набрать данные, а затем нажать Enter или клавишу перемещения курсора. MS EXCEL определяет, являются ли вводимые данные текстом, числом или формулой по первому символу. Если первый символ буква или знак апостроф ('), то MS EXCEL считает, что вводится текст. Если первый символ цифра или знак =, то MS EXCEL считает, что вводится число или формула. Данные, которые вводятся, отображаются в ячейке и в строке формул и только при нажатии Enter или клавиши перемещения курсора помещаются в ячейку. Ввод текста Текст – это набор любых символов. Если текст начинается с числа, то начать ввод необходимо с апострофа ('). Если ширина текста больше ширины ячейки и ячейка справа пуста, то текст на экране займет и ее место. При вводе данных в соседнюю ячейку предыдущий текст будет обрезан (но при этом в ячейке он будет сохранен полностью). 5.1. В ячейку А1 занесите текст Гродно – древний город. По умолчанию после фиксации текста в ячейке он будет прижат к левому краю. Ввод чисел Числа в ячейку можно вводить со знаками =, +, – или без них. Если ширина введенного числа больше, чем ширина ячейки на экране, то MS EXCEL изображает его в экспоненци9

альной форме либо вместо числа ставит символы ##### (при этом число в ячейке будет сохранено полностью). Экспоненциальная форма используется для представления очень маленьких либо очень больших чисел. Число 501 000 000 будет записано как 5,01 Е+08, что означает 5,01·108. Число 0,0000000051 будет представлено как 5,1 Е–9, что означает 5,1·10–9. Для ввода дробных чисел используется десятичная запятая или точка в зависимости от настройки (по умолчанию – запятая). Любой символ перед числом или в середине числа превращает его в текст. По умолчанию после фиксации числа в ячейке MS EXCEL сдвигает его к правой границе ячейки. 5.2. В ячейку В1 введите число 1128 (это год основания города Гродно). 5.3. В ячейку С1 введите число – текущий год. Ввод даты и времени При вводе даты или времени MS EXCEL преобразует их в специальное число (поэтому дата и время после фиксации в ячейке прижаты к правому краю ячейки). Это число представляет собой количество дней, прошедших от 1 января 1900 года до введенной даты. Благодаря тому, что MS EXCEL преобразует дату и время в последовательное число, с ними можно производить такие же операции, как и с числами. Введенная дата может быть представлена в любом формате, допускаемом MS EXCEL. Список всех установленных форматов можно увидеть, выполнив команду ФОРМАТ/ЯЧЕЙКИ. На вкладке ЧИСЛО следует выбрать слева категорию Дата (рисунок 4): После фиксации даты в ячейке в качестве разделителя между днем, месяцем и годом устанавливается точка. Например, 3.12.99 или 12.Map.99 Введенное время может иметь форматы, приведенные на рисунке 6. 5.4. В ячейку К1 занесите текущую дату. 5.5. В ячейку К2 занесите дату 1 марта 2003 года. 10

5.6. В ячейку D1 занесите формулу = С1– В1 (С1 и В1 вводятся либо в английской раскладке клавиатуры, либо щелчком левой кнопки мыши по соответствующей ячейке на рабочем листе). Полученное число представляет собой возраст города Гродно (рис. 5).

эту ячейку, выполнить команду ФОРМАТ/ЯЧЕЙКИ и на вкладке ЧИСЛО выбрать числовой формат (рис. 8).

Рисунок 5

Рисунок 6 Рисунок 4

5.7. В ячейку КЗ занесите формулу = К1– К2, чтобы определить количество дней, которое прошло между датами, приведенными в ячейках К1 и К2 (рис. 7). Чтобы разность в ячейке К3 отобразилась привычным числом, следует сделать активной Рисунок 7

11

Рисунок 8

12

Основные приемы редактирования таблиц 6. Изменение ширины столбцов и высоты строк Эти действия можно выполнить, используя мышь или меню. При использовании мыши указатель мыши необходимо навести на разделительную линию между именами столбцов или номерами строк. Указатель примет вид двойной черной стрелки. Затем необходимо нажать левую кнопку мыши и растянуть (сжать) столбец или строку или дважды щелкнуть в этом месте левой кнопкой мыши для автоматической установки ширины столбца или высоты строки. При использовании меню необходимо выделить строки или столбцы и выполнить команды ФОРМАТ/ СТРОКА/ ВЫСОТА или ФОРМАТ/СТОЛБЕЦ/ШИРИНА (рис. 9–10).

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

Рисунок 9

Рисунок 10

6.1. При помощи мыши измените ширину столбца А так, чтобы текст был виден полностью, а ширину столбцов В, С, D сделайте минимальной. 6.2. При помощи меню измените высоту строки 1 и сделайте ее равной 30. 6.3. Сделайте высоту строки 1 первоначальной (12,75). Корректировка содержимого ячейки

6.4. Например, требуется определить возраст Гродно в 2010 году. Замените текущий год в ячейке С1 на 2010. В ячейке D1 появился новый возраст города (в 2010 году). 6.5. Отредактируйте текст в ячейке А1. Новый текст: Гродно – центр Гродненской области.

Редактирование данных может осуществляться в процессе ввода в ячейку и после завершения ввода. Если во время ввода 13

Чтобы отредактировать данные после завершения ввода (после нажатия клавиши Enter), необходимо переместить указатель к нужной ячейке и нажать клавишу F2 для перехода в режим редактирования или щелкнуть мышью на данных в строке формул. Далее необходимо отредактировать данные и нажать Enter или клавиши перемещения курсора для завершения редактирования.

Операции со строками, столбцами, блоками Прежде чем произвести какие-либо действия с блоком, его необходимо выделить. 14

Чтобы выделить Текст в ячейке

Сделайте следующее Если режим редактирования в ячейке включен, выделите ячейку, дважды щелкните ее и выделите текст в ячейке. Если режим редактирования в ячейке выключен, выделите ячейку и выделите текст в строке формул.

Отдельную ячейку

Укажите ячейку или перейдите к ней, используя клавиши перемещения.

Диапазон ячеек

Протащите указатель от первой ячейки диапазона к последней.

Все ячейки листа

Нажмите кнопку Выделить все.

Несмежные ячейки или диапазоны ячеек

Выделите первую ячейку или первый диапазон ячеек, затем, удерживая нажатой клавишу CTRL, выделите остальные ячейки или диапазоны.

Большой диапазон ячеек

Укажите первую ячейку, затем, удерживая нажатой клавишу SHIFT, укажите последнюю ячейку диапазона. Для перемещения к последней ячейке можно использовать полосы прокрутки.

Всю строку

Щелкните заголовок строки.

Заголовок строки 15

Весь столбец

Щелкните заголовок столбца. Заголовок столбца

Смежные строки или столбцы

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

Несмежные строки или столбцы

Выделите первую строку или первый столбец, затем, удерживая нажатой клавишу CTRL, выделите остальные строки или столбцы.

Большее или меньшее количество ячеек по сравнению с текущим диапазоном

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

Действия по перемещению, копированию, удалению, очистке блока можно производить несколькими способами, что дает возможность пользователю выбрать наиболее удобный для него способ: ƒ с помощью кнопок панели инструментов СТАНДАРТНАЯ (ВЫРЕЗАТЬ, КОПИРОВАТЬ, ВСТАВИТЬ); ƒ через главное меню MS EXCEL (в меню ПРАВКА команды ВЫРЕЗАТЬ, КОПИРОВАТЬ, ВСТАВИТЬ, УДАЛИТЬ, ОЧИСТИТЬ, ЗАПОЛНИТЬ); ƒ с помощью мыши; ƒ с помощью контекстно-зависимого меню. Если щелкнуть по выделенному блоку правой кнопкой мыши, то откроется контекстно-зависимое меню, где можно 16

найти все команды, необходимые для копирования, вырезания, вставки, удаления. Порядок работы аналогичен работе с главным меню. Перемещение Перемещая данные, необходимо указать, что перемещается и куда. Для выполнения перемещения с помощью мыши требуется выделить ячейку или блок (что перемещается). Затем навести указатель мыши на рамку блока или ячейки (он должен принять форму белой стрелки). Далее следует перетащить блок или ячейку (в место, куда нужно переместить данные). 6.6. Выделите блок А1:D1 и переместите его на строку ниже. 6.7. Верните блок на прежнее место. Копирование При копировании оригинал (что) остается на прежнем месте, а в другом месте (куда) появляется копия. Копирование выполняется аналогично перемещению, но при нажатой клавише Ctrl. 6.8. Скопируйте блок А1:D1 в строки 3, 5, 7. Заполнение При заполнении исходная ячейка (что) или блок повторяется несколько раз за одно действие. Заполнение возможно вправо или вниз. Заполнение с помощью мыши выполняется так же, как и перемещение, но при этом курсор должен наводиться на нижний правый угол ячейки или блока (принимает форму черного плюса и называется маркером заполнения). Если маркера нет, то выполните следующие действия: СЕРВИС/ПАРАМЕТРЫ/ПРАВКА/ПЕРЕТАСКИВАНИЕ ЯЧЕЕК [9], ОК. 6.9. Выделите строку 7 и заполните выделенными данными строки по 15 включительно. 6.10. Заполните данными столбца С столбцы Е, F, G. 17

Удаление, очистка Если надо очистить только данные (числа, текст, формулы), то достаточно на выделенной ячейке или блоке нажать клавишу Del. 6. 11. Выделите блок A10:G15 и очистите его. 6.12. Выделите блок К1:КЗ и очистите его. Для очистки ячейки или блока от введенных данных можно установить указатель на ячейку или выделить блок, а затем выполнить команду ПРАВКА/ОЧИСТИТЬ. В подменю необходимо указать, что конкретно требуется очистить: данные, оформление, примечание или все вместе. 6.13. Очистите содержимое ячейки G9, используя команды меню. Для удаления столбцов, строк, блоков нужно выделить необходимый элемент, а затем воспользоваться командами меню ПРАВКА/УДАЛИТЬ. 6.14. Удалите столбец Е. Обратите внимание на смещение столбцов. Для удаления данных из таблицы с сохранением пустого места необходимо воспользоваться командой ПРАВКА/ОЧИСТИТЬ. 6.15. Удалите столбец Е с сохранением пустого места. Экран после выполнения данной части работы должен иметь вид, представленный на рисунке 11. 7. Использование функции автозаполнения. В MS EXCEL существует функция автозаполнения, которая позволяет быстро вводить различные типовые последовательности (арифметическую и геометрическую прогрессии, даты, дни недели, месяца, года, и т. д.) MS EXCEL позволяет вводить также некоторые нетиповые последовательности, если удается выделить какую-либо закономерность. 7.1. В ячейку G10 занесите год – 2004 7.2. В ячейку H10 занесите год – 2005 7.3. Выделите блок G10:H10 7.4. Укажите на маленький квадратик в правом нижнем углу ячейки H10 (экранный курсор превращается в маркер заполнения (рисунок 12). 18

7.5. Нажмите левую кнопку мыши и, не отпуская ее, двигайте мышь вправо, пока рамка не охватит ячейки G10:M10. Блок заполнился годами с 2004 по 2010. 7.6. Введите в ячейки G11:M11 аналогично дни недели, начиная с понедельника. При этом оказывается достаточно ввести один понедельник 7.7. В ячейки G12 M12 аналогично введите названия месяцев, начиная с января. При этом достаточно ввести один январь. 7.8. В ячейки G13 : М13 введите даты, начиная с 12 декабря. 7.9. Используя рисунок 13, внесите следующие данные в таблицу: - в ячейки G16:М16 – века; - в ячейку G15 занесите заголовок: Население Гродно (тыс. чел.); - в ячейки С17: М17 запишите данные о населении города Гродно по годам. 8. Освойте действия с таблицей в целом, такие, как сохранить, закрыть, создать, открыть.

Для действий с рабочей книгой в целом используются команды из меню ФАЙЛ. - СОХРАНИТЬ – сохраняет рабочую книгу на диске для последующего использования; - СОХРАНИТЬ КАК... – аналогична команде СОХРАНИТЬ, но при этом позволяет изменить имя файла или место его размещения; - ЗАКРЫТЬ – убирает документ с экрана; - СОЗДАТЬ – создает новую рабочую книгу (пустую или на основе указанного шаблона); - ОТКРЫТЬ – возвращает рабочую книгу с диска на экран. Действия СОЗДАТЬ, ОТКРЫТЬ, СОХРАНИТЬ закреплены за тремя первыми кнопками панели инструментов СТАНДАРТНАЯ. 8.1. Сохраните таблицу на рабочем диске в личном каталоге под именем my.xls 8.2. Закройте документ. 8.3. Вернитесь к своему документу my.xls

Рисунок 13

Рисунок 11

8.4. Закройте файл. 9. Завершите работу с MS EXCEL. Для выхода из MS EXCEL можно воспользоваться, например, одним из способов: 1. Меню: ФАЙЛ/ВЫХОД. 2. Клавиатура – Alt+F4. Если Вы не сохранили рабочую книгу, то появится рамка с предупреждающим сообщением, вам будет предложено сохранить ее или выйти без сохранения.

Рисунок 12

19

20

Формула, вводимая в ячейку, может содержать следующие элементы: • операторы (+, –, /, *, ^, &, %, =, >, >= ,<, <=, <>, : , запятая, одиночный пробел). Последние три оператора – операторы ссылки. Двоеточие – оператор диапазона. Запятая – оператор объединения. Пробел – оператор пересечения; • ссылки на ячейки. Сюда входят именованные ячейки и диапазоны, относящиеся как к текущему рабочему листу, так и к ячейкам другого листа текущей рабочей книги и даже ячейкам листов другой рабочей книги; • константы (числовые, текстовые, дата и время); • функции и их аргументы; • скобки. Задают порядок выполнения действий в формуле. Ввод формул можно осуществить вручную. При ручном вводе можно одновременно редактировать вводимую формулу. Завершение ввода формулы – ENTER (за исключением ввода табличных формул). В ячейке обычно после этого появляется результат вычислений, а в строке формул видна сама формула. При вводе ссылок на ячейки и их диапазоны желательно производить их ввод с использованием мыши. Для вставки имени в формулу следует выполнить команду Вставка/Имя/Вставить. Чаще всего формулы не имеют пробелов. Однако при вводе формул все же можно использовать пробелы и разрывы строк для улучшения читаемости формулы. Для разрыва строки следует использовать сочетание ALT+ENTER. Формула не может содержать более 1024 символов. Редактирование формул можно выполнить одним из следующих способов: • • • •

двойной щелчок при курсоре, расположенном в ячейке с редактируемой формулой; нажать F2; выделить ячейку с формулой и перейти в строку формул; щелкнуть по кнопке Изменить формулу в строке формул (=). 21

При редактировании формулы можно выделять несколько символов либо с помощью мыши, либо с помощью клавиш со стрелками при нажатой клавише SHIFT. Можно использовать клавиши Home, End. Ниже приведена таблица с приоритетами операторов в формулах.

Символ

– % ^ *, / +, – & =, <, >, <=, >=, <>

Оператор

Отрицание Процент Возведение в степень Умножение и деление Сложение и вычитание Объединение текстовых строк в одну Сравнение

Приоритет

2. Составление формул Основные сведения о формулах

1 2 3 4 5 6 7

Для изменения порядка выполнения операций используются круглые скобки. Вычисление по формуле происходит сразу же после ввода (ENTER). Существует четыре типа ссылок: • относительные А1. При копировании формулы ссылка на ячейку изменяется в соответствии с новым расположением ячейки со скопированной формулой; • абсолютные $A$1. При копировании формулы ссылка на ячейку не меняется; • абсолютная строка $A1; • абсолютный столбец A$1. Знак доллара в соответствующем месте ссылки можно получить с использованием клавиши F4. По умолчанию Excel использует тип ссылок А1. Однако программа поддерживает и другую систему записи – R1C1. В соответствии с этой системой ячейка А1 имеет адрес R1C1 и т.д. Для изменения системы записи следует выполнить команду Сервис/Параметры и на вкладке Общие установить или убрать соответствующий флажок. 22

В формуле могут использоваться ссылки на ячейки других листов данной рабочей книги или на ячейки листов другой рабочей книги: =Лист2!А1+1 =[Бюджет.xls]Лист1!А1+1. Если в имени рабочей книги присутствуют пробелы, имя книги и имя рабочего листа следует заключить в одинарные кавычки: =’[Анализ бюджета.xls]Лист1’!А1+1. Если рабочая книга, на которую задана ссылка, закрыта, необходимо добавить полный путь к файлу этой книги: =’C:\MSOffice\Excel\Анализ бюджета.xls]Лист1’!А1+1. Совсем не обязательно записывать такие строки вручную. При формировании формулы в случае открытых книг можно выполнять переходы на нужные листы и книги кнопкой мыши. При копировании формулы Excel автоматически изменяет ссылки на ячейки при вставке формулы в новую ячейку. Если этого следует избежать, то можно скопировать формулу в режиме редактирования как обычный текст и затем вставить формулу в нужную ячейку. Таким же образом можно копировать при необходимости часть формулы. Иногда требуется преобразовать формулу в значение. Для этого следует использовать копирование нужных ячеек, а затем выполнить команду Правка/Специальная вставка и выбрать переключатель Значения. Чтобы скрыть формулы, следует выполнить команду Формат/Ячейки, перейти на вкладку Защита, установить флажок Скрыть формулы (рисунок 14), в меню Сервис открыть подменю Защита и выбрать команду Защитить лист (с паролем)

23

Рисунок 14

Ниже приведены ошибки, возникающие при вводе формул: Значение ошибки #ДЕЛ/0! #ИМЯ? #Н/Д

#ПУСТО! #ССЫЛ! #ЧИСЛО! #ЗНАЧ!

Пояснения Деление на ноль В формуле используется имя, которое Excel не воспринимает Формула обращается к ячейке (прямо или косвенно), в которой используется функция НД, что служит сигналом обнаружения неопределенных данных. Задано пустое пересечение диапазонов Недопустимая ссылка на ячейку (например, ячейка удалена) Возникли проблемы со значением. Например, введено отрицательное число там, где должно быть введено положительное число Используется недопустимый тип аргумента

Иногда при работе с формулами Excel выдает сообщение об обнаружении циклических ссылок. Такая ссылка появляется, когда формула содержит прямую или косвенную ссылку на собственное значение. Обычно появление сообщений такого рода связано с ошибочным вводом. Если же нужны итерационные 24

вычисления, то следует установить флажок Итерации на вкладке Вычисления окна диалога Сервис/Параметры. Использование имен Имя можно рассматривать как идентификатор какоголибо элемента рабочей книги. Имя может быть присвоено ячейке, диапазону, диаграмме, формуле и т.д. Преимущества использования имен: • легче понять смысл формулы; • описательное имя диапазона легче запомнить; • можно легко перемещаться по рабочему листу, используя поле Имя в левой части строки формул; • при выделении именованной ячейки в поле Имя получаем имя этого элемента; • легче написать формулу, используя команду Вставка/Имя/Вставить. Чтобы присвоить имя ячейке или диапазону, следует выделить нужный элемент и выполнить команду Вставка/Имя/Присвоить. Затем ввести имя в соответствующем текстовом поле. Если продолжаем присваивать имена, то щелкнем по кнопке Добавить и продолжим процесс. Количество имен не ограничено. Имя можно задавать и прерывистому диапазону ячеек. Присвоить имя можно с помощью поля Имя. Для этого следует выделить диапазон, открыть поле Имя и ввести имя. Правила создания имен: 1) имена не должны содержать пробелов; 2) первый символ – буква; 3) количество символов – не более 255; 25

4)

для разделения слов внутри имени не следует использовать других символов кроме подчеркивания и точки; 5) регистр не имеет значения. Имена могут присваиваться автоматически. Чтобы присвоить имена, используя текст смежных ячеек, выделим этот текст и ячейки, которым должно быть присвоено имя. Текст имен должен находится в смежных ячейках относительно ячеек, которым следует назначить имена. Например, в ячейках А1:А12 находятся названия месяцев. Чтобы ячейкам В1:В12 были присвоены соответствующие имена, выделяем диапазон А1:В12 и выполняем команду Вставка/Имя/Создать. Обычно программа правильно распознает нужный вариант присваивания имен. В противном случае следует вручную установить нужный флажок. Иногда имеет смысл присвоить имя целому столбцу или строке: =СУММ(ПРОДАЖИ) вместо =СУММ(А:А). Чтобы назначить имя диапазону А1:С3, который распространяется на Лист1, Лист2, Лист3, можно выполнить такие действия: 1) активизировать Лист1; 2) выполнить команду Вставка/Имя/Присвоить; 3) ввести имя; 4) активизировать поле Формула; 5) удерживая нажатой клавишу Shift, щелкнуть на ярлыке Лист3. При этом получим ссылку: =’Лист1:Лист3’!$A$1; 6) выделить диапазон А1:С3 на листе Лист1 еще один раз. В поле Формула появится запись: =’Лист1:Лист3’!$A$1:$C$3; 7) Ok 26

=A3/$G$3 =A3/$H$3 соответственно и распространим их на диапазон В4:D20 (рисунок 15). Здесь использованы абсолютные ссылки на ячейки с курсами валют. Отформатируем числовые данные в ячейках А3:D20 так, как показано на рисунке 16. Для этого следует выделить соответствующий столбец, выполнить команду ФОРМАТ/ЯЧЕЙКИ и на вкладке ЧИСЛО выбрать соответствующий числовой формат (рисунок 17).

Имя, присваиваемое ячейке или диапазону, обычно может использоваться на любом из листов рабочей книги. Такое имя называется именем уровня рабочей книги (или глобальным именем). По умолчанию все создаваемые имена именно таковы. Чтобы создать имя уровня рабочего листа, следует выполнить все те же команды по созданию имени. Однако в поле Имя перед именем объекта следует задать имя листа, за которым следует восклицательный знак. Чтобы использовать имя в формуле, следует выполнить команду Вставка/Имя/Вставить, затем выбрать нужное имя, нажать Ok и продолжать запись формулы.

Примеры решения задач Задача 2.1. На рисунке 15 в ячейках А3:А20 расположены числа, представляющие собой денежные суммы в белорусских рублях. Получить в ячейках В3:D20 эквивалентные суммы в российских рублях, евро и долларах США. Курсы указанных валют по отношению к белорусскому рублю приведены в ячейках F3:H3. Отформатировать полученную таблицу так, как показано на рисунке 16. Решение. Введем в ячейки В3:D3 формулы: =A3/$F$3 27

Рисунок 15

Рисунок 16

28

Установим курсор в ячейку D2 и введем в нее формулу: =ЕСЛИ(B2>C2;"Превышение бюджета"; "В пределах бюджета").

Рисунок 17

Замечание.Если в ячейку В3 ввести формулу =$A3/F$3, а затем распространить ее сначала на ячейки С3:D3, а затем – на диапазон В4:D20, то получим тот же результат. Задача 2.2. Рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925 (рисунок 18). Составить формулу для проверки соответствия бюджету расходов определенного месяца, выводя сообщения вида: «Превышение бюджета» или «В пределах бюджета»

Рисунок 19

Выполняем затем распространение этой формулы на оставшийся диапазон D3:D4. Результат работы формулы приведен на рисунке 20.

Решение Рисунок 20

Задача 2.3. Пусть требуется назначить буквенную категорию числам согласно приведенной ниже таблице. Рисунок 18

29

30

Средний балл Больше 89 От 80 до 89 От 70 до 79 От 60 до 69 Меньше 60

Категория A B C D F

Решение

1 вариант. Воспользуемся вложенными функциями ЕСЛИ: =ЕСЛИ(A20>89;"A";ЕСЛИ(A20>79;"B"; ЕСЛИ(A20>69;"C";ЕСЛИ(A20>59;"D";"F")))).

В предыдущем примере второе предложение ЕСЛИ является в то же время аргументом значение_если_ложь для первого предложения ЕСЛИ. Аналогично, третье предложение ЕСЛИ является аргументом значение_если_ложь для второго предложения ЕСЛИ. Например, если первое лог_выражение (Среднее>89) имеет значение ИСТИНА, то возвращается значение «A». Если первое логическое выражение имеет значение ЛОЖЬ, то вычисляется второе предложение ЕСЛИ, и так далее. 31

32

2 вариант. Задача решается с использованием функций ВПР. Создадим таблицу, содержащую диапазоны баллов с указанием категории, причем первый столбец этой таблицы должен быть отсортирован по возрастанию. Присвоим этому диапазону имя Средний_балл. Установив курсор в ячейку В20, вводим формулу: =ВПР(A20;Средний__балл;2). Задача 2.4. В ячейках А3:А20 расположены числа, представляющие собой денежные суммы, а в ячейках В3:В20 – вид валюты. Получить в ячейках С3:С20 эквивалентные суммы в белорусских рублях. Курсы валют по отношению к белорусскому рублю приведены в ячейках F3:H3.. Решение 1 вариант. Введем в ячейку С3 формулу: =ЕСЛИ(B3=$F$2;A3*$F$3;ЕСЛИ(B3=$G$2;A3*$G$3; ЕСЛИ(B3=$H$2;A3*$H$3;"нет курса"))). Здесь предусмотрен случай вывода фразы «нет курса», если обозначение курса валюты в диапазоне В3:В20 отсутствует в диапазоне F2:H2. Распространим формулу на диапазон С4:С20. 2 вариант. Рассмотрим вариант решения задачи с использованием функций ГПР и ЕОШИБКА: Введем в ячейку С3 формулу

=ЕСЛИ(ЕОШИБКА(ГПР(B3;$F$2:$H$3;2;ЛОЖЬ)); "нет курса"; ГПР(B3;$F$2:$H$3;2;ЛОЖЬ)*A3), которую распространим затем на диапазон С4:С20.

33

Рисунок 21

Задача 2.5. В ячейках A1:Е1 расположены числа, представляющие собой денежные суммы. Произвести начисление процентов по этим суммам, если оно производится в соответствии со следующей таблицей: Сумма до 100000 включительно От 100001 до 300000 От 300001 до 700000 От 700001 до 1000000 От 1000001 до 5000000 От 5000001

34

Процент от суммы 5 % 4 % 3 % 2,5 % 2 % 1 %

Решение

1 вариант. Пусть исходные данные (денежные суммы) расположены в диапазоне А1:Е1. Установим курсор в ячейку А2 и введем в нее формулу: =ЕСЛИ(A1<=0;0;ЕСЛИ(A1<=100000;0,05*A1; ЕСЛИ(A1<=300000;0,04*A1; ЕСЛИ(A1<=700000;0,03*A1; ЕСЛИ(A1<=1000000;0,025*A1; ЕСЛИ(A1<=5000000;0,02*A1;0,01*A1)))))).

Задача 2.6. В ячейке А2 находится денежная сумма, которую положили в банк под некоторый процент, указанный в ячейке В2. Через сколько лет вклад утроится? Решение. Расположим исходные данные так, как на рисунке 22 (диапазон А1:В2). Ячейкам А2 и В2 присвоим имена Сумма и Процент соответственно. Предположим, что начисляются сложные проценты.

1 вариант. В ячейку В4 введем формулу: =Сумма+Сумма*Процент, в В5 – формулу =B4+B4*Процент.

Выполним затем копирование этой формулы на диапазон В2:Е2. Ниже в таблице приведен результат начислений (вторая строка) для сумм в первой строке: Рисунок 22

В ячейки С4 и С5 введем формулы

2 вариант. Задача решается с использованием функции ГПР. Справочные данные расположим в таблице как показано ниже, причем в первой строке данные отсортированы по возрастанию. Этому диапазону присвоим имя «таблица_начислений».

=B4/Сумма и =B5/Сумма. Распространим содержимое ячеек В5 и С5 вниз до тех пор, пока в очередной ячейке столбца С не получится первое число, равное или большее 3. Тогда в соответствующей ячейке столбца А получим нужное количество лет (целое). Результат работы формулы приведен на рисунке 23.

Установим курсор в ячейку А2 и введем в нее формулу:

=ГПР(А1;таблица_начислений;2)*А1. Распространим формулу на диапазон В2:Е2.

35

36

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

3 вариант. Обозначим через х неизвестное количество лет. Воспользуемся математическими соотношениями, приведенными ниже: 3000000 = 1000000 * 1,07 x ⇒ 3 = 1,07 x ⇒ ln 3 = x * ln1,07 ⇒ x =

ln 3 . ln 1,07

Применив эту формулу, получим такой же результат. Задача 2.7. В ячейках А1:Н1 находится информация, полученная импортом из документа, в котором отрицательные числа отображаются так, что знак «–» расположен справа от числа (рис.24). Получить в ячейках А2:Н2 числовую информацию для обработки в Excel.

Рисунок 23

2 вариант. В о с п о л ь з у ем с я ф и н а н с ов ой ф у н к ц и е й КПЕР(ставка;платеж;нз;бз;тип), которая возвращает общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки. В нашем случае ставка равна 7 %, плата не производится, то есть равна 0, нз равно -1000000, бз равно 3000000:

Рисунок 24

Решение. Воспользуемся функциями обработки текста ЛЕВСИМВ и ДЛСТР. Поместим курсор в ячейку А2 и введем в нее формулу: =ЕСЛИ(ЕЧИСЛО(A1);A1;ЛЕВСИМВ(A1;ДЛСТР(A1)–1)*(–1)). Функция ЕЧИСЛО возвращает значение ИСТИНА, если значение является числом. Распространяем формулу из ячейки А2 на диапазон В2:Н2 (рис. 25).

=КПЕР(Процент;0;-1000000;3000000). Знак минус означает, что мы отдаем свои деньги (если бы брали сумму как кредит, например, то нз было бы положительно). В ответе получаем 16,24, что означает, что на семнадцатом го37

Рисунок 25

38

Задача 2.8. Пусть в ячейках А4:А14 находятся числа, представляющие собой денежные суммы. Необходимо вывести эти суммы соответственно в ячейки В4:В14 так, чтобы запись занимала 25 позиций, причем пустые позиции должны быть заполнены знаком «*». Решение. Воспользуемся функциями обработки текста ПОВТОР и ДЛСТР. Установим курсор в ячейку В4 и введем в нее формулу: =A4&ПОВТОР("*";20–ДЛСТР(A4))&" руб.".

Рисунок 27 Рисунок 26

Распространив эту формулу на весь диапазон В5:В14, получим результат (рис. 26). Задача 2.9. Задана таблица, содержащая фамилию служащего и его табельный номер. Вывести фамилию служащего с заданным табельным номером. Решение. Пусть исходные данные расположены так, как на рисунке 27. В ячейку D2 вводится табельный номер служащего.

Введем в ячейку Е2 формулу =ВПР(D2;A2:B12;2;ЛОЖЬ). В результате получим:

Задача 2.10. Пусть задана таблица с информацией о продавцах (фамилия, срок работы, объем продаж) и две дополнительные таблицы с коэффициентами для вычисления комиссионных в зависимости от стажа и объема продаж. Следует произвести начисления, подобрав подходящий коэффициент каждому сотруднику. Решение. Пусть исходные данные расположены так, как на рисунках 28–29. Поместим курсор в ячейку D2. Для подбора подходящего коэффициента каждому сотруднику следует воспользоваться следующей формулой:

=ВПР(C2;ЕСЛИ(B2<3;$G$2:$H$8;$J$2:$K$7);2). 39

40

Рисунок 30

Рисунок 28

Распространим ее на диапазон D3:D12. Поместим в ячейку Е2 формулу =С2*D2 и распространим ее на диапазон Е3:Е12.

Задача 2.11. Предположим, что Вы намерены выкупить страховку, по которой выплачивается по А ед. в конце каждого месяца в течение В последующих лет. Стоимость ренты составляет С ед, а. выплачиваемые деньги принесут D % годовых. Определить, будет ли это хорошим способом инвестировать капитал?

ячейку Е2 и введем в нее формулу

Решение. Разместим исходные данные так, как на рисунке слева. Поместим курсор в

=ПС($D$2/12;$B$2*12;$A$2;;0).

Рисунок 29

На рисунке 30 показан результат работы этой формулы.

41

При указанных исходных данных получаем, что текущий объем вклада составит -59 777,15 ед, Результат получается отрицательный, поскольку он представляет собой деньги, которые необходимо выплатить. Настоящий объем вклада (59 777,15 ед,) меньше, чем запрашиваемая цена (60 000 ед,). Следовательно, это не самый лучший способ инвестирования денег. Задача 2.12. Составить таблицу для вычисления коэффициентов наращения суммы вклада на интервал от 1 года до 10 лет под интервал процентов от 1 % до 9 % годовых (предполагается, что на проценты по вкладу начисляются проценты). 42

Решение 1 вариант. Воспользуемся смешанными ссылками. Поместим в ячейки А2:А11 интервал лет, а в ячейки В1:J1 – проценты по вкладам. Установим курсор в ячейку В2 и введем в нее формулу: =(100 %+B$1)^$A2. Распространим ее на весь остальной диапазон В2:J11. На рисунке 31 представлен результат работы этой формулы. Рисунок 32

Рисунок 31

3 вариант. Воспользуемся финансовой функцией БЗ, которая вычисляет будущее значение вклада. Установим курсор в ячейку А1 и введем в нее формулу: =БЗ(СТОЛБЕЦ(A:A)/100;СТРОКА(1:1);;-1;1). 4 вариант. В ячейки В2:J2 поместим единицы (начальная сумма принята за единицу). Введем в ячейку В3 формулу для получения коэффициента наращения за 1 год: =B2+B2*B$1 и распространим ее на весь остальной диапазон В2:J12 (рис. 33)

2 вариант. Воспользуемся функциями СТРОКА и СТОЛБЕЦ (они определяют номер строки и столбца соответственно), чтобы опустить вспомогательный столбец с номерами лет и вспомогательную строку с процентами. Установим курсор в ячейку А1 и введем в нее формулу: =(1+СТОЛБЕЦ(A:A)/100)^СТРОКА(1:1), а затем распространим ее на весь остальной диапазон В2:J11. На рисунке 32 представлен результат работы этой формулы.

Рисунок 33

Задача 2.13. Определить по дате рождения человека день недели его рождения, год рождения по восточному календарю и знак зодиака, а также количество прожитых дней. 43

44

Решение. Воспользуемся вспомогательными таблицами, в которых расположены год по восточному календарю, знаки зодиака и дни недели. Введем в ячейку А1 дату рождения человека и присвоим этой ячейке имя дата_рождения. Тогда для определения дня недели рождения, года рождения по восточному календарю, знака зодиака и количества прожитых дней воспользуемся формулами: =ВПР(ДЕНЬНЕД(день_рождения);недели;2), =ВПР(ОСТАТ(ГОД(день_рождения);12);год;2), =ВПР(день_рожден;зодиак;2), =СЕГОДНЯ()-день_рождения. Имена недели, год, зодиак присвоены соответствующим диапазонам ячеек.

Задачи для самостоятельного решения 1. Дан список работников предприятия с указанием фамилий и коэффициентов трудового участия - КТУ (взять значения от 0 до 2 при норме 1). Задана также сумма премии (в рублях), которая подлежит распределению пропорционально КТУ. Рассчитать размер премии каждому работнику, которую следует 45

уменьшить на величину подоходного налога. Найти минимальный и максимальный размер премии. 2. Дан список работников предприятия с указанием фамилий, должностных окладов и сумм штрафов для некоторых работников (в рублях), которые вычитаются из окладов работников. Задан также размер премии, которая подлежит распределению пропорционально окладам среди всех работников. К этой премии добавляется сумма штрафов. Рассчитать размер премии каждому работнику и общую сумму выплат с учетом подоходного налога. Вычислить средний размер премии. 3. Дан список работников фирмы с указанием фамилий и зарплаты (в рублях). Рассчитать величину средней зарплаты и суммы доплат, размеры которых определяются по правилу: А ед., если зарплата работника составляет 50 % – 67 % от средней зарплаты; В ед., если зарплата работника составляет 49 % и менее от средней зарплаты. Рассчитать также общую сумму выплат каждому работнику (с учетом окладов, доплат и подоходного налога). 4. Дан список клиентов типографии, печатающей бланки, проспекты, листовки и т.п. В списке имеется графа «Постоянный клиент» с пометками «Да» для части клиентов. Известны соответствующие объемы заказов клиентов в количестве экземпляров. Рассчитать стоимость каждого заказа с учетом следующих расценок: печать до 1000 экз. – А ед. за лист; от 1000 до 2000 экз. – В ед. за лист; свыше 2000 экз. – С ед. за лист (С<В<А). Постоянным клиентам предоставляется скидка Н % от расчетной суммы заказа. Дополнительно определить максимальную величину заказа (в деньгах и экземплярах). 5. Дан список студентов с указанием фамилий и результатов сдачи трех экзаменов (оценки – «1», «2», «3», «4», «5», «6», «7», «8» «9», «10). Размер базовой стипендии составляет А ед., надбавка при сдаче экзаменов на «8», «9» и «10» – В %; надбавка при сдаче только на «9» и «10» – С % (в процентах к базовой стипендии). Рассчитать для каждого студента размер его стипендии, которая не назначается при хотя бы одной оценке ниже «4». 6. Известен список вкладчиков банка – физических лиц с указанием фамилий, суммы, срока вклада (6 или 12 месяцев) и договорные годовые процентные ставки по каждому из вкладов (от 46

18 % до 25 %). Определить величину вклада каждого клиента банка на момент получения вклада с учетом того, что часть дохода по вкладу, которая превышает 3/4 действующей ставки рефинансирования Центрального банка, облагается налогом в размере А %. Ставку рефинансирования Центрального банка принять равной В %. 7. Дан список магазинов, для которых известны объемы товарооборота за летние месяцы текущего года. Определить суммарный объем товарооборота для каждого магазина за летний период и среднемесячное его значение. Рассчитать долю участия каждого предприятия в суммарном объеме товарооборота всех магазинов (в процентах). 8. Дан список работниц с указанием фамилий, окладов и количества детей-иждивенцев у каждой из работниц. Рассчитать сумму доплат на одного ребенка и всех детей в семье каждой работницы и общую сумму к выплате (с учетом оклада, доплаты и подоходного налога). Доплата производится по системе: при наличии 1 ребенка – А руб., 2-х – В руб. на каждого ребенка, 3-х и более детей – С руб. на каждого ребенка (С<В<А). 9. У владельца пакетов акций имеется список наименований предприятий, число акций по каждому из предприятий, номинальная и рыночная стоимость одной акций и размер дивидендов (в процентах к номиналу). Рассчитать для каждого предприятия рыночную стоимость пакета акций, сумму дивидендов и вывести сообщение «Докупить акции», если сумма дивидендов превышает 10 % от рыночной стоимости акций, «Продавать акции», если сумма дивидендов ниже 1 % от рыночной стоимости акций. 10. Дан список вкладчиков банка с указанием фамилий, суммы, срока вклада (3, 6 или 12 месяцев) и примечания о ежемесячном съеме процентов («Да» или «Нет»). Известна также единая месячная процентная ставка по депозитным вкладам. Рассчитать итоговые суммы отдельных вкладов по истечении срока вклада с учетом двух возможных ситуаций: с ежемесячным съемом процентов или с капитализацией процентов. 11. Известны значения курса доллара США (в рублях), установленные Центральным банком на 1-е число каждого месяца, а также месячные индексы инфляции в течение года (в процентах). Рассчитать уровень инфляции с начала года на 1-е 47

число очередного месяца (в процентах) и реальный (с учетом инфляции) курс валюты в относительных единицах (относительно курса на 1-е января прошедшего года). Дополнительно вычислить среднегодовой курс доллара США (в рублях). 12. Дан список наименований товаров с указанием их цен на начало года. Известен уровень инфляции в процентах для каждого месяца года. Рассчитать цены каждого товара на 1-е число каждого месяца и конец года, компенсирующие потери от инфляционного процесса. 13. Дан список исполнителей завершенных работ. Известны фамилия, стоимость работы, даты планового и фактического окончания работы. Если работа выполнена раньше, чем за 15 дней, исполнитель поощряется денежной суммой в размере А % от стоимости работы. Если работа выполнена с опозданием, то исполнителю начисляется пеня размером в В % от стоимости работ за каждый день просрочки. Рассчитать итоговые суммы выплат исполнителям работ на момент их завершения. 14. Дан список наименований товаров – овощей, хранящихся на базе в течение года. Известен начальный объем запаса товара в натуральных единицах по каждому из наименований. Рассчитать убыль по каждому наименованию за год, если естественная убыль осеннего периода составляет – 3 %, зимнего периода 2 %, весеннего периода – 4 %, летнего периода – 5 %. Дополнительно определить максимальную убыль в натуральных единицах 15. Дан список работниц с указанием их фамилий и окладов. Накануне Международного женского Дня 8 марта им выделена сумма премии. Рассчитать размер премии для каждой из работниц при условии распределения суммы премии пропорционально окладам работниц. Рассчитать также общую сумму выплат с учетом подоходного налога. Дополнительно вычислить средний размер премии. 16. Дан список клиентов банка, в котором указаны размер ссуды, процент, под который она выдана, и дата ее погашения. За каждый день просрочки возврата ссуды на клиента начисляется пеня в размере А % от размера ссуды. Определить суммарную задолженность каждого клиента перед банком на текущую дату. 17. Дан список клиентов банка, арендующих сейфы, с указанием сроков аренды (в днях). В списке имеется графа «Постоянный клиент» с пометками «Да» для части клиентов. Определить 48

стоимость аренды для каждого клиента с учетом следующих тарифов: до 30 дней аренды – А руб./сутки; от 30 до 60 дней – В руб./сутки и свыше 60 дней – С руб./сутки (С<В<А). Постоянным клиентам предоставляется скидка в размере Н % от расчетной стоимости аренды. 18. Дан список продуктов питания, включающий в себя наименование, дату изготовления и срок годности (в месяцах). Определить на текущую дату продукты с просроченным сроком годности (вывести сообщение «Негоден!»). Для остальных продуктов определить оставшийся интервал годности в днях. В случае, если до окончания срока годности осталось менее месяца, вывести сообщение «Срочная реализация». 19. Имеется список торговых предприятий, для которых известны значения прибыли, полученной за каждый месяц последнего квартала года. Определить суммарную прибыль, полученную каждым предприятием за квартал, и среднемесячное ее значение. Рассчитать долю участия каждого предприятия в получении общей суммы прибыли, полученной всеми предприятиями (в процентах). 20. Известен список вкладчиков банка с указанием фамилий, суммы и срока вклада (3, 6 или 12 месяцев) и две годовые процентные ставки, не зависящие от срока вклада: А % для сумм вкладов, не превышающих В руб. и С % для сумм от В руб. и выше. Определить величину вклада каждого клиента банка на момент получения вклада. 21. Дан список сотрудников с указанием их фамилий и окладов. Задана сумма денежной премии – А руб. Распределить премию пропорционально окладам сотрудников. Рассчитать суммы премий и общую сумму выплат отдельным сотрудникам (с учетом оклада и подоходного налога). Дополнительно найти максимальный и минимальный размер премии. 22. Дан список непродовольственных товаров. Для каждого товара известны наименование, дата производства, начальная цена и конечная дата реализации. Определить цены товаров на текущую дату при условии, что если до даты реализации остается менее месяца, то цена товара уменьшается на А %, а если конечный срок реализации прошел, то цена товара уменьшается на В % (А<В). 49

23. Дан список комитентов комиссионного магазина с указанием наименования сданного на комиссию товара, цены и даты сдачи товара на реализацию. Если товар не реализован в течение первого месяца, то его цена уменьшается на 20 %, еще через месяц на очередные 30 %, а по истечении третьего месяца товар возвращается комитенту (в графе «Примечание» делается пометка «Возврат». Рассчитать цены нереализованных товаров на текущую дату. 24. Известен список вкладчиков банка с указанием фамилий, суммы и срока вклада (3, 6 или 12 месяцев) и три годовые процентные ставки, не зависящие от суммы вклада: 18 % для срока вклада 3 месяца, 19 % для срока 6 месяцев и 22 % для срока 12 месяцев. Определить величину вклада каждого клиента банка на момент получения им своего вклада. 25. Дан прайс-лист на товары бытовой техники, в котором цены указаны в долларах США с учетом НДС (А %) и налога с продаж (В %). Пересчитать цены в рублевый эквивалент по текущему курсу и выделить суммы НДС и налога с продаж. Для товаров, имеющих в графе «Скидка» пометку «Да», снизить расчетную цену на С %. 26. Задать таблицу из двух столбцов: Страны, Столицы. (Австрия, Болгария, Германия, Греция, Италия, Норвегия, Франция, Чехия.), (Вена, София, Берлин, Афины, Рим, Осло, Париж, Прага). По названию страны, заданному в ячейке А1, определить столицу. 27. В японском календаре принят 60-летний цикл, состоящий из пяти 12-летних подциклов. Подциклам соответствуют цвета: зеленый, красный, желтый, белый и черный. Внутри каждого подцикла годы носят названия животных: крысы, коровы, тигра, зайца, дракона, змеи, лошади, овцы, обезьяны, курицы, собаки и свиньи. 1984 год – год зеленой крысы – был началом очередного цикла. В ячейке А8 задать номер года, а в ячейке В8 вычислить его название по японскому календарю. 28. Астрологи делят год на 12 периодов по знакам зодиака. 20.01–18.02 19.02–20.03 21.03–19.04 20.04–20.05

Водолей Рыбы Овен Телец

23.07–22.08 23.08–22.09 23.09–22.10 23.10–22.11 50

Лев Дева Весы Скорпион

21.05–21.06 22.06–22.07

Близнецы Рак

23.11–21.12 22.12–19.01

Стрелец Козерог

По заданной дате определить знак зодиака. 29. Вычислить ежегодную сумму амортизации оборудования, приобретенного за А руб. и имеющего остаточную стоимость В руб. Оборудование имеет срок эксплуатации – С лет. Для вычислений использовать функцию АМР. 30. Вычислить сумму ежемесячных процентных выплат за кредит, взятый в размере А руб. под В % годовых со сроком погашения С лет. Для вычислений использовать функцию ППЛАТ. 31. Вычислить количество платежей при ежемесячных выплатах в А руб. за кредит В руб., взятый под С % годовых. Для вычислений использовать функцию КПЕР. 32. Вычислить накопленную сумму, если в течение трех лет в конце каждого месяца был произведен вклад в А руб. под В % годовых. Для вычислений использовать функцию БЗ. 33. Вычислить сумму кредита, который можно получить, если взять кредит в долларах США на А лет под В % годовых, выплачивая проценты один раз в конце года. Клиент может выплатить не более С $. Для вычислений использовать функцию ПЗ. 3. Использование надстройки «Поиск решения» Надстройка «Поиск решения» позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Это средство работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки. F = f ( x1 , x 2 ,..., x n ) → max(min, const ) (1) g i x j ≤ (=, ≥)bi , (2)

( )

d j ≤ xj ≤ Dj ,

(3) 51

i = 1, m, j = 1, n,

(4) (5) Рисунок 34

На рисунке 34 представлен общий вид модели задачи такого типа (это задачи оптимального управления), где xj может принимать либо действительные, либо целочисленные значения. Формула (1) определяет целевую функцию, формула (2) определяет ограничения, а формула (3) — граничные условия. В зависимости от вида функции f в формуле (1) задача может относиться к классу задач линейного или нелинейного программирования. В зависимости от того, являются ли xj целыми или действительными величинами, задача может относиться к задачам целочисленного программирования или нет. Приведем общие рекомендации по использованию надстройки «Поиск решения». 1. Перед использованием надстройки необходимо выполнить запись на бумаге математической постановки задачи. Точное отображение математической модели в табличную модель на рабочем листе Excel облегчает понимание табличной модели. 2. Необходимо тщательно проверять знаки неравенств во всех ограничениях. В противном случае можно получить противоречивую модель. Следует избегать ограничений в виде равенств, если это не продиктовано моделируемой ситуацией. 3. Не следует забывать об ограничениях неотрицательности для изменяемых ячеек. Это ограничение можно ввести в окне диалога Параметры (рис. 35) путем установки соответствующего флажка или с помощью явного задания ограничений неотрицательности в окне диалога Добавить ограничения. 4. Плохо масштабированные модели часто вызывают преждевременную остановку средства «Поиск решения» или приводят к выводу сообщений об ошибке. Следует избегать ситуаций, когда некоторые переменные принимают значения, отличающиеся по величине на пять или более порядков по отношению к другим переменным. Например, объемы продук52

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

Рисунок 35

5. Использование негладких функций, таких, как функция ЕСЛИ в формулах вычисления целевой функции или функций ограничений может спровоцировать проблемы в работе средства «Поиск решения», так как ни один известный алгоритм оптимизации не гарантирует в таких условиях нахождение оптимального решения. 6. При задании в диалоговом окне «Поиска решения» правых частей ограничений следует указывать ссылки на ячейки табличной модели, а не формулы.

3. Чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, устанавливаем переключатель в положение «максимальному значению» (рис.36). Чтобы минимизировать значение целевой ячейки, устанавливаем переключатель в положение «минимальному значению». Чтобы установить значение в целевой ячейке равным некоторому числу, устанавливаем переключатель в положение значению и вводим в соответствующее поле требуемое число. 4. В поле Изменяя ячейки вводим имена или адреса изменяемых ячеек, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажимаем кнопку Предположить. 5. В поле Ограничения вводим все ограничения, накладываемые на поиск решения. 6. Нажимаем кнопку Выполнить. 7. Чтобы сохранить найденное решение, следует установить переключатель в диалоговом окне Результаты поиска решения в положение Сохранить найденное решение. Чтобы восстановить исходные данные, устанавливаем переключатель в положение Восстановить исходные значения. 8. Чтобы прервать поиск решения, следует нажать клавишу ESC. Microsoft Excel пересчитает лист с учетом найденных значений влияющих ячеек.

Постановка задачи и оптимизация модели с помощью процедуры поиска решения 1. В меню Сервис выберите команду Поиск решения. Если команда Поиск решения отсутствует в меню Сервис, следует установить соответствующую надстройку. 2. В поле Установить целевую ячейку следует ввести адрес или имя ячейки, в которой находится формула оптимизируемой модели. 53

54

1 2 3 4

0 1 2 -

0 5 6 -

0 2 3 4

0 8 9 12

0 1 -

0 3 -

Решение Обозначим через хij (i=1,2,3,4; j=1,2,3) наличие проекта і предприятия j среди отобранных. Так как по каждому предприятию должен приняться не более чем один проект, то должны выполняться следующие соотношения: 4

∑x

Рисунок 36

i =1

Примеры решения задач Задача 3.1. Совет директоров фирмы изучает предложения по наращиванию производственных мощностей на трех принадлежащих фирме предприятиях. Для расширения всех трех предприятий фирма выделяет 5 миллионов долларов. Каждое предприятие представляет на рассмотрение проекты, которые характеризуются величинами (в миллионах долларов) суммарных затрат c и доходов R , связанных с реализацией каждого из проектов. Соответствующие данные приведены в таблице, в которую включены также проекты с нулевыми затратами. Это позволяет учесть возможность отказа от расширения какоголибо предприятия. Каждый проект может быть принят или отклонен независимо от решения по другим проектам. Цель фирмы – получение максимального дохода от инвестиций в объеме 5 миллионов долларов. Предприятие Проект

1 с1

2 R1 55

с2

3 R2

с3

R3

ij

≤ 1, для всех j = 1,3.

Представим данные в виде двух отдельных таблиц с затратами и доходами. В тех случаях, когда проект отсутствует, будем считать, что затраты по проекту есть, а доходы отрицательны (например, 1000 и –1000 соответственно).

1

1 0

затраты 2 0

3 0

2

1

2

1

3

2

3

0

4

1000

4

1000

1

1 0

доходы 2 0

3 0

2 3

5 6

8 9

3 0

4

-1000

12

-1000

Проект

Проект

По условию суммарные расходы не должны превысить сумму 5 миллионов: 56

4

3

∑∑ c x

ij ij

i =1 j =1

≤ 5.

Суммарный доход от реализации этих проектов при таком выборе составит 4

3

∑∑ i =1 j =1

xij Rij .

Ниже приведена формализованная модель. 4

3

f = ∑∑ xij R4 → max, i =1 j =1

⎧ 4 ⎪∑ xij ≤ 1 для всех j = 1,3, ⎪ i =1 ⎪⎪ 4 3 ⎨∑∑ cij xij ≤ 5, ⎪ i =1 j =1 ⎪ ⎧0 ⎪ xij = ⎨ (i = 1,4; j = 1,3). ⎪⎩ ⎩1 Воспользуемся для решения задачи надстройкой «Поиск решения». Для этого разместим в ячейках B27:D30 изменяемые переменные хjj (i = 1,4; j = 1,3) . В ячейку B31 поместим формулу

=СУММ(B27:B30), которую скопируем затем на диапазон С31:D31. В ячейке F13 расположена формула =СУММПРОИЗВ(B13:D16;B27:D30), а в ячейке F14 – формула: =СУММПРОИЗВ(B27:D30;B20:D23). На рисунке 37 показано окно диалога «Поиск решения». Решение представлено на рисунке 38. 57

Рисунок 37

58

Требуется составить план перевозок, при котором все заявки были бы выполнены, и при этом общая стоимость всех перевозок была минимальной. Решение. Нетрудно заметить, что сумма всех запасов равна сумме всех заявок (125). Обозначим через хij количество единиц товара, отправленного из пункта производства i (1<=i<=4) в пункт потребления j (1<=j<=5), а через сij – стоимость перевозки из пункта i в пункт j. Тогда должны иметь место следующие соотношения: 5

∑x j =1

1j

4

∑x i =1

i2

5

5

5

4

j =1

j =1

j =1

i =1

≤ 48, ∑ x 2 j ≤ 30, ∑ x3 j ≤ 27, ∑ x 4 j ≤ 20, ∑ xi1 ≥ 18, 4

4

4

i =1

i =1

i =1

≥ 27, ∑ xi 3 ≥ 42, ∑ xi 4 ≥ 12, ∑ xi 5 ≥ 26,

xij ≥ 0, (1 ≤ i ≤ 4,1 ≤ j ≤ 5), xij − целые. Необходимо минимизировать целевую функцию вида 4

Рисунок 38

Задача.3.2. Имеется 4 пункта производства А1, А2, А3 и А4, в которых сосредоточены запасы какого-то однородного товара в количествах 48, 30, 27 и 20 единиц соответственно. Кроме того, имеется 5 пунктов потребления В1, В2, В3, В4 и В5, подавших заявки на 18, 27, 42, 12 и 26 единиц товара соответственно (рисунок 39). Известна стоимость (в таблице на рисунке 39 стоимость расположена на пересечении соответствующей строки и столбца) перевозки единицы товара от каждого пункта производства до каждого пункта потребления. A1 A2 A3 A4 Заявки

B1 10 6 8 7 18

B2 8 7 7 5 27

B3 5 8 10 4 42

B4 6 6 8 6 12

B5 9 5 7 8 26

Рисунок 39

59

Запасы 48 30 27 20 125

5

∑∑ с i =1 j =1

ij

xij → min .

Разместим исходные данные для решения на листе MS Excel, например, так, как на рисунке 40. На этом листе формулы для целевой функции, ограничений по запасам и заявкам имеют соответственно вид: =СУММПРОИЗВ(C5:G8;C12:G15), =СУММ(C12:G12), =СУММ(C13:G13), =СУММ(C14:G14), =СУММ(C15:G15), =СУММ(C12:C15), =СУММ(D12:D15), =СУММ(E12:E15), =СУММ(F12:F15), =СУММ(G12:G15). 60

раза меньше первого. Определить первоначальную длину нити. Решение. Построим модель. Пусть ячейка А1 является изменяемой. В ней будет располагаться длина стороны первоначального квадрата. Тогда сторона нового квадрата вычисляется по формуле =A1-9. Поместим эту формулу в ячейку А2. В ячейках Е1 и Е2 располагаются площади исходного и нового квадратов соответственно. В ячейке А4 расположен периметр исходного квадрата, а в ячейке Е4 – отношение периметров исходного и нового квадратов (рисунок 42). Выполнив команду Сервис/Поиск решения, приведем окно диалога к виду, как на рисунке 43. Рисунок 40

Полученное в результате решение приведено на рисунке 41. Рисунок 42

Рисунок 41

Задача 3.3. От нити, равной периметру некоторого квадрата, отрезано 36 см. Укороченная таким образом нить представляет собой периметр другого квадрата, площадь которого в 2,25 61

Рисунок 43

Результат приведен на рисунке 44. 62

=0,05*Заработная_плата =0,1*(Материалы_и_комплектующие+Заработная_плата) =СУММ(C3:C7) Рисунок 44

Задача 3.4. Таблица на рисунке 45 представляет смету расходов на выполнение некоторых работ, где отдельные статьи сметы связаны между собой определенными зависимостями. Эти зависимости для ячеек С5:С11 приведены на рисунке 46. Анализ сметы сводится к сопоставлению отдельных статей расходов, общих расходов и прибыли с общей стоимостью работ. Определить: 1) при какой общей стоимости работ прибыль достигнет величины 500 ед.; 2) при какой общей стоимости работ отношение прибыли к общей стоимости достигнет величины 50 %.

Рисунок 45

=0,05*(Всего-Услуги_сторонних_организаций) =0,05*Всего =Всего-Итого-Услуги_сторонних_организаций-Накладные_расходы Рисунок 46

Решение. 1. Присвоим ячейкам С11 и С12 имена Прибыль и Всего соответственно. Выполнив команду Сервис/Поиск решения, установим ячейку Прибыль в качестве целевой, а в качестве изменяемой – ячейку Всего (рис. 47), получим решение (рис. 48). 2. Введем в ячейку D12 формулу =Прибыль/Всего и отформатируем ее с помощью процентного формата. Выполнив команду Сервис/Поиск решения, установим ячейку D12 в качестве целевой, значение которой равно 50 %, а в качестве изменяемой – ячейку Всего (рис. 49), получим решение (рис. 50).

. Рисунок 47

=0,37*Заработная_плата 63

64

Рисунок 48

Рисунок 50

Задачи для самостоятельного решения 1. Для перевозки А т овощей было потребовано несколько грузовиков определенной грузоподъемности. За неимением свободных грузовиков этой грузоподъемности гараж выслал грузовики с грузоподъемностью на полтонны меньше и дал таких грузовиков на один больше. Сколько тонн овощей взял каждый из высланных грузовиков?

2. Два автомобиля выезжают одновременно в пункт, отстоящий от него на А км. Скорость первого на 10 км больше скорости второго, и поэтому первый автомобиль приезжает на место на 1 час раньше второго. Определить скорости автомобилей. 3. Заданы координаты 20 точек на плоскости (множество «Населенные пункты»). Найти такую точку на этой плоскости, суммарное расстояние от которой до остальных точек множества «Населенные пункты» минимально. 4. Стулья, произведенные на предприятии, продаются на той же неделе. Удельная валовая прибыль (доход – расход) составляет А ед. для каждого проданного стула марки С и В ед. для каждого стула марки М. Для сборки стула нужны длинные и короткие штифты и одно из двух типов сидений, которые имеются на складе в ограниченном количестве. Запас длинных и коротких штифтов, которые можно будет использовать на следующей неделе, составляет 1280 и 1600 штук соответственно. Для производства одного стула марки С требуется 8 длинных и 4 коротких штифта, а для производства стула марки М – 4 длинных и 12 коротких штифтов. Запас ножек на следующую неделю составляет 760 штук. Для производства одного стула любого типа требуется 4 ножки. Запас прочных и облегченных сидений составляет 140 и 120 штук соответственно. Для производства стульев типа С используются прочные сиденья, а для стульев марки М – облег66

Рисунок 49

65

ченные. Общее число произведенных стульев по договору не может быть менее 100. Определить, сколько стульев каждой марки необходимо произвести на следующей неделе для получения максимальной прибыли. 5. Наилучшим образом внести сумму в А руб. на годовые депозиты в три банка. Условия банка №1: 10 % годовых при ежеквартальном начислении процентов. Условия банка №2: 12 % годовых при полугодовом начислении процентов. Условия банка №3: 9 % годовых при ежемесячном начислении процентов. Не следует доверять одному банку более половины капитала. Банки не принимают сумм меньших 50000 руб. 6. Цех мебельного комбината выпускает трельяжи, трюмо и тумбочки под телевизоры. Норма расхода материала в расчете на одно изделие, плановая себестоимость, оптовая цена предприятия, плановый (месячный) ассортимент и трудоемкость единицы продукции приведены в таблице 4. Запас древесностружечных плит, досок еловых и березовых – 90, 30 и 14 м2. Плановый фонд рабочего времени составляет 16800 человекочасов. Исходя из необходимости выполнения плана по ассортименту и возможности его перевыполнения по отдельным (или даже всем) показателям, построить модель, на основе которой можно решить а) задачу максимизации объема реализации (за плановый период); б) задачу максимизации прибыли (за тот же период). Таблица 4 Показатели Норма расхода материала, м2 Древесностружечные плиты Доски: еловые березовые Трудоемкость, чел.-ч. Плановая себестоимость 67

Трельяжи 0,032 0,020 0,005 10,2 88,81

Трюмо

Тумбочки

0,031 0,020 0,005 7,5 63,98

0,038 0,008 0,006 5,8 29,60

Продолжение таблицы Оптовая цена предприятия 93 76 30 Плановой ассортимент 350 290 1200 7. Фабрика выпускает кожаные брюки, куртки и пальто специального назначения в ассортименте, заданном соотношением 2:1:3. В процессе изготовления изделия проходят три производственных участка – дубильный, раскройный и пошивочный. Фабрика имеет практически неограниченную сырьевую базу, однако сложная технология предъявляет высокие требования к квалификации рабочих. Численность их в рамках планируемого периода ограничена. Время обработки изделий на каждом участке, их плановая себестоимость, оптовая цена предприятия приведены в таблице 5. Ограничения на фонд времени для дубильного, раскройного и пошивочного участков составляют соответственно 3360, 2688 и 5040 часов. Учитывая заданный ассортимент, построить модель, на основе которой можно сформулировать экстремальную задачу определения напряженного месячного плана по прибыли от реализованной продукции. Таблица 5 Показатели Норма времени на участках, чел.-ч. Дубильном Раскройном Пошивочном Полная себестоимость Оптовая цена предприятия

Брюки

0,3 0,4 0,5 15 17,5

Куртки

0,4 0,4 0,4 40,5 42

Пальто

0,6 0,7 0,8 97,8 100

8. На приобретение оборудования для нового производственного участка выделено 300 ед. Его предполагается раз68

местить на площади 45 м2. Участок может быть оснащен оборудованием трех видов – машинами стоимостью 6 ед. (здесь и далее все показатели приводятся на единицу оборудования), размещающимися на площади 9 м2, производительностью 8 тыс. ед. продукции за смену; машинами стоимостью 3 ед., размещающимися на площади 4 м2 , производительностью 4 тыс. ед. продукции за смену; машинами стоимостью 2 ед., размещающимися на площади 3 м2 , производительностью 3 тыс. ед. продукции за смену. Построить модель, на основе которой можно сформулировать экстремальную задачу определения плана приобретения оборудования, обеспечивающего наибольшую производительность всего участка. 9. В плановом году строительные организации города переходят к сооружению домов типов Д-1, Д-2, Д-3 и Д-4. Данные о количестве квартир разного типа в каждом из указанных типов домов и их плановая себестоимость приведены в таблице 6.

Таблица 6 Показатели Типы квартир Однокомнатные Двухкомнатные Смежные Несмежные Трехкомнатные Четырехкомнатные Плановая себестоимость

Д-1

Д-2

Д-3

Д-4

10

18

20

15

40 60 20 83000

20 90 10 83500

20 10 36000

60 5 45000

Годовой план ввода жилой площади составляет соответственно 800, 1000, 900, 2000 и 7000 квартир указанных типов. Исходя из необходимости выполнения плана ввода квартир (возможно его перевыполнение по всем показателям) и обеспеченности строительными материалами, построить модель и сформулировать на ее основе экстремальную задачу, анализ которой позволит обосновать объем капиталовложений в жилищное строительство на плановый год. 69

10. Компания производит две марки телевизоров – А и С. Работают два конвейера, каждый из которых выпускает телевизоры одной марки, и два цеха, занятых производством деталей для телевизоров обеих марок. Производственная мощность конвейера, выпускающего телевизоры марки А, составляет 70 телевизоров в день, а конвейера С – 50 телевизоров в день. Цех 1 производит телевизионные трубки. На производство одной телевизионной трубки для телевизора марки А требуется 1 час рабочего времени, а на производство трубки для телевизора марки С – 2 часа. В настоящее время в цехе 1 производству трубок для телевизоров обеих марок может быть уделено не более 120 часов рабочего времени в день. В цехе 2 изготавливаются корпуса телевизоров, причем для производства одного корпуса для телевизора любой марки требуется 1 час рабочего времени. Цех 2 может посвятить изготовлению корпусов не более 90 часов рабочего времени в день. Удельная валовая прибыль от реализации одного телевизора марки А и С составляет 20 и 10 единиц соответственно.. Каким должен быть дневной план производства для максимизации прибыли при условии, что компания может продать все произведенные телевизоры? 11. Завод должен переслать заказчику 1100 деталей, которые для пересылки требуется упаковать в ящики трех типов. Один ящик первого типа вмещает 70 деталей, второй – 40 деталей, третий – 25 деталей. Стоимость пересылки одного ящика первого, второго и третьего типов равна соответственно 20, 10 и 7 единиц. Построить модель, на основе которой можно сформулировать экстремальную задачу определения плана перевозки, обеспечивающего минимальную стоимость пересылки. Рассмотреть варианты, когда а) недогрузка ящиков не допускается.; б) недогрузка ящиков допускается. 12. Необходимо разменять денежную сумму в S единиц так, чтобы получить р купюр достоинством k, n и m единиц соответственно. 13. Необходимо разменять денежную сумму в S единиц так, чтобы получить минимальное суммарное количество купюр достоинством k, n и m единиц соответственно 14. На складе имеются М станков четырех типов весом k, l, m и n. Необходимо перевезти их на машине заданной грузоподъемности так, чтобы количество рейсов было минимальным. 70

15. Имеются А предметов, каждый из которых характеризуется своим весом и стоимостью. Необходимо упаковать эти предметы в «рюкзак» ограниченной вместимости так, чтобы их суммарная стоимость была максимальной. 16. Заданы координаты К селений. Построить модель, на основе которой можно сформулировать экстремальную задачу выбора пункта размещения станции скорой помощи таким образом, чтобы поездка в самое удаленное селение (по времени) занимала бы минимальное время. 17. Банка корма весом 450 г для собаки должна содержать как минимум следующее количество питательных веществ: белков – 85 г, углеводов – 140 г и жиров – 115 г. Нужно смешать четыре вида корма в различных пропорциях, чтобы получить наиболее дешевую банку собачьего корма, соответствующего требованиям по содержанию питательных веществ. Содержание питательных веществ и цена каждого вида корма в расчете на 450 г приведены в таблице 7. Таблица 7 Содержание питательных веществ и цена Корм Белки Углеводы Жиры Цена 1 4 85 198 142 2 6 142 113 170 3 3 57 57 170 4 2 85 227 57 18. Предприятие имеет возможность вложить а) до 55 млн. руб., б) до 90 млн. руб. При этом цена капитала составляет 10 %. Составьте оптимальный инвестиционный портфель из следующих делимых возможных проектов: Значения в млн. руб. А Б В Г

-30 -20 -40 -15

6 4 12 4

71

11 8 15 5

13 12 15 6

12 5 15 5

19. Анализируются 4 независимых делимых проекта. Цена капитала составляет 12 %. Бюджет ограничен А. ед. Составьте оптимальный инвестиционный портфель. А Б В Г

-31 -60 -25 -40

6 20

Значения в тыс. ед. 6 6 6 6 20 40 10

6

6

6 80

30

25

20. Определить, в каком количестве следует выпускать продукцию четырех типов А, Б, В и Г для получения максимальной прибыли, если для ее изготовления требуются ресурсы трех типов в количествах, приведенных в таблице 8.

Таблица 8 Тип продукции Ресурс

А

Б

В

Г

Наличие

Прибыль

60

70

120

130

Труд

1

1

1

1

16

Сырье

6

5

4

3

110

Финансы

4

6

10

13

100

21. Создать таблицу, приведенную на рисунке 51. В этой таблице представлены поквартальные сведения о сбыте некоторых товаров, себестоимости продукции, доходах от реализации и величине прибыли. Объем сбыта зависит от сезонного коэффициента (Кi), а также от затрат на рекламу продукции при прочих фиксированных факторах. При создании таблицы исходными данными являются величины Кi, Qi, C и R. Остальные величины вычисляются по формулам, приведенным на рисунке).

72

ка и муниципальные облигации. Необходимо инвестировать все имеющиеся средства. Таблица 9 Максимально Ожидаемый возможная сумма Варианты инвестирования доход, % инвестиций, млн. ед. Обыкновенные акции 8 5 Облигации казначейства 6 7 Фонд денежного рынка 12 2 Муниципальные облигации 9 4

Рисунок 51

Определить: • можно ли получить в IV квартале прибыль 100 000 ед., изменяя расходы на рекламу Q4; • определить величину расходов на рекламу в IV квартале (Q4), необходимую для получения прибыли 150 000 ед.; • определить максимальное значение прибыли P4 с точностью до единиц целой части числа. 22. Инвестиционная компания должна определить, куда вложить средства в размере 10 миллионов единиц. Цель – максимизировать ожидаемый доход в следующем году. Четыре варианта вложения средств представлены в таблице 9. Компания также приняла решение, что не менее 30 % средств должно быть вложено в обыкновенные акции и долгосрочные облигации казначейства и не менее 40 % — в фонды денежного рын73

23. Организация управляет четырьмя фермами, производительность которых примерно одинакова. Каждая ферма имеет определенную площадь сельхозугодий и ей требуется определенное количество часов рабочего времени, чтобы ухаживать за растениями и убирать урожай. Данные для предстоящего сезона представлены в таблице 10. Таблица 10 Используемая Ресурс рабочего Ферма площадь, акры времени в месяц, ч 1 500 1700 2 900 3000 3 300 900 4 700 2200 Организация рассматривает варианты выращивания трех культур, которые отличаются ожидаемой удельной прибылью в расчете на один акр занимаемой ими площади и требуемыми трудозатратами, что отражается в таблице 11. Общая площадь, выделенная под каждую определенную культуру, ограничена требованиями к уборочной технике. Чтобы поддержать примерно одинаковую рабочую нагрузку на ферму, руководство организации решило, что процентные отношения площадей, занятых определенными культурами, должны быть одинаковы на всех фермах. Однако ферма может выращивать произвольную комбина74

Ожидаемая прибыль на акр, ед.

A B C

Трудозатраты, часы в месяц,

Культура

Максимальная площадь, акры

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

700 800 300

2 4 3

500 200 300

лее на рисунках 52–58 показаны шаги МАСТЕРА ДИАГРАММ, приводящие к построению нужного графика на рисунке 59.

. 4. Построение графиков функций и диаграмм

Примеры решения задач Задача 4.1. Построить график функции, отражающей изменение численного состава жителей города Гродно за временной интервал с 1588 по 1987 годы.

Рисунок 52

Решение. Выделяем диапазон G17:T17 и нажимаем кнопку Мастер диаграмм на панели инструментов СТАНДАРТНАЯ или выполняем команду ВСТАВКА/ДИАГРАММА. Да75

На первом шаге выбирается тип диаграммы. В данном случае строится график. 76

Рисунок 53

На шаге 2 Мастер диаграмм пытается пронумеровать данные по оси ОХ. Эти подписи неправильные (нужно, чтобы отображались года), поэтому переходим на вкладку РЯД и заполняем строку Подписи оси Х, обводя мышью диапазон G16:T16.

77

Рисунок 54

78

Рисунок 56

Рисунок 55

Нажимаем на кнопку ДАЛЕЕ, вводим название диаграммы и обозначение по оси Y. На рисунке 54 подписи по оси ОХ правильные, но обозначение Ряд 1 лишено смысла, так как других рядов с данными нет. Поэтому переходим на вкладку ЛЕГЕНДА и снимаем флажок ДОБАВИТЬ ЛЕГЕНДУ. 79

Рисунок 57

80

Рисунок 60

19 87

19 85

19 75

19 65

19 56

19 31

300 250 200 150 100 50 0 15 88

тыс. чел.

Население г. Гродно

Рисунок 59

На рисунке 59 приведен первоначальный вариант графика. После форматирования этот же график выглядит так как на рисунке 60. Форматирование можно производить, например, установив указатель мыши на какой-либо элемент диаграммы и нажав правую кнопку мыши (рис. 61). 81

Рисунок 61

82

1987

1987

1986

1985

1980

1975

1970

1965

1959

1956

1939

1931

Диаграмму можно разместить либо на отдельном листе либо на имеющемся.

1897

Рисунок 58

300 250 200 150 100 50 0 1588

тыс. чел.

Население г. Гродно

Задача 4.2. На рабочем листе имеются данные о численности населения СССР за 1940–1953 годы. Отразить с помощью гистограммы имеющиеся данные на трех диаграммах.

Рисунок 62

Решение. Выделяем диапазон H25:I31 и вызываем МАСТЕР ДИАГРАММ. На первом шаге выбираем в списке Тип Гистограмма (рис.62). Далее на рисунках 63–66 по шагам рассматривается процесс построения первой диаграммы. Скопируем полученную диаграмму и дважды вставим копию. Активизируем первую копию и для указания новых данных перенесем мышью линию выделения данных в столбцах H, I на столбцы J, K (рис. 67) и уменьшим временной диапазон, начиная отображать данные с 1950 года. Добавим соответствующий заголовок. Затем активизируем вторую копию и для указания новых данных перенесем мышью линию выделения данных в столбцах H, I на столбцы L, M (рис. 69). Рисунок 63

83

84

тыс. чел.

140000 120000 100000 80000 60000 40000 20000 0

город село

1940 1946 1950 1951 1952 1953 Рисунок 66

Рисунок 64

Рисунок 67

Изменение численности 5000

город село

тыс. чел.

4000 3000 2000 1000 0 -1000

Рисунок 65

85

1950

1951

1952

Рисунок 68

86

1953

Рисунок 69

Рисунок 71 Физический

0,50000

-1,00000

-1,50000

Рисунок 72

88

21.08.2004

19.08.2004

17.08.2004

15.08.2004

13.08.2004

11.08.2004

09.08.2004

07.08.2004

05.08.2004

03.08.2004

01.08.2004

-0,50000

30.07.2004

0,00000 28.07.2004

Решение. Биологические ритмы могут быть описаны функциями вида sin ( 2π ( t - t0 ) / Tk ), где t – время, а Tk – периоды (физический – 23,6884, эмоциональный – 28,4261, интеллектуальный – 33,1638)., k – номер периода. Началом всех трех кривых является день рождения t = t0, sin(0)=0. Исходными данными являются дата рождения и дата начала исследования. Построим таблицу, фрагмент которой приведен на рисунке 71. В столбец А помещается формула для вычисления числа прожитых дней на момент расчета биоритмов, в столбец В – дата, в столбцы C, D, E – значения функции sin ( 2π ( t - t0 ) / Tk ), На рисунке 72 приведен фрагмент построенного графика. 87

1,00000

26.07.2004

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

Интеллектуальный

1,50000

24.07.2004

Рисунок 70

Эмоциональный

Задача 4.4.По данным таблицы построить диаграмму сравнения показателей по двум возрастным группам А и В. возрастная группа <21 21-30 31-40 41-50 51-60 61-70 >70

А 63 76 78 46 58 64 5

В 92 99 99 67 84 108 53

Решение. Пусть исходные данные занимают диапазон А1:С8. Данные по группе А введены со знаком «-» (рис. 73).

Рисунок 73

Выделяем диапазон и создаем линейчатую диаграмму. Используем подтип нормированная линейчатая диаграмма (рис. 74).

89

Рисунок 74

90

Рисунок 75

>70

>70 51-60

51-60

А

31-40

В

31-40

<21 50%

<21 -50%

А В

0%

50%

100%

Для горизонтальной оси следует использовать пользовательский формат 0 %;0 %;0 %. При таком формате удаляется знак «-» (рис.75). Результат работы приведен на рисунке 76. 91

0%

50%

100%

Рисунок 76

В окне диалога Формат оси для вертикальной оси на вкладке Вид следует убрать все деления. В группе меток делений выбираем переключатель внизу (рис. 77). При этом ось размещается по центру диаграммы, а метки отображаются слева от нее. Вы92

бираем любой ряд данных и в окне Формат ряда данных устанавливаем значение в поле Перекрытие равным 100, а в поле Ширина зазора – 0. Удаляем легенду и добавляем текстовые поля А и В (рис. 78–79).

Рисунок 79

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

Рисунок 80 Рисунок 78

93

Решение. Порядок построения диаграммы: 94

1) ввести данные (наименование работы, начало, длительность). Вычислить окончание работ по формуле Окончание=Начало+Длительность); 2) с помощью Мастера диаграмм создать линейчатую диаграмму на основе диапазона А2:С13. Использовать второй подтип, обозначенный как линейчатая диаграмма с накоплением; 3) на втором шаге выбрать переключатель Ряды в столбцах (рис. 81);

Рисунок 82

5) на третьем шаге удалить легенду и нажать кнопку ГОТОВО; К Ж Г

Рисунок 81

4) щелкнуть на вкладке Ряд и добавить новые ряды данных, если их там нет: Ряд1: В2:В13 Ряд2: С2:С13 Подписи оси Х: А2:А13; 95

А 0

5

10

15

20

25

30

35

Рисунок 83

6) изменить высоту диаграммы и шрифт так, чтобы стали видны все наименования осей (рис. 84); 96

7) открыть окно Формат оси для горизонтальной оси. Установить минимальное и максимальное значения в соответствии с самым ранним и самым поздним временем (рис. 82);

9) выбрать первый ряд данных и открыть окно Формат ряда данных. На вкладке Вид установить невидимую границу и прозрачную заливку. Это сделает невидимым первый ряд данных (рис. 86); 10) сделать нулевой ширину зазора между элементами диаграммы на вкладке Параметры (рис. 87).

Рисунок 84

8) открыть окно Формат для вертикальной оси и на вкладке Шкала установить опцию Пересечение с осью Х в максимальном значении; Рисунок 86

Рисунок 85

97

Рисунок 87

98

Тотализатор Дополнительный заработок Доходы всего

М Л К И З

30 200

100 500

Продолжение таблицы 50 100 10 150 50 300

900 300 550 1600

900 300 430

900 300 390

Ж

Расходы Квартплата Автомобиль Общие расходы Отпуск Расходы всего

12

17

22

27

32

Рисунок 88

На рисунке 88 приведен окончательный вид графика Гантта.

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

Сальдо 2. Построить приведенную ниже таблицу. Заполнить пустые ячейки соответствующими формулами. По таблице построить диаграмму. В построенной диаграмме а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных Объем продаж Фирма

99

2500 1500 1950

2500 1500 1200

Декабрь

Ноябрь

Сентябрь

2500 2500 1500 1500 1580 1790

Октябрь

2500 1500 1800

Август

Июль

Доходы/Расходы за второе полугодие 2003 г.

Доходы Оклад Премия Комиссионные

АО «Феникс» ООО «Печатник» НПФ «Верас»

15050 12908 13598 12000 13495 11984 8956 10023 12000 12345 23456 12365 14367 16790 15000 17900 15600 14560

Всего 2500 1500 1100

900 300 330 3200

Июнь

А

900 300 620

Май

Б

900 300 580

Апрель

В

Март

Г

Февраль

Д

Январь

Е

25 % (НДС) Итого с учетом НДС 100

4. Построить приведенную ниже таблицу. По таблице построить диаграмму. В построенной диаграмме а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных.

10445 7868 16860 11503 15451

Октябрь

8856 16100 10330 11610 12764

Сентябрь

20 860 15 657 10 450 7 680 13 550

Август

Июль

Гродненская ярмарка Гродненская правда Погоня В свободный вечер Молодежный курьер

Июнь

Наименование издания

Май

Планируемые объемы продаж

11734 9474 16210 14586 12892 8050 7889 13645 7243 15987 16154 9460 16863 7880 8496

5. На основании следующей таблицы требуется графически представить величину отношения объема диска, размера монитора и объема видеопамяти к цене и их среднее значение для компьютеров с размером оперативной памяти 8 Мб. 101

GoldStar 725 486sx-25 4 Compaq Contura 486dx2-40 4 400CX Austin 486dx2-50 4 Arche 486dx2-50 4 RoverBook 486dx2-66 4 Toshiba T2130CT 486dx4-75 8 Toshiba T2155CDS 486dx4-75 8 Texas Instr. 486dx4-75 8 TM4000M Magitronic 486dx4-100 8 Twinhead SlimNote I486dx416 5100T 100 CTX EzBook 5x86-100 8 Compal 5x86-133 8 Toshiba T105CS p-75 8 Compal p-100 8 Hitachi E-100D p-100 8 Compaq Armada 1120 p-100 8 Toshiba T110SC p-100 8 Toshiba 110SC p-100 8 Toshiba T205CDS p-100 8 102

170 260 340 260 200 520 520 525

9,5 8,7

Цена

512 12 850 12 1100

9,5 12 1390 10,4 512 12 1500 10,4 12 800 10,4 1024 12 2350 10,4 12 1980 10,4 12 2390

520 9,5 810 10,4 810 540 810 540 810 810 810 772 810

Гар.срок

Модель

Видео

(по материалам «Компьютерной газеты» №19 (108) 20 мая 1997 года)

Монитор

Рим Париж Гродно 15,0 C 5,0 C 0,0 C 13,0 C 4,0 C -3,0 C 10,0 C 2,0 C 2,0 C 14,0 C 1,0 C 4,0 C 8,0 C -10,0 C 2,0 C 9,0 C -4,0 C -5,0 C 5,0 C -2,0 C 3,0 C

HDD

Понедельник Вторник Среда Четверг Пятница Суббота Воскресение

Лондон 10,0 C 12,0 C 7,0 C 8,0 C 4,0 C -2,0 C 0,1 C

Компьютеры типа «Notebook»

ОЗУ

Температура воздуха в городах мира с 12 по 18 декабря

Выберите подходящий тип диаграммы и постройте ее. В построенной диаграмме: а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных.

Процессор

3. Построить приведенную ниже таблицу. По таблице построить диаграмму. В построенной диаграмме а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных.

10,4 10,4 10,4 11,3 11,3 10,4 11,3 11,3 11,3

12 1760 12 2380 1024 1024 1024 1024 1024 1024 1024 2048

12 36 12 36 12 12 12

1650 1350 1600 1750 1850 1984 2000 2100 24 2200

Toshiba T425CDS p-100 JVC p-100 Toshiba T425CDT p-100 Toshiba 200CDS Sat- p-100 ellite RoverBook p-100 Leo DesigNote 3510T p-120 Compaq Armada 1130 p-120 Compaq Armada 1510 p-120 Compaq Armada p-120 1130T

8 8 8 8

Продолжение таблицы 810 11,3 2048 24 2250 540 11,3 1024 36 2300 1300 11,3 2048 36 2800 772 11,3 1024 2820

8 16 16 16

1870 2446 2240 2320 2380

810 1000 1080 1000

11,3 12 10,4 1024 12 11,3 2048 36 10,4 1024 12

6. На основе следующей таблицы построить объемную гистограмму. В построенной диаграмме: а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных. Продажи в 2003 году: МП «Сириус» Наименование товара Шкаф Венеция Компьютерный стол Кресло-кровать Кухня «Вязынка» Комод «Венеция» Спальный гарнитур

Кв 1 250 345 120 90 350 450

Кв 2 190 450 150 110 400 210

Кв 3 180 270 100 120 220 290

Кв 4 230 330 160 150 375 380

7. На основе следующей таблицы построить объемную гистограмму. В построенной диаграмме: а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных. Периодические издания: продажи за 2004 год Наименование издания Кв 1 Кв 2 Кв 3 Кв 4 «Мой сад» 3 200 3 939 2 310 3 200 103

Продолжение таблицы «Советская Белоруссия» 3 714 4 321 3 012 4 120 «Литературная газета» 4 374 5 012 4 678 5 115 «АиФ» 5 869 6 780 5 500 5 750 «Главный бухгалтер» 3 560 2 900 3 100 3 110 8. На основе следующей таблицы построить объемную гистограмму. В построенной диаграмме а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных.

Расходы на путешествие Город Варшава Прага Москва Киев Одесса Минск

Гостиница Питание Экскурсии 159 207 109 219 123 107 249 155 231 240 222 201 180 143 243 200 246 160

Покупки 233 193 143 165 109 195

9. В таблице приведена информация о производстве товаров фирмой и ее реализации в 2002–2003 гг. Рассчитать годовые итоговые показатели и процент реализации продукции (отношение «Реализовано» к «Произведено»). Год 2002 2002

2003 2003

Квартал Произведено Реализовано % реализации Всего I кв. II кв. III кв. IV кв. Всего I кв. II кв.

134 129 144 135

105 117 165 126

160 136

132 128 104

III кв. IV кв.

Продолжение таблицы 160 160

140 167

На основе построенной таблицы построить объемную гистограмму. В построенной диаграмме а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных 10. В таблице приведена информация о предварительных итогах выборов в Государственную Думу РФ в 1999 году. Блок, партия

% голосов

КПРФ 27,2 % Единство 28,2 % СПС 12,2 % Отечество 9,5 % ЛДПР 7,2 % Яблоко 6,8 % НДР 2,4 % СЖР 1,8 % Пенсионеры 1,4 % Кедр 1,2 % Прочие 2,1 % На основе таблицы построить круговую диаграмму. В построенной диаграмме: а) изменить способ заливки одного из рядов данных; б) изменить количество рядов данных 5. Управление списками (базами данных) В Excel присутствуют средства, позволяющие обрабатывать информацию, организованную по принципу баз данных. Это средства работы со списками. Списки состоят из данных одинаковой структуры. Чтобы достичь максимальной эффек105

тивности в работе со списками, при формировании списков необходимо следовать определенным правилам: • данные в пределах одного столбца однородны (одного типа); • столбцам присвоены уникальные имена; каждая строка уникальна; • список не содержит пустых строк. • между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. Это позволяет Excel быстрее обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений; • следует избегать размещения важных данных слева или справа от списка; данные могут быть скрыты при фильтрации списка; • перед внесением изменений в список необходимо убедиться в том, что все скрытые строки и столбцы отображены. Если строки или столбцы списка скрыты, данные могут быть непреднамеренно удалены; • при создании списка не рекомендуется создавать дополнительные строки, отделяющие заголовки столбцов и заполненные символами «тире» или знаками равенства, так как Excel может рассматривать такие строки как данные. Строки списка называются записями, а столбцы – полями. Основными видами обработки баз данных являются поиск и извлечение данных, сортировка и анализ полученных результатов.

Примеры решения задач Задача 5.1. База данных АВТОВЛАДЕЛЬЦЫ содержит следующие поля: Фамилия, Имя, Отчество, Дата выпуска машины, Марка машины, Дата регистрации машины, Номерной знак, Серия, Цвет, Цена($). Найти записи, в которых идет речь о машинах марки «Мерседес». 106

Решение. Вариант 1. Воспользуемся средством Автофильтр. Выполним указанные ниже действия: 1) установим курсор в фильтруемом списке (базе данных); 2) выполним команду Данные/Фильтр/Автофильтр (рис. 89); 3) откроем список в поле Марка машины (рис. 90); 4) выберем в списке значение «Мерседес». На рис. 91 изображен результат применения фильтра к заданной базе данных.

Рисунок 90

Рисунок 91

Рисунок 89

Вариант 2. Воспользуемся средством «Фильтр по выделенному». Установим курсор в ячейку, содержащую название марки

107

108

«Мерседес» и нажмем кнопку Автофильтр на панели инструментов Стандартная. Результат приведен на рисунке 92.

Рисунок 92

Вариант 3. Воспользуемся средством Форма. Для этого, установив курсор в списке, выполним команду Данные/Форма. В результате получим доступ к окну диалога, приведенному на рисунке 93. Нажмем кнопку Критерии и в пустом бланке справа от текста Марка машины введем нужное название (рис. 94). Нажимая на кнопку Далее, получим все записи списка, удовлетворяющие заданному условию (рис. 95).

Рисунок 95

Задача 5.2. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в которых идет речь о машинах черного или сиреневого цвета, год выпуска которых – 1998. Решение. Воспользуемся средством Автофильтр: 1) устанавливаем курсор в фильтруемом списке (базе данных); 2) выполняем команду Данные/Фильтр/Автофильтр; 3) открываем список в поле Дата выпуска машины; 4) выбираем в списке значение «Условие». Выбираем оператор «больше или равно» и устанавливаем дату 1.01.98. Вторым условием, связанным с первым логическим И является «меньше или равно 31.12.98» (рис. 96);

Рисунок 93

Рисунок 94

109

110

Рисунок 96

Рисунок 98

5) в поле «Цвет открываем список и выбираем значение «Условие» (рис. 97):

Задача 5.3. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в которых идет речь о машинах, год выпуска которых 1997 или 2000. Решение. Так как 1997 и 200 годы относятся к двум диапазонам от 1.01.97 до 31.12.97 и от 1.01.00 до 31.12.00, то следует воспользоваться средством Расширенный фильтр. Для этого нужно выполнить такую последовательность действий: 1) скопировать из списка заголовок фильтруемого столбца и дважды вставить его на рабочий лист, например так, как показано на рисунке 99;

Рисунок 97

На рисунке 98 изображен результат применения фильтра к заданной базе данных.

Рисунок 99

111

2) ввести в строки под заголовками условий требуемые критерии отбора (рис. 100); 112

Результат фильтрации приведен на рисунке 102.

Рисунок 101 Рисунок 100

3) установить курсор в списке; 4) выполнить команду Данные/Фильтр/Расширенный фильтр; 5) чтобы показать результат фильтрации, скрыв ненужные строки, следует установить переключатель Обработка в положение Фильтровать список на месте. Чтобы скопировать отфильтрованные строки в другую область листа, следует установить переключатель Обработка в положение Скопировать результаты в другое место, перейти в поле Поместить результат в диапазон, а затем указать верхнюю левую ячейку области вставки; 6) ввести в поле Диапазон критериев ссылку на диапазон условий отбора, включающий заголовки столбцов и щелкнуть по кнопке Ok (рис. 101). 113

Рисунок 102

Задача 5.4. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в которых идет речь о машинах, год выпуска которых 1997 или 2000, а цвет черный или розовый.

114

Решение. На рисунке 103 приведены диапазоны критериев для фильтрации списка в этом случае. Дата выпуска машины >=1.01.97 >=1.01.00 >=1.01.97 >=1.01.00

Дата выпуска машины <=31.12.97 <=31.12.00 <=31.12.97 <=31.12.00

Цвет розовый розовый черный черный

Ссылки в формулах указываются для первой записи в базе данных. Так получим на экране:

ЛОЖЬ ЛОЖЬ ЛОЖЬ

Рисунок 103

Решение. На рисунке 104 приведен диапазон критериев для фильтрации списка (1 вариант), а на рисунке 105 – второй вариант. В первом варианте используется оператор точного соответствия, который в общем виде выглядит так: =''=текст'' После ввода вместо текст ский, ская или сков на экране получим: Так вводим:

Фамилия =*ский =*ская =*сков

=ПРАВСИМВ(A2;4)="ский" =ПРАВСИМВ(A2;4)="ская" =ПРАВСИМВ(A2;4)="сков" Рисунок 105

Задача 5.5. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в которых фамилии автовладельцев заканчиваются на. «ский», «ская» или «сков».

Так получим на экране:

Так вводим:

Фамилия ="=*ский" ="=*ская" ="=*сков"

Задача 5.6. В базе данных АВТОВЛАДЕЛЬЦЫ найти записи, в которых идет речь о машинах, цена которых отличается от средней цены по всей базе не более чем на 1000. Найти количество таких записей, среднюю, максимальную и минимальную цены таких машин. Решение. На рисунке 106 приведен диапазон критериев. Здесь $J$2:$J$30 ― диапазон размещения столбца с полем Цена($). Так как среднее значение при переходе от записи к записи не должно меняться, использованы абсолютные ссылки. Для проверки правильности результата можно вычислить среднюю цену по базе. В данной задаче это значение равно 4388,69. На рисунке 107 приведены отфильтрованные записи. Так получим на экране: ИСТИНА

Так вводим: =ABS(СРЗНАЧ($J$2:$J$30)– J2)<=1000

Рисунок 104

Рисунок 106

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

116

Замечание. Для суммирования значений в списке по заданным условиям может быть использован мастер суммирования. Например, с помощью мастера суммирования можно создать формулу для вычисления суммарной цены машин по определенной марке в списке. Мастер суммирования является надстройкой. Если в подменю Мастер меню Сервис отсутствует команда Частичная сумма, необходимо установить и загрузить программу надстройки. Рисунок 107

Для получения количества таких записей, средней, максимальной и минимальной цены таких машин следует воспользоваться соответствующими функциями обработки баз данных: =БСЧЁТА(А1:J30;J1;Критерии) =ДСРЗНАЧ(А1:J30;J1;Критерии) =ДМАКС(А1:J30;J1;Критерии) =ДМИН(А1:J30;J1;Критерии). Имя «Критерии» присвоено диапазону N10:N11, в котором расположена область критериев. На рисунке 108 приведен результат фильтрации (для контроля) и результат вычислений по формулам.

Для определения суммарной цены всех машин марки «Ауди», год выпуска которых находится в диапазоне от 1990 до 2000 годов, следует выполнить следующие действия: 1) установить курсор в списке; 2) выполнить команду Сервис / Мастер /Частичная сумма;

3) проверить, правильно ли указан диапазон размещения списка; 4) нажать кнопку Далее; 5) следуя указаниям Мастера, добавить нужные условия; 6) выбрать на рабочем листе ячейку (ячейки), в которые следует вывести информацию. Рисунок 108

117

118

119

120

Задачи для самостоятельного решения 1. База данных АВТОВЛАДЕЛЬЦЫ содержит следующие поля: Фамилия, Имя, Отчество, Марка машины, Год выпуска, Номерной знак, Серия, Цвет, Цена. 1.1.Найти в списке записи, в которых фамилии начинаются, заканчиваются или содержат заданную цепочку символов. 1.2.Найти в списке записи, в которых идет речь о машинах, год выпуска которых входит в заданный диапазон 1.3.Найти в списке записи, в которых идет речь о трех марках машин.

1.4.Найти в списке записи, в которых цена машины не превышает среднюю. 1.5.Найти в списке записи, в которых фамилии начинаются, заканчиваются или содержат заданную цепочку символов, а марка машины либо «Мерседес», либо «Ауди». 1.6.Найти в списке записи, в которых идет речь о трех марках машин, а номерной знак начинается на заданную цифру. 1.7.Найти в списке записи, в которых цена машины отличается от средней не более чем на заданную величину, а год выпуска находится в заданном диапазоне. 1.8.Найти в списке записи, в которых фамилии начинаются на «А», «Д» или «Ма». 2. База данных ШКОЛЬНИКИ содержит следующие поля: Фамилия, Имя, Отчество, IQ (коэффициент умственного развития), Год обучения, Параллель, Рост, Оценка по алгебре, Оценка по геометрии, Оценка по физике, Оценка по физкультуре. 2.1. Найти записи, относящиеся к 8 классам. 2.2. Найти записи, относящиеся к 8 и 9 классам. 2.3. Найти записи, относящиеся к 3, 5 и 9 классам. 2.4. Найти записи, относящиеся к отличникам. 2.5. Найти записи, в которых отсутствуют оценки ниже «4». 2.6.Найти записи, в которых оценка по алгебре выше оценки по геометрии. 2.7.Найти записи, в которых оценка по алгебре выше средней оценки по алгебре. 2.8.Найти записи, в которых средняя оценка выше средней оценки по всей базе. 2.9.Найти записи, в которых средняя оценка отличается от средней оценки по всей базе не больше, чем на заданную величину. 2.10. Найти записи, в которых фамилия начинается на заданную букву или цепочку символов. 2.11. Найти записи, в которых фамилия закачивается на заданную букву или цепочку символов.

121

122

Ниже приведен результат вычислений и формула, которую создает Матер суммирования. 1990

2000 Ауди

3891

{=СУММ(ЕСЛИ($E$2:$E$30>=K1;ЕСЛИ($E$2:$E$30<=L1; ЕСЛИ($D$2:$D$30=M1;$I$2:$I$30;0);0);0))}

2.12. Найти записи, в которых средняя оценка выше, чем средняя оценка по всей базе, а IQ – ниже, чем среднее значение IQ по всей базе. 3. База данных БОЛЬНИЦА содержит следующие поля: Фамилия, Имя, Лечащий врач, Заболевание, Отделение, Дата поступления, Дата выписки, Исход. 3.1.Найти в списке записи, в которых фамилии больных заканчиваются на «ов», «ова» или «ский». 3.2.Вывести записи с летальным исходом. 3.3.Вывести записи с летальным исходом, приходящимся на зимние месяцы. 3.4.Упорядочить список всех больных кардиологического отделения. 3.5.Найти записи, в которых срок лечения находится в диапазоне от 1 до 2 месяцев. 3.6.Найти записи, в которых срок лечения превышает средний срок лечения по всей базе и количество таких записей. 3.7.Найти записи, в которых срок лечения отличается от среднего срока лечения по всей базе не более чем на заданную величину. 3.8.Найти все записи с летальным исходом, относящиеся к заданному лечащему врачу. 4. База данных СОТРУДНИКИ содержит следующие поля: Фамилия, Имя, Отчество, Пол, Дата рождения (в формате ДД.ММ.ГГ), Должность, Стаж, Оклад. 4.1. Найти в списке записи, в которых фамилии начинаются, заканчиваются или содержат заданную цепочку символов. 4.2. Найти в списке записи, в которых возраст сотрудника находится в заданном диапазоне, а оклад ниже среднего оклада по всей базе. 4.3. Найти в списке записи, относящиеся к заданным должностям, и количество таких записей. 4.4. Найти в списке записи, в которых оклад отличается от среднего оклада по всей базе не более чем на заданную величину.

4.5. Найти в списке записи, относящиеся к людям предпенсионного возраста (для женщин — 50–55 лет, для мужчин — 55–60 лет). 4.6.Найти в списке записи, относящиеся к сотрудникам, стаж которых превышает средний стаж по всей базе, а оклад которых ниже среднего оклада по всей базе. 4.7.Найти в списке записи, относящиеся к сотрудникам, родившимся в 1 квартале (январь–март). 5. База данных БАСКЕТБОЛИСТЫ содержит следующие поля: Фамилия, Имя, Штат, Команда, Рост, Вес, Количество сезонов, Сыграно матчей, Подборы, Пасы, Перехваты, Очки. 5.1.Найти в списке записи, в которых фамилии начинаются, заканчиваются или содержат заданную цепочку символов. 5.2.Найти в списке записи, относящиеся к трем командам. 5.3.Найти в списке записи, в которых фамилии начинаются, заканчиваются или содержат заданную цепочку символов, а рост спортсмена выше среднего, и количество таких записей. 5.4.Найти в списке записи, в которых рост спортсмена выше среднего, а вес ниже среднего. 5.5.Найти в списке записи, в которых количество подборов отличается от среднего по базе не более чем на заданную величину. 5.6.Найти в списке записи, в которых количество подборов отличается от среднего по базе не более чем на заданную величину, а количество набранных очков отличается от максимального больше чем на заданную величину. 5.7.Найти в списке записи, относящиеся к трем командам, названия которых заканчиваются на «а», «ы» или «о». 6. База данных ВИНА содержит следующие поля: Название, Год урожая, Ёмкость, Цвет, Содержание сахара (сухое, полусухое), Содержание спирта, Цена 1 бутылки. 6.1. Найти записи с самым дорогим красным сухим вином. 6.2.Отсортировать записи по цене, содержанию спирта, ёмкости, цвету, году урожая и коду вина. 6.3.В винах какого цвета содержится больше всего спирта?

123

124

6.4.Найти записи, год урожая в которых принадлежит заданному диапазону. 6.5.Найти записи, в которых содержание спирта ниже среднего, и определить количество таких записей. 6.6.Найти записи, в которых цена бутылки отличается от средней не более чем на заданную величину. 6.7.Найти записи, код вин в которых начинается, заканчивается или содержит заданную цепочку символов. 6.8.Найти записи, в которых названия вин заканчиваются на «е», «а» или «е». 7. База данных ГАЗЕТЫ содержит следующие поля: Название, Индекс, Количество номеров в квартал, Цена за квартал, Вид. 7.1.Найти записи, относящиеся к журналам или газетам, в названиях которых присутствуют буквы А,Б,В,Г. 7.2.Найти записи, относящиеся к журналам, индексы которых находятся в двух заданных диапазонах. 7.3.Найти записи, относящиеся к газетам, у которых цена отличается от средней по базе не более чем на заданную величину. 7.4.Найти записи, относящиеся к газетам, у которых цена отличается от максимальной не более чем на заданную величину и количество таких записей. 7.5.Найти записи, относящиеся к журналам или газетам, у которых количество номеров в квартал минимально, а цена выше среднего по базе. 7.6.Найти записи, в которых название газеты заканчивается на «я», «а» или «и». 8. База данных КОМПЬЮТЕРНЫЕ ИГРЫ содержит следующие поля: Место, Всего недель в чарте, Название, Производитель, Категория, Самое высокое место, Цена. 8.1.Найти игру, которая дольше всех в чарте и имела место выше 3-го. 8.2.Найти записи, в которых названия игры начинаются, заканчиваются или содержат заданную цепочку символов, и количество таких записей. 8.3.Найти самую дорогую игру.

8.4.Найти, есть ли компания, у которой несколько игр были в лидерах (1–3 место). 8.5.Найти записи, в которых цена игры ниже среднего, а количество недель в чарте выше среднего. 8.6.Найти записи, в которых цена игры отличается от средней не более чем на заданную величину, и количество таких записей. 8.7.Найти записи, в которых название игры заканчивается на «я», «а» или «и». 9. База данных КНИГИ содержит следующие поля: Название книги, Автор, Жанр, Год издания, Место издания, Издательство, Количество страниц, Тираж, Спрос Цена. 9.1.Найти записи, относящиеся к московским издательствам. 9.2.Найти автора самого дорогого фантастического романа. 9.3.Отсортировать записи по году издания, по жанру, по названию, тиражу. 9.4.Найти книгу, изданную после 1980 года, имеющую спрос больше 500 и меньше 1000. Определить количество таких записей. 9.5.Найти книги, которые имеют больший спрос, чем тираж. 9.6.Найти записи, относящиеся к авторам, фамилии которых начинаются, заканчиваются или содержат заданную цепочку символов. 9.7.Найти записи, в которых количество страниц ниже среднего по всей базе, а тираж – выше среднего. 9.8.Найти книги, относящиеся к трем заданным издательствам. 9.9.Найти книги, у которых цена отличается от средней по всей базе не более чем на заданную величину, а спрос выше среднего. 9.10. Найти записи, в которых название издания заканчивается на «я», «а» или «и». 10. База данных ЛЕГКАЯ АТЛЕТИКА содержит следующие поля: Фамилия, Возраст, Разряд, Время на 100 м., Время на

125

126

110 метров с барьерами, Прыжок в длину, Прыжок в высоту, Тройной прыжок, Шест, Ядро, Диск. 10.1. Найти записи, относящиеся к спортсменам, результат которых по заданному виду меньше мирового рекорда на заданную величину. 10.2. Найти записи, относящиеся к спортсменам, результат которых превосходят мировой рекорд по трем видам (любым). Определить количество таких записей. 10.3. Есть ли в базе данных спортсмены, у которых наилучшие результаты по всем видам? 10.4. Есть ли в базе данных спортсмены, у которых наилучшие результаты по каким-либо трем видам? 10.5. Есть ли в базе данных спортсмены, у которых результаты отличаются от лучших по базе не более чем на 5 % по всем видам? 10.6. Есть ли в базе данных спортсмены, у которых результаты отличаются от мировых рекордов не более чем на 5 % по всем видам? Сколько таких записей? 11. База данных ВЕЩЕСТВА содержит следующие поля: Название, Удельный вес, Состояние, Температура замерзания, Проводимость, Предназначение, Количество, Цена за единицу веса. 11.1. Найти записи, относящиеся к четырем разным веществам, удельный вес которых находится в заданном диапазоне. 11.2. Найти записи, относящиеся к диэлектрикам, количество которых отличается от максимального количества не более чем на заданную величину. Определить количество таких записей. 11.3. Найти записи, относящиеся к четырем разным веществам, цена которых ниже среднего по всей базе. 11.4. Найти записи, относящиеся к четырем разным веществам, удельный вес которых отличается от среднего не более чем на заданную величину. 11.5. Найти записи, относящиеся к газообразным или жидким веществам, удельный вес которых находится в заданном диапазоне.

11.6. Найти записи, в которых название вещества заканчивается на «ий», «а» или «д». 12. База данных ПОКУПКИ содержит следующие поля: Код заказа, Наименование товара, Характеристика, Цена, Дата оплаты, Номер карточки, Фамилия владельца, Срок действия карточки. 12.1. Пользуется ли заданный товар наибольшим спросом? 12.2. Сделал ли конкретный покупатель больше всего покупок? 12.3. Есть ли у заданного покупателя несколько карточек? 12.4. Вывести сведения о покупках, у которых цена больше средней цены одной покупки. Определить количество таких записей. 12.5. Проверить, не использовалась ли одна и та же карточка с заданным кодом разными покупателями. Определить количество таких записей. 12.6. Найти записи, в которых фамилия покупателя начинается, заканчивается или содержит заданную цепочку символов. 12.7. Найти записи со сроком действия карточки в заданном диапазоне. 12.8. Вывести сведения о покупках, у которых цена отличается от средней цены не более чем на заданную величину. Определить количество таких записей. 13. База данных ПРОГРАММЫ содержит следующие поля: Программа, Код программы, Дата записи, Длительность (мин.), Директор, Режиссер, Популярность, Цена программы (млн. руб.). 13.1. Найти записи, относящиеся к указанному директору. 13.2. Найти записи, относящиеся к заданному диапазону лет. 13.3. Найти записи, относящиеся к программам, созданным в зимние месяцы. Определить количество таких записей. 13.4. Найти записи, превосходящие по длительности среднее аналогичное значение по всему списку. Определить количество таких записей.

127

128

13.5. Найти записи, относящиеся к программам, цена которых отличается от средней цены более чем на заданную величину. 14. База данных ФИРМЫ содержит следующие поля: Фирма, Форма собственности, Директор, Адрес, Дата регистрации, Номер счета, Банк, Годовой оборот, Вид деятельности. 15.1. Найти записи, относящиеся к фирмам, годовой оборот которых находится в заданном диапазоне. 15.2. Найти записи, относящиеся к фирмам, в названии которых присутствует заданная цепочка символов. 15.3. Найти записи, относящиеся к фирмам, месяц регистрации которых приходится на март. Определить количество таких записей. 15.4. Найти записи, относящиеся к фирмам, год создания которых находится в заданном диапазоне. 15.5. Найти записи с указанной формой собственности. Определить количество таких записей. 15.6. Есть ли в списке фирмы, директором которых является один и тот же человек (фамилия и инициалы заданы)? 15.7. Найти записи, относящиеся к фирмам, годовой оборот которых отличается от среднего годового оборота по всему списку не более чем на заданную величину. 16. База данных HAMMER THROW 2002 WR содержит следующие поля: Бросок, Атлет, Страна, Дата рождения, Занятое место, Место выполнения броска, Дата проведения броска. 16.1. Найти записи, относящиеся к летним месяцам выполнения броска. 16.2. Найти записи, возраст спортсмена в которых находится в заданном диапазоне. 16.3. Найти записи, в которых бросок отличается от мирового рекорда не больше чем на заданную величину. Определить количество таких записей. 16.4. Найти записи, в которых бросок превосходит мировой рекорд. Определить количество таких записей. 16.5. Найти записи, относящиеся к белорусским спортсменам.

16.6. Найти записи, относящиеся к спортсменам России, Беларуси и Украины, бросок которых выше среднего по всей базе. Определить количество таких записей. 17. База данных КРУПНЕЙШИЕ ОЗЕРА ПЛАНЕТЫ содержит следующие поля: Название, Часть света, Страна, Площадь, Площадь в пределах страны, Наибольшая глубина, Наибольшая глубина в пределах страны, Средняя глубина. 17.1. Найти записи, относящиеся к озерам, расположенным в Европе и Азии. Определить количество таких записей. 17.2. Найти записи, относящиеся к озерам, расположенным в Европе, которые находятся в пределах одной страны. 17.3. Найти записи, относящиеся к озерам, площадь которых отличается от площади самого большого озера не более чем на заданную величину. 17.4. Найти записи, относящиеся к озерам, расположенным в Европе, у которых площадь в пределах страны составляет не менее 50 % от общей площади. Определить количество таких записей. 17.5. Найти записи, относящиеся к озерам, расположенным в Канаде, названия которых содержат заданную цепочку символов. 17.6. Найти записи, относящиеся к озерам, расположенным в Африке, средняя глубина которых находится в заданном диапазоне, причем озера расположены в пределах одной страны. 18. База данных КРУПНЕЙШИЕ ОСТРОВА ЗЕМЛИ содержит следующие поля: Название, Часть света, Принадлежность государству, Площадь, Численность населения. 18.1. Найти записи, относящиеся к трем частям света. Определить количество таких записей. 18.2. Найти записи, относящиеся к островам государств Европы или Африки, площадь которых находится в заданных пределах. 18.3. Найти записи, относящиеся к островам, площадь которых превышает среднюю по всей базе, а количество населения в которых меньше средней численности по всей базе.

129

130

18.4. Найти записи, относящиеся к островам, в названии которых присутствует заданная цепочка символов. 18.5. Найти записи, относящиеся к островам, названия которых не содержат заданную цепочку символов. 18.6. Найти записи, относящиеся к островам с максимальной площадью или минимальным населением. 19. База данных СТОЛИЦЫ содержит следующие поля: Название, Часть света, Страна, Численность населения, Занимаемая площадь, Средний возраст жителей, Количество мужчин старше 18 лет, Количество женщин старше 18 лет, Средняя стоимость 1м2 жилой площади, Бюджет города. 19.1. Найти записи, относящиеся к столицам европейских государств, в названии которых присутствует заданная цепочка символов. 19.2. Найти записи, относящиеся к самым дорогим по стоимости 1 м2 жилья столицам Азии. 19.3. Найти записи, относящиеся к столицам, где численность женского населения превосходит мужское. Определить количество таких записей. 19.4. Найти записи, относящиеся к столицам, в которых средний возраст жителей меньше среднего возраста по всей базе. 19.5. Найти записи, относящиеся к столицам, площадь которых меньше средней площади по всей базе, а количество населения превышает такой показатель по всей базе. 19.6. Найти записи, относящиеся к столицам, в которых количество детей больше среднего показателя по всей базе. Определить количество таких записей. 20. База данных СТРАНЫ содержит следующие поля: Название, Часть света, Столица, Общая численность населения, Численность населения столицы, Занимаемая площадь, Количество мужчин, Количество женщин, Количество жителей в диапазонах до 18 лет, от 18 до 40 лет, от 40 до 60 лет, выше 60 лет. 20.1. Найти записи, относящиеся к Европе, Африке и Австралии.

20.2. Найти записи, относящиеся к странам Европы, у которых численность населения отличается от средней численности европейских стран более чем на заданную величину. Определить количество таких записей. 20.3. Найти записи, относящиеся к странам Африки с наибольшей плотностью населения. 20.4. Найти записи, относящиеся к странам с наибольшим процентом детского населения. 20.5. Найти записи, относящиеся к странам с наибольшим количеством женского и детского населения (до 18 лет). Определить количество таких записей. 20.6. Найти записи, относящиеся к странам с преобладающим населением в диапазоне от 40 лет. Определить количество таких записей. 21. База данных ПЛАНЕТЫ СОЛНЕЧНОЙ СИСТЕМЫ содержит следующие поля: Название, Масса, Дата открытия, Экваториальный диаметр, Расстояние до Солнца, Наличие спутников. 21.1. Найти записи, относящиеся к планетам, в названии которых присутствует заданная цепочка символов. 21.2. Найти записи, относящиеся к планетам, масса которых отличается от средней массы всех планет не более чем на заданную величину. Определить количество таких записей. 21.3. Найти записи, относящиеся к планетам, находящимся от Солнца на расстоянии, попадающем в заданный диапазон. Определить количество таких записей. 21.4. Найти записи, относящиеся к планетам, дата открытия которых приходится на заданный диапазон. 21.5. Найти записи, относящиеся к планетам с наибольшей плотностью вещества. 21.6. Найти записи, относящиеся к планетам, имеющим хотя бы один спутник. Определить количество таких записей. 22. База данных ФУТБОЛИСТЫ содержит результаты исследований, проведенных экспертами спортивной газеты по 20 футболистам, которые могли бы занять место опорного полузащитника в сборной страны на чемпионате мира. Исследовались 14 критериев: 1 — стабильность, 2 — короткий и сред-

131

132

ний пас, 3 — видение поля, 4 —контроль мяча, 5 — работоспособность, 6 — удар на силу, 7 —удар на технику, 8 — отбор, 9 — жесткость, 10 — длинный пас, 11 — игра головой, 12 — международный опыт, 13 — опыт игры на данной позиции, 14 — игровая практика в предыдущем сезоне. Каждый критерий оценивался по 10-балльной шкале. 22.1. Найти записи, относящиеся к футболистам, у которых все показатели выше средних показателей по всей базе. 22.2. Найти записи, относящиеся к футболистам, которые набрали не менее 8 баллов по всем показателям. 22.3. Найти записи, относящиеся к футболистам, которые набрали 10 баллов по 4 показателям. 22.4. Найти записи, относящиеся к футболистам, у которых международный опыт меньше среднего показателя по всей базе. Определить количество таких записей. 22.5. Найти записи, относящиеся к футболистам, получившим 10 баллов по трем и более показателям. Определить количество таких записей. 22.6. По какому количеству критериев вообще не были выставлены оценки, равные 10 баллам? 23. База данных ОЛИМПИАДЫ содержит следующие поля: Вид олимпиады (зимняя, летняя), Год проведения, Страна, Город, Количество спортсменов, Количество золотых, серебряных, бронзовых медалей, Вознаграждение за золотую медаль, Вознаграждение за серебряную медаль, Вознаграждение за бронзовую медаль. 23.1. Найти записи, относящиеся к зимним олимпиадам заданного временного диапазона. Определить количество таких записей. 23.2. Найти записи, относящиеся к олимпиадам с максимальным количеством золотых медалей. 23.3. Найти записи, относящиеся к олимпиадам с максимальным количеством участников. 23.4. Найти записи, относящиеся к олимпиадам с наибольшим вознаграждением за золотую медаль. 23.5. Есть ли в базе олимпиады, проводившиеся в одной и той же стране несколько раз (название страны задано)?

23.6. Есть ли в базе олимпиады, проводившиеся в одном и том же городе несколько раз (название города задано)? 24. База данных ОЛИМПИЙСКИЕ ЧЕМПИОНЫ содержит следующие поля: Фамилия и инициалы спортсмена, Дата рождения (ДД.ММ.ГГ), Вид олимпиады (зимняя, летняя), Год проведения игр, Страна, Город, Команда, Вид спорта. 24.1. Найти записи, относящиеся к чемпионам, год рождения которых находится в заданном диапазоне. 24.2. Найти записи, относящиеся к чемпионам в заданных видах спорта. Определить количество таких записей. 24.3. Найти записи, относящиеся к чемпионам зимних олимпиад, которые родились в зимние месяцы. Определить количество таких записей. 24.4. Найти записи, относящиеся к чемпионам, фамилия которых содержит заданную цепочку символов. 24.5. Найти записи, относящиеся к чемпионам, которые выступали за указанные государства. Определить количество таких записей. 24.6. Найти записи, относящиеся к спортсменам, ставшим чемпионами в заданных странах и городах.

133

134

6. Сортировка списков. Промежуточные итоги Сортировка списка Рассмотрим способы сортировки данных, допускаемые Excel: • сортировка строк или столбцов в возрастающем или убывающем порядке (текстовые данные – в алфавитном или обратном алфавитном порядке); • создание собственного порядка сортировки. При сортировке по строкам изменяется порядок их расположения в списке, а порядок расположения столбцов остается прежним. При сортировке по столбцам изменяется порядок расположения столбцов.

Сортировка производится либо с помощью команды Данные/Сортировка, либо с использованием кнопок на па. нели инструментов Стандартная При одном обращении к команде Данные/Сортировка Excel дает возможность осуществить три уровня сортировки (рис. 109 ).

Рисунок 109

Перед выполнением команды Данные/Сортировка курсор должен находиться внутри списка. Если же сортировке подвергается часть списка, то она должна быть предварительно выделена. В полях с раскрывающимися списками «Сортировать по», «Затем по», «В последнюю очередь, по « задаются заголовки столбцов (или строк), по которым должна осуществляться сортировка. Переключатель "«Идентифицировать поля по» в нижней части окна диалога устанавливается в положение «Подписям». В этом случае заголовки столбцов (строк) не будут включены в сортировку. 135

Для выбора сортировки по строкам или по столбцам следует воспользоваться кнопкой «Параметры» диалогового окна «Сортировка диапазона» и установить переключатель «Сортировать» в соответствующее положение («Строки диапазона» или «Столбцы диапазона») (рис. 110). Для применения пользовательского порядка сортировки необходимо создать НОВЫЙ СПИСОК, выполнив команду Сервис/Параметры/Списки, а затем, нажав кнопку «Параметры» в окне диалога «Сортировка диапазона», раскрыть список поля «Сортировка по первому ключу» и выбрать нужный список (рис. 111).

Рисунок 110

Рисунок 111

136

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

Р исунок 113

Рисунок 112

Рисунок 114

137

138

Промежуточные итоги Команда Данные/Итоги используется после работы команды Данные/Сортировка, когда список отсортирован по заданным критериям. Команда Данные/Итоги добавляет строки промежуточных итогов для каждой группы элементов списка, а также создает общие итоги. Можно использовать различные функции для вычисления итогов (например, СУММ или СРЗНАЧ). При выводе промежуточных итогов Excel создает структуру списка. Чтобы вывести нужный уровень детализации данных, следует щелкнуть мышью на соответствующем символе структуры. Диалоговое окно «Промежуточные итоги» представлено на рисунке 117. Рисунок 115

Пример сортировки по столбцам дан на рисунке 116 (список сотрудников отсортирован по фамилиям).

Рисунок 117 Рисунок 116

139

140

Чтобы «вложить» или вставить итог для групп, находящихся внутри уже существующих групп, необходимо выполнить следующие операции: 1). отсортировать список по двум или более столбцам, для которых необходимо подвести итоги; 2). вставить автоматические итоги для первого столбца, содержащего группы суммируемых данных. Этот столбец при сортировке списка должен быть указан в поле Сортировать по; 3). после создания автоматических итогов в первом столбце повторить процедуру для следующего столбца; 4). установить курсор в списке; 5). выбрать команду Данные/Итоги; 6). выбрать следующий столбец, по которому необходимо подвести итоги, из списка При каждом изменении В; 7). снять флажок Заменить текущие итоги, а затем нажать кнопку ok; 8). повторять шаги с 4 по 7 для каждого столбца, по которому необходимо подвести итоги. Для вычисления значений итогов используется итоговая функция, например, СУММ или СРЗНАЧ. Промежуточные итоги могут быть отображены в списке с помощью нескольких типов вычислений одновременно. Общие итоги подводятся с помощью детальных данных, а не с помощью значений промежуточных итогов. Например, итоговая функция СРЗНАЧ возвращает значение для всех детальных данных списка, но не для промежуточных итоговых значений. Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении детальных данных.

Рисунок 118

Решение. Отсортируем список по полю Марка машины, а затем выполним команду Данные/Итоги. Заполнение диалогового окна для списка выполним так, как показано на рисунке 119. Результат применения команды приведен в виде фрагментов на рисунке 120.

Примеры решения задач Задача 6.1. На рисунке 118 приведен фрагмент списка Автовладельцы. Используя промежуточные итоги, определить, сколько в списке машин каждой марки. Рисунок 119

141

142

При выполнении команды список был структурирован. Щелчок мыши на символе строки уровня 1 дает возможность просмотреть только общие итоги (рис. 121), на символе строки уровня 2 – вывести только промежуточные и общие итоги (рис. 122), скрыв сам список, и увидеть все уровни детализации – на символе строки уровня 3.

Рисунок 121

Рисунок 122

Рисунок 120

Чтобы удалить промежуточные итоги и структуру, можно использовать кнопку «Убрать все» диалогового окна «Промежуточные итоги». Для применения нескольких итоговых функций достаточно повторить команду «Итоги» для новой итоговой функции и снять флажок «Заменить текущие итоги» диалогового окна «Промежуточные итоги».

143

144

Имя

Отчество

Антонович Богдан

Татьяна Елена

Васильевна Николаена

Гресь

Елена

Сергеевна

Гришук Гресь Гресь Данилова Дешко Храмова Врублевская

Татьяна Елена Маруся Ольга Ольга Татьяна Елена

Владимирова Сергеевна Евгеньевна Васильевна Алексеевна Ивановна Олеговна

Башкатова

Наталья

Сергеевна

Должность

Фамилия

Возраст

Задача 6.2. На рисунке 123 приведен фрагмент списка. Подвести промежуточные итоги, по которым можно определить распределение должностей по половому признаку.

46 бухгалтер 18 директор зав.лабора36 торией зам.ди41 ректора 45 лаборант 24 менеджер 36 менеджер 44 менеджер 39 менеджер 47 оператор програм19 мист

Оклад пол

272 ж 168 ж 219 ж 282 ж 175 ж 50 ж 42 ж 119 ж 282 ж 131 ж

Рисунок 124

218 ж

Рисунок 123

Решение. Отсортируем список по столбцам Пол и Должность. При этом окно диалога Сортировка диапазона должно выглядеть так, как на рисунке 124. Вставляем автоматические итоги для столбца пол (рис. 125–126). Повторяем процедуру для столбца Должность (рис. 127–128).

Рисунок 125

145

146

Рисунок 126

Рисунок 128

Рисунок 127

147

Рисунок 129

148

Задачи для самостоятельного решения 1. Список содержит фамилию, должность, пол, год рождения и телефон сотрудников предприятия. 1.1. Сколько Ивановых работают в фирме и кто из них самый пожилой? 1.2. Каков средний возраст мужчин и женщин, работающих в фирме? 1.3. Сколько в фирме менеджеров, инженеров, водителей и представителей других должностей? 2. Список содержит фамилии студентов некоторого факультета, номера курсов, групп и наименования предметов, вынесенных в сессию. 2.1. Отсортировать список так, чтобы предметы (названия полей) располагались в алфавитном порядке. Скопировать список на отдельный лист. На этом листе транспонируйте таблицу. Дополните таблицу строкой «Средний балл» и заполните ее, введя соответствующую формулу. 2.2. Выполнить сортировки по столбцам. Перед каждой сортировкой копировать таблицу на новый лист. 2.2.1. Переставьте столбцы так, чтобы фамилии студентов расположились в обратном алфавитном порядке. 2.2.2. Переставьте столбцы так, чтобы в первых колонках были худшие студенты (с минимальным средним баллом). 2.2.3. Переставьте столбцы так, чтобы в первых колонках были студенты, хорошо успевающие по математическому анализу, но с провалами по программированию. 2.2.4. Переставьте столбцы так, чтобы в начале таблицы оказались отличники по математическому анализу, причем первыми должны идти те, у кого наилучший средний балл. 2.2.5. Переставьте столбцы так, чтобы фамилии студентов расположились в алфавитном порядке, снова транспонируйте таблицу и поместите ее на Листе 1, начиная с клетки A12.

7. Анализ данных с помощью сводных таблиц Для всестороннего и эффективного анализа данных больших таблиц в Excel используются т.н. сводные таблицы. Функции работы с СТ относятся к одному из самых мощных инструментов Excel. Главные достоинства СТ – это представление больших объемов информации в концентрированном и удобном для анализа виде, широкие возможности для группировки данных, а также возможность получения промежуточных и общих итогов, которые помещаются в таблицу автоматически. Применять этот инструмент следует для больших таблиц, где имеются различные повторения значений в столбцах и (или) строках. Сводные таблицы являются «трехмерными», т.к. к привычным атрибутам таблиц: строка и столбец, добавляется атрибут – страница Для построения и модификации сводной таблицы используется Мастер сводных таблиц, вызываемый с помощью пункта меню Данные /Сводная таблица. На первом шаге предлагается выбрать источник данных для построения таблицы. Создать сводную таблицу можно на основе данных, находящихся в одном из четырех источников (рис. 130). На втором шаге в диалоговом окне указывается интервал клеток списка или базы данных, которые должны быть сведены (рис. 131). На третьем шаге в макете таблицы создается структура СТ и определяются ее функции. Макет представлен в центре окна и состоит из областей: строка, столбец, страница и данные. Справа от макета отображаются все имена полей (заголовки столбцов) в заданном интервале исходной таблицы (рис. 130). Размещение полей в определенную область макета выполняется путем их «перетаскивания» при нажатой левой кнопке мыши. Чтобы удалить поле из области макета, его перетаскивают за пределы макета.

149

150

элемента внешнего поля, элементы внутреннего поля повторяются

Рисунок 130 Рисунок 132

Рисунок 131

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

151

152

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

Рисунок 134

На рис. 135 приведен результат работы Мастера создания сводных таблиц.

Примеры решения задач Задача 7.1. Пусть исходная база данных содержит следующие поля: Дата, Фамилия, Проект, Код работ, Часы, Стоимость, Вид работы. Пользуясь имеющимися данными, требуется отобрать сотрудников для выполнения нового проекта. Решение. Запускаем Мастер создания сводных таблиц, выполнив команду Данные/Сводная таблица. На рисунке 134 приведен макет сводной таблицы. Заполнение макета производится перетаскиванием мышью названий полей базы в соответствующие области макета. 153

Рисунок 135

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

лицу, перемещая поля следующим образом (это можно выполнить непосредственно в сводной таблице и на этапе построения макета сводной таблицы) (рис. 136).

Содержимое сводной таблицы на рисунке 137 – время, отработанное каждым сотрудником по всем проектам и с разбивкой по отдельным кодам работ. Задача 7.2. Сгруппировать информацию в таблице на рисунке 137 по Петрову и Беловой. Решение. Выделяем в сводной таблице соответствующие фамилии и выполняем группировку, например так, как показано на рисунках 138–139.

Рисунок 136

Получаем новую сводную таблицу, в которой отражена нужная информация (рис. 137).

Рисунок 138

Рисунок 137 Рисунок 139

155

156

На рисунке 140 приведена сводная таблица, в которой Иванов и Белова объединены в одну группу со стандартным названием Группа1.

Рисунок 142

Задача 7.3. Пусть сводная таблица преобразована к виду, как на рисунке 143. Выполнить группирование по месяцам.

Рисунок 143

Рисунок 140

Выполнив двойной щелчок по Группа1, получаем таблицу, приведенную на рисунке 141.

Решение. Щелкнув по полю Дата, выделяем весь ряд с датами и выполняем группировку по месяцам, например, с помощью кнопки на панели инструментов Сводная таблица. На рисунках 144–145 приведены этапы выполнения группировки.

Рисунок 141

Вместо стандартного названия Группа1 можно обычным способом ввести более подходящее название, например, Объединение (рис. 142). Рисунок 144

157

Задача 7.4. Выполнить группировку по кодам работ в сводной таблице, приведенной на рисунке 142. Решение. Выполним группирование по кодам работ (они числовые). Щелкнув по полю Код работ, выделяем весь ряд с кодами работ и выполняем группировку по числовому диапазону (рис. 146). 158

Рисунок 145 Рисунок 147

Рисунок 146

На рисунке 147 приведен результат выполнения указанных действий. Задача 7.5. Пусть исходная база данных содержит информацию о продаже продуктов питания в разные годы. Получить сводную таблицу, отражающую прирост по отношению к предыдущему году. Решение. Построим сводную таблицу, в которой на этапе создания макета в поле Данные помещены два поля Объем (рис. 148). 159

Рисунок 148

Выполнив двойной щелчок по Сумма по полю Объем2 в области Данные, получим окно диалога, в котором воспользуемся кнопкой Дополнительно (рис. 149–150).

160

Открыв список Дополнительные вычисления, выберем Приведенное отличие (рис. 151).

Рисунок 149

Рисунок 151

В списке поле выбираем год, а в списке элемент – назад. На рисунке 152 приведен результат применения формулы к сводной таблице.

Рисунок 150

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

161

162

Решение Построим сводную таблицу такого вида, как на рисунке 153.

Рисунок 152

Создадим в сводной таблице формулу для вычисления процента выполнения плана по страховым платежам. Щелкнув правой кнопкой в месте создания формулы, выполняем команду Формулы/Вычисляемое поле (рис. 154) и входим в соответствующее окно диалога (рис. 155). В поле ввода Имя введем название формулы – PROCENT. В поле ввода Формула вводим формулу для вычисления процента выполнения плана. Названия полей Факт и План переносятся выбором из списка Поля и щелчком по кнопке Добавить поле (рис. 156). Рисунок 153

163

164

Рисунок 154

Рисунок 156

Далее следует выполнить форматирование полученных результатов (в данном случае следует округлить полученные при вычислениях проценты, например, до двух знаков после запятой). На рисунке 157 приведена окончательная сводная таблица с формулой. Задача 7.7. Используя сводную таблицу задачи 7.1, получить итоговые функции с максимальным и минимальным количеством отработанных часов по всем проектам. Решение. Воспользуемся таблицей на рисунке 158.

Рисунок 155

165

166

Рисунок 158

Запустив Мастер сводных таблиц, создадим два поля Сумма по полю Часы (рис. 159).

Рисунок 159

Рисунок 157

167

Выполнив двойной щелчок по кнопке Сумма по полю Часы, выберем функцию Минимум (рис.160), а затем отформатируем поле ввода Имя. Аналогично поступаем с кнопкой Сумма по полю Часы2 (рис. 161). 168

Рисунок 160 Рисунок 162

Задача 7.8. Имеются данные о выплатах за три месяца сотрудникам фирмы. Используя сводную таблицу, создать итоговый документ. Полученные результаты проиллюстрировать графиче ски. Решение 1 вариант. Пусть данные расположены на одном листе в нескольких диапазонах. Этим диапазонам присвоены имена январь, февраль и март соответственно. На первом шаге мастера сводных таблиц выбираем вариант создания таблицы на основе данных, находящихся в нескольких диапазонах консолидации (рис. 163).

Рисунок 161

На рисунке 162 представлена полученная сводная таблица. 169

170

На рисунке 165 показано, как выбираются диапазоны таблиц для консолидации.

Рисунок 163

На рисунке 164 приведено второе окно диалога. Рисунок 165

Рисунок 164 Рисунок 166

171

172

На рисунке 167 приведен результат работы мастера сводных таблиц.

Рисунок 168

Рисунок 167

2 вариант. Пусть данные расположены на трех листах в одной рабочей книги. Этим листам присвоены имена январь, февраль и март соответственно. Шаг 2б мастера сводных таблиц в этом случае выглядит так, как на рисунке 168. 3 вариант. Пусть данные расположены в трех рабочих книгах. Активной на момент построения сводной таблицы является книга итог1.xls. Остальные книги итог2.xls и итог3.xls также открыты. Шаг 2б мастера сводных таблиц в этом случае выглядит так, как на рисунке 169. На рисунке 170 приведена диаграмма по результатам построения сводной таблицы. Диаграмма построена с использованием соответствующей кнопки на панели инструментов Сводная таблица. Рисунок 169

173

174

Рисунок 170

Задачи для самостоятельного решения 1. База данных АВТОВЛАДЕЛЬЦЫ содержит следующие поля: Фамилия, Имя, Отчество, Марка машины, Год выпуска, Номерной знак, Серия, Цвет, Цена. 1.1. Есть ли в базе данных однофамильцы? 1.2. Есть ли в базе данных однофамильцы с совпадающими именами? 1.3. Сгруппировать данные по году выпуска машин с заданным шагом. 1.4. Сколько машин разных марок числится в базе данных? 1.5. Вывести максимальную, минимальную и среднюю цены по маркам машин. 1.6. Вывести максимальную, минимальную и среднюю цены по маркам машин и по годам выпуска, сгруппированным с шагом 5 лет. 1.7. Сколько машин в базе данных с заданной серией? 1.8. Сколько машин числится за каждой фамилией? 1.9. Вывести фамилии владельцев заданной марки машин. 1.10. Есть ли в базе данных несколько записей, относящихся к машине с заданным номерным знаком и серией? 2. База данных ШКОЛЬНИКИ содержит следующие поля: Фамилия, Имя, Отчество, IQ (коэффициент умственного развитьия), Год обучения, Параллель, Рост, Оценка по алгебре, 175

Оценка по геометрии, Оценка по физике, Оценка по физкультуре. 2.1. Найти записи, относящиеся к 8 классам. 2.2. Найти записи, относящиеся к 8 и 9 классам. 2.3. Найти записи, относящиеся к 3, 5 и 9 классам. 2.4. Найти записи, относящиеся к отличникам. 2.5. Найти записи, в которых отсутствуют оценки ниже «4». 2.6. Есть ли в школе однофамильцы? 2.7. Есть ли в школе однофамильцы в параллельных классах? 2.8. Есть ли в школе однофамильцы, которые учатся в одном классе? 3. База данных БОЛЬНИЦА содержит следующие поля: Фамилия, Имя, Лечащий врач, Заболевание, Отделение, Дата поступления, Дата выписки, Исход. 3.1. Сколько больных у каждого врача? 3.2. Сколько больных в каждом отделении? 3.3. Вывести количество записей с летальным исходом. 3.4. Вывести количество записей с летальным исходом, приходящимся на зимние месяцы. 3.5. Вывести количество летальных исходов по каждому врачу. 3.6. Найти записи, в которых срок лечения более 1 месяца. 3.7. Определить максимальный, минимальный и средний срок лечения по каждому отделению. 4. База данных СОТРУДНИКИ содержит следующие поля: Фамилия, Имя, Отчество, Пол, Дата рождения (в формате ДД.ММ.ГГ), Должность, Стаж, Оклад. 4.1. Есть ли в базе данных однофамильцы? 4.2. Вывести реальное штатное расписание по имеющейся базе данных с разбивкой по половому признаку. 4.3. Вывести максимальный, минимальный и средний оклады по должностям с разбивкой по половому признаку. 4.4. Сколько сотрудников относится к людям предпенсионного возраста (для женщин — 50–55 лет, для мужчин — 55–60 лет)? 4.5. Сколько сотрудников родилось в каждом месяце? 4.6. Есть ли сотрудники, у которых в этом году отмечается 50-летний юбилей? 176

5. База данных БАСКЕТБОЛИСТЫ содержит следующие поля: Фамилия, Имя, Штат, Команда, Рост, Вес, Количество сезонов, Сыграно матчей, Подборы, Пасы, Перехваты, Очки. 5.1. Есть ли в базе данных однофамильцы? 5.2. Сколько очков набрала каждая команда? 5.3. Сколько команд у каждого штата? 5.4. Вывести максимальный, минимальный и средний рост игроков каждой команды. 5.5. Вывести отношение роста к весу для каждого игрока. 5.6. Вывести отношение роста к весу для каждой команды. 5.7. Вывести отношение роста к весу для всех команд штата. 6. База данных ВИНА содержит следующие поля: Название, Год урожая, Ёмкость, Цвет, Содержание сахара (сухое, полусухое, сладкое, полусладкое), Содержание спирта, Цена 1 бутылки, Количество (в декалитрах). 6.1. Какого года вин в базе данных больше всего? 6.2. Вывести максимальную, минимальную и среднюю цены всех вин, сгруппированных по содержанию сахара. 6.3. Вывести информацию о суммарном количестве вина по годам. 6.4. Вывести информацию о суммарном количестве вина с разбивкой по цвету вина и содержанию спирта. 7. База данных КОМПЬЮТЕРНЫЕ ИГРЫ содержит следующие поля: Место, Всего недель в чарте, Название, Производитель, Категория, Самое высокое место, Цена. 7.1. Сколько игр принадлежит каждому производителю? 7.2. Сколько игр относится к каждой категории? 7.3. Вывести игры с максимальной, минимальной и средней ценой с разбивкой по производителям. 7.4. Сколько игр у каждого производителя занимали 1–3 места? 7.5. Сколько игр по категориям занимали 1–3 места? 7.6. Сколько игр у каждого производителя занимали 1–3 места (с разбивкой по категориям)? 7.7. Вывести игры с максимальной, минимальной и средней ценой с разбивкой по категориям и производителям.

8. База данных КНИГИ содержит следующие поля: Название книги, Автор, Жанр, Год издания, Место издания, Издательство, Количество страниц, Тираж, Спрос, Цена. 8.1. Сколько книг (по названиям) выпустило каждое издательство?. 8.2. Сколько книг (по названиям) относится к заданному жанру? 8.3. Сгруппировать данные по тиражу с разбивкой по издательствам. 8.4. Вывести информацию по количеству страниц, опубликованных каждым автором в каждом издательстве. 8.5. Найти книги, относящиеся к трем заданным издательствам. 8.6. Вывести отношение тиража к количеству страниц по каждому жанру. 9. База данных ПОКУПКИ содержит следующие поля: Код заказа, Наименование товара, Характеристика, Цена, Дата оплаты, Номер карточки, Фамилия владельца карточки, Срок действия карточки. 9.1. Какой товар пользуется наибольшим спросом? 9.2. Кто из покупателей сделал больше всего покупок? 9.3. Кто из покупателей сделал покупок на самую большую сумму? 9.4. Сколько карточек у каждого покупателя ? 9.5. Вывести сведения о максимальной, минимальной и средней цене покупки. 9.6. Использовалась ли одна и та же карточка разными покупателями? 9.7. Сгруппировать срок действия карточек по месяцам. 10. База данных ПРОГРАММЫ содержит следующие поля: Программа, Код программы, Дата записи, Длительность (мин.), Директор, Режиссер, Популярность (в баллах), Цена программы (млн. руб.). 10.1. Определить, сколько программ относятся к каждому директору. 10.2. Сгруппировать программы по дате записи по кварталам. 10.3. Работали ли вместе данный директор и режиссер? 10.4. Вывести рейтинги наиболее и наименее популярных программ каждого директора.

177

178

11. База данных ТЕСТ содержит следующие поля: Фамилия, Возраст, Вес (кг), Рост (см), Индекс Кеттле, Вегетативный индекс, Самочувствие, Настроение, Самооценка здоровья, Сприрометрия, Физическая работоспособность, Уровень физического состояния. 11.1. Сгруппировать записи по возрастному диапазону с шагом 3 года и весу (2 кг). 11.2. Для данного роста вывести наилучшие показатели по спирометрии и физической работоспособности. 11.3. Вычислить отношение роста к весу и вывести этот показатель с разбивкой по самооценке и уровню физического состояния. 11.4. Есть в базе данных однофамильцы? 11.5. Есть ли в базе спортсмены с одинаковым весом и ростом? 12. База данных ФИРМЫ содержит следующие поля: Фирма, Форма собственности, Директор, Адрес, Дата регистрации, Номер счета, Банк, Годовой оборот, Вид деятельности. 12.1. Сколько фирм с заданной формой собственности? 12.2. Есть ли фирмы с одинаковыми названиями? 12.3. Есть ли фирмы, месяц регистрации которых приходится на март? 12.4. Найти названия фирм, год создания которых находится в заданном диапазоне. 12.5. Есть ли несколько фирм с одним адресом? 12.6. Есть ли в списке фирмы, директором которых является один и тот же человек? 13. База данных HAMMER THROW 2002 WR содержит следующие поля: Бросок, Атлет, Страна, Дата рождения, Занятое место, Место выполнения броска, Дата проведения броска. 13.1. Сколько записей относится к летним месяцам выполнения броска? 13.2. Сколько записей, возраст спортсмена в которых находится в заданном диапазоне? 13.3. Вывести максимальный и минимальный броски по странам с разбивкой по годам. 13.4. Вывести фамилии и результаты белорусских спортсменов.

13.5. Сколько спортсменов России, Беларуси и Украины выполнили бросок в заданном диапазоне (с разбивкой по странам)? 14. База данных КРУПНЕЙШИЕ ОЗЕРА ПЛАНЕТЫ содержит следующие поля: Название, Часть света, Страна, Площадь, Площадь в пределах страны, Наибольшая глубина, Наибольшая глубина в пределах страны, Средняя глубина. 14.1. Сколько озер в каждой части света? 14.2. Сколько озер в каждой части света с разбивкой по странам? 14.3. Вывести максимальную, минимальную и среднюю глубины озер с разбивкой по частям света. 14.4. Определить процентное отношение площади озера в пределах страны к общей его площади с разбивкой по частям света. 14.5. Найти записи, относящиеся к озерам, расположенным в Канаде. 15. База данных КРУПНЕЙШИЕ ОСТРОВА ЗЕМЛИ содержит следующие поля: Название, Часть света, Принадлежность государству, Площадь, Численность населения. 15.1. Сколько островов находится в каждой части света? 15.2. Сколько записей относится к островам государств Европы или Африки, площадь которых находится в заданных пределах. 15.3. Вывести максимальную, минимальную и среднюю площади островов Европы и Азии. 15.4. Сколько островов в каждой стране? 15.5. Есть ли в базе данных острова с одинаковым названием? 16. База данных СТОЛИЦЫ содержит следующие поля: Название, Часть света, Страна, Численность населения, Занимаемая площадь, Средний возраст жителей, Количество мужчин старше 18 лет, Количество женщин старше 18 лет, Средняя стоимость 1м2 жилой площади, Бюджет города. 16.1. Сколько стран в каждой части света? 16.2. Вывести максимальную, минимальную и среднюю стоимость 1 м2 жилья в разных частях света. 16.3. Сгруппировать население столиц по численности с некоторым шагом, выполнив разбивку по частям света и половому признаку.

179

180

16.4. Определить максимальную, минимальную и среднюю плотность населения с разбивкой по частям света. 17. База данных ОЛИМПИАДЫ содержит следующие поля: Вид олимпиады (зимняя, летняя), Год проведения, Страна, Город, Количество спортсменов, Количество золотых, серебряных, бронзовых медалей, Вознаграждение за золотую медаль, Вознаграждение за серебряную медаль, Вознаграждение за бронзовую медаль. 17.1. Сколько всего было зимних и летних олимпиад (по отдельности)? 17.2. Сколько было получено медалей разного достоинства на зимних и летних олимпиадах (по отдельности)? 17.3. Каково максимальное, минимальное и среднее вознаграждение за медали разного достоинства на зимних и летних олимпиадах? 17.4. Сколько раз олимпиада проходила в каждой стране? 17.5. Сколько раз олимпиада проводилась и том же городе? 18. База данных ОЛИМПИЙСКИЕ ЧЕМПИОНЫ содержит следующие поля: Фамилия и инициалы спортсмена, Дата рождения (ДД.ММ.ГГ), Вид олимпиады (зимняя, летняя), Год проведения игр, Страна, Город, Команда, Вид спорта. 18.1. Сколько чемпионов родилось в каждый месяц года? 18.2. Вывести фамилии чемпионов в заданных видах спорта. 18.3. Вывести фамилии чемпионов зимних олимпиад, которые родились в зимние месяцы. 18.4. Есть ли однофамильцы с совпадающими инициалами среди чемпионов? 18.5. Сколько олимпийских чемпионов в каждой стране? 18.6. За какие страны выступал конкретный олимпийский чемпион? 19. База данных МЕТРО описывает использованные проездные билеты на метро и содержит следующие поля: Номер Станция, Турникет, День, Час, Осталось. 19.1. Построить сводную таблицу распределения Количества билетов по атрибутам Станция, Турникет для билетов метро на 1 поездку.

19.2. Построить сводную таблицу распределения Количества билетов по атрибутам Станция, Турникет для билетов метро на 2 поездки. 19.3. Построить сводную таблицу распределения Количества билетов по атрибутам Станция, Турникет для билетов метро на 5 поездок. 19.4. Построить сводную таблицу распределения Количества билетов по атрибутам Станция, Турникет для билетов метро на 10 поездок. 19.5. Построить сводную таблицу распределения Количества билетов по атрибутам Станция, Турникет для билетов метро на 20 поездок. 20. База данных содержит следующие поля: код груза, номер вагона, стоимость перевозки, дата отгрузки, дата возврата вагона. 20.1. Получить список номеров вагонов, использовавшихся в первом полугодии прошлого года. 20.2. Найти среднюю стоимость перевозки по каждому из встречающихся кодов грузов. 20.3. Определить общую стоимость перевозок за указанный период. 21. База данных ПРЕТЕНДЕНТЫ содержит следующие поля: Ф.И.О., Вес, Рост, Дата рождения, Пол, Место рождения. 21.1. Найти средний рост и средний вес по каждому из встречающихся мест рождения отдельно для мужчин и женщин. 21.2. Определить количество человек, родившихся за указанный период в указанном месте. 22. База данных содержит следующие поля: Порода собаки, Год рождения, Кличка, Дата регистрации, Ф.И.О. владельца. 22.1. Получить список владельцев и клички овчарок, зарегистрированных во втором квартале позапрошлого года. 22.2. Найти средний возраст собак по каждой из встречающихся пород. 22.3. Найти количество регистраций за каждый день в июле прошлого года. 22.4. Определить количество, максимальный и минимальный возраст собак указанной породы.

181

182

23. База данных ПРОДУКЦИЯ содержит следующие поля: Название продукции, Стоимость за единицу, Количество, Дата выпуска, Изготовитель. 23.1. Получить список названий продукции, выпущенной в четвертом квартале прошлого года. 23.2. Найти общую стоимость продукции по каждому из встречающихся изготовителей. 23.3. Найти количество продукции, выпущенной в каждом из месяцев прошлого года. 23.4. Определить среднюю стоимость за единицу продукции, выпущенной за указанный период указанным изготовителем. 24. База данных содержит следующие поля: шифр абитуриента, название специальности, название предмета, оценка, дата сдачи экзамена. 24.1. Получить список шифров абитуриентов, сдававших экзамены во второй половине июля прошлого года. 24.2. Найти средние баллы по каждому из встречающихся предметов. 24.3. Найти количество абитуриентов, сдававших экзамены, за каждый день в июле прошлого года. 24.4. Определить количество отличных оценок, полученных по указанному предмету для указанной специальности.

183

Приложение Краткая справка по функциям MS Excel

ВПР Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Функция ВПР используется, когда сравниваемые значения расположены в столбце слева от искомых данных. Синтаксис: ВПР(искомое_значение;инфо_таблица;номер_столбца; интервальный_просмотр) Искомое_значение – это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой. Инфо_таблица – это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например, БазаДанных или Список. • Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента инфо_таблица должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A–Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной. • Значения в первом столбце аргумента инфо_таблица могут быть текстами, числами или логическими значениями. • Регистр не учитывается (т. е. строчные и заглавные буквы не различаются). Если номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в аргументе инфо_таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!. 184

Интервальный_просмотр – это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.

ЕСЛИ

Замечания • До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования более сложных проверок. • Когда значения аргументов значение_если_истина и значение_если_ложь вычислены, функция ЕСЛИ возвращает полученное значение.

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция ЕСЛИ используется при проверке условий для значений и формул. Синтаксис: ЕСЛИ(лог_выражение;значение_если_истина; значение_если_ложь) Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения. Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент — строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой. 185

186

Если, например, делаются ежемесячные выплаты по четырехгодичному займу под 12 % годовых, то следует использовать 12 %/12 для задания аргумента Ставка. Если делаются ежегодные платежи по тому же займу, то следует использовать 12 % для задания аргумента Ставка. В финансовых функциях выплачиваемые Вами деньги представляются отрицательным числом; деньги, которые Вы получаете, представляются положительным числом.

ПС Возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат. Синтаксис:

КПЕР

ПС(ставка; кпер; выплата; бз; тип)

Возвращает общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки. Синтаксис: КПЕР(ставка;платеж;нз;бз;тип) Ставка – это процентная ставка за период; плата – это выплата, производимая в каждый период; это значение не может меняться в течении всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов; нз – это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента; бз – это будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бз опущен, то предполагается, что он равен 0 (например, бз для займа равно 0); тип – это число 0 или 1, обозначающее, когда должна производиться выплата. Тип Когда нужно платить 0 или опущено В конце периода (обычная или постнумерано) 187 1 В начале периода (пренумерано)

Ставка – это процентная ставка за период выплат. Например, если Вы получили ссуду под автомобиль под 10 % годовых и делаете ежемесячные равные выплаты, то процентная ставка за месяц составит 10 %/12, или 0,83 %; кпер – это общее число периодов выплат. Например, если Вы получили ссуду на 4 года под автомобиль и делаете ежемесячные равные платежи, то ссуда имеет 4*12=48 периодов. Число 48 следует ввести в формулу в качестве значения аргумента кпер; выплата – это выплата, производимая в каждый период и не меняющаяся за все время выплаты ссуды; бз – это будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если бз опущено, она полагается равной 0.

АМР Возвращает величину непосредственной амортизации имущества за один период. Синтаксис: АМР(стоимость;остаток;период) Нач_стоимость – это начальная стоимость имущества. 188

Ост_стоимость – это стоимость в конце периода амортизации (иногда называется остаточной стоимостью имущества). Время_эксплуатации – это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).

он полагается равным 0. В этом случае должно быть указано значение аргумента плата. Тип — это число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент тип опущен, то он полагается равным 0.

ППЛАТ Вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки. Синтаксис: ППЛАТ(ставка;кпер;нз;бз;тип) Ставка — это процентная ставка по ссуде. Кпер — это общее число выплат по ссуде. Нз — это текущее значение или общая сумма, которую составят будущие платежи, называемая также основной суммой. Бз — это будущая сумма или баланс наличности, которой нужно достичь после последней выплаты. Если бз опущено, оно полагается равным 0, т.е. будущая сумма ссуды, например, равна 0. Тип — это число 0 (ноль) или 1, обозначающее, когда должна производиться выплата (см. справку по функции ПЗ).

ЕОШИБКА Используются для проверки типа значения или ссылки. Синтаксис: ЕОШИБКА(значение) Возвращает ИСТИНА, если значение ссылается на любое значение ошибки (#Н/Д, #ЗНАЧ! , #ССЫЛ! , #ДЕЛ/0! , #ЧИСЛО! , #ИМЯ? или #ПУСТО!). Значение — это проверяемое значение. Значение может быть пустой ячейкой, значением ошибки, логическим значением, текстом, числом, ссылкой или именем объекта любого из перечисленных типов. ЕЧИСЛО

БЗ

Синтаксис:

Возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Синтаксис:

ЕЧИСЛО(значение) Возвращает ИСТИНА, если значение ссылается на число. Значение — это проверяемое значение. Значение может быть пустой ячейкой, значением ошибки, логическим значением, текстом, числом, ссылкой или именем объекта любого из перечисленных типов.

БЗ(ставка;кпер;плата;нз;тип) Ставка — это процентная ставка за период. Кпер — это общее число периодов выплат годовой ренты. Плата — это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если аргумент опущен, должно быть указано значение аргумента нз. Нз — это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент нз опущен, то 189

ДЛСТР Функция ДЛСТР возвращает количество символов в текстовой строке. Синтаксис: ДЛСТР(текст) 190

Текст — это текст, длина которого определяется. Пробелы считаются как символы.

Синтаксис:

ЛЕВСИМВ

Ссылка — это ячейка или интервал ячеек, для которых определяется номер строки. • Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТРОКА. • Если ссылка является интервалом ячеек, и если функция СТРОКА введена как вертикальный массив, то функция СТРОКА возвращает номера строк в аргументе ссылка в виде вертикального массива. • Ссылка не может ссылаться на несколько областей.

Возвращает указанное число первых символов текстовой строки. Синтаксис: ЛЕВСИМВ(текст;количество_символов) Текст — это текстовая строка, которая содержит извлекаемые символы. Количество_символов определяет, сколько символов должна извлечь функция ЛЕВСИМВ. • Количество_символов должно быть больше или равно нулю. • Если количество_символов больше длины текста, то функция ЛЕВСИМВ возвращает весь текст. • Если количество_символов опущено, то оно полагается равным 1. ПОВТОР Повторяет текст заданное число раз. Функция ПОВТОР используется, чтобы заполнить ячейку некоторым количеством вхождений текстовой строки. Синтаксис: ПОВТОР(текст;сколько_раз) Текст — это повторяемый текст. сколько_раз — это положительное число, определяющее, сколько раз нужно повторить текст. Если сколько_раз равно 0 (нулю), то функция ПОВТОР возвращает «» (пустой текст). Если сколько_раз не целое, то оно усекается. Результат функции ПОВТОР не должен превышать 255 символов. СТРОКА Возвращает номер строки, определяемой ссылкой. 191

СТРОКА(ссылка)

СТОЛБЕЦ Возвращает номер столбца по заданной ссылке. Синтаксис: СТОЛБЕЦ(ссылка) Ссылка - это ячейка или интервал ячеек, для которых определяется номер столбца. • Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТОЛБЕЦ. • Если ссылка является интервалом ячеек, и если функция СТОЛБЕЦ введена как горизонтальный массив, то функция СТОЛБЕЦ возвращает номера столбцов в ссылке в виде горизонтального массива. • Ссылка не может ссылаться на несколько областей. ДЕНЬНЕД Возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 1 (воскресенье) до 7 (суббота). Синтаксис: ДЕНЬНЕД(дата_в_числовом_формате;тип) Дата_в_числовом_формате — это число, соответствующее дате, день недели которой необходимо найти. Даты могут вводиться как текстовые строки в двойных кавычках (например "30.1.1998"), как числа (например 35825 представляет 30 января 192

1998 г. при использовании системы дат 1900) или как результат других формул или функций (например ДАТАЗНАЧ("30.1.2001")). Тип — это число, которое определяет тип возвращаемого значения. Тип

Возвращаемое число

1 или опущен

Число от 1 (воскресенье) до 7 (суббота). Аналогично предыдущей версии Microsoft Excel.

2

Число от 1 (понедельник) до 7 (воскресенье)

ОСТАТ

3

Число от 0 (понедельник) до 6 (воскресенье)

Возвращает остаток от деления аргумента число на делитель. Результат имеет такой же знак, как и делитель. Синтаксис:

ГОД Возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое в интервале 1900—9999. Синтаксис: ГОД(дата_в_числовом_формате) Дата_в_числовом_формате — это дата, год которой необходимо найти. СЕГОДНЯ Возвращает текущую дату в числовом формате. Числовой формат даты – это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени Синтаксис: СЕГОДНЯ()

193

ОСТАТ(число;делитель) Число — это число, остаток от деления которого определяется. Делитель — это число, на которое нужно разделить (делитель). Если делитель равен 0, то функция ОСТАТ возвращает значение ошибки #ДЕЛ/0!. СУММПРОИЗВ Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений. Синтаксис: СУММПРОИЗВ(массив1;массив2;массив3; ...) Массив1, массив2, массив3, ... — это от 2 до 30 массивов, чьи компоненты нужно перемножить, а затем сложить. • Аргументы, которые являются массивами, должны иметь одинаковые размерности. • СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.

194

СУММ Суммирует все числа в интервале ячеек. Синтаксис: СУММ(число1;число2; ...) Число1, число2, ... — это от 1 до 30 аргументов, для которых требуется определить итог или сумму. • Учитываются числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов. • Если аргумент является массивом или ссылкой, то только числа учитываются в массиве или ссылке. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются. • Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки. ABS Возвращает модуль (абсолютную величину) числа. Абсолютная величина числа — это число без знака. Синтаксис: ABS(число) Число — это действительное число, абсолютную величину которого требуется найти. МАКС Возвращает наибольшее значение из набора значений. Синтаксис: МАКС(число1;число2; ...) Число1, число2, ... — это от 1 до 30 чисел, среди которых ищется максимальное значение. • Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значе195

ниями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок. • Если аргумент является массивом или ссылкой, то в нем учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА. • Если аргументы не содержат чисел, то функция МАКС возвращает 0 (ноль). МИН Возвращает наименьшее значение в списке аргументов. Синтаксис: МИН(число1;число2; ...) Число1, число2, ... — это от 1 до 30 чисел, среди которых ищется минимальное значение. • Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок. • Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения или тексты в массиве или ссылке игнорируются. Если логические значения или тексты игнорироваться не должны, следует пользоваться функцией МИНА. • Если аргументы не содержат чисел, то функция МИН возвращает 0. СРЗНАЧА Вычисляет среднее арифметическое значений, заданных в списке аргументов. Помимо чисел в расчете могут участвовать текст и логические значения, такие, как ИСТИНА и ЛОЖЬ. Синтаксис: СРЗНАЧА(значение1,значение2,...) 196

Значение1, значение2,... — это от 1 до 30 ячеек, интервалов ячеек или значений, для которых вычисляется среднее. Замечания • Аргументы должны быть числами, именами, массивами или ссылками. • Массивы и ссылки, содержащие текст, интерпретируются как 0 (ноль). Пустой текст ("") интерпретируется как 0 (ноль). Если при расчете не требуется учитывать текстовые значения следует использовать функцию СРЗНАЧ. • Аргументы, содержащие значение ИСТИНА, интерпретируются как 1, Аргументы, содержащие значение ЛОЖЬ, интерпретируются как 0 (ноль). БСЧЁТ Подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных в указанном поле для всех записей, удовлетворяющих заданным условиям. Аргумент поле не является обязательным. Если аргумент поле опущен, то функция БСЧЁТ подсчитывает количество записей в базе данных, отвечающих критериям. Синтаксис:

ДМИН Возвращает наименьшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям. Синтаксис: ДМИН(база_данных;поле;критерий) База_данных — это интервал ячеек, формирующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Верхняя строка списка содержит названия каждого столбца. Поле — определяет столбец, используемый функцией. Аргумент «поле» может быть задан как текст с названием столбца в двойных кавычках, например «Возраст» или «Урожай», или как число, задающее положение столбца в списке: 1 — для первого поля, 2 — для второго поля и так далее. Критерий — это интервал ячеек, который содержит задаваемые условия. Любой интервал, который содержит по крайней мере одно название столбца и по крайней мере одну ячейку под названием столбца с условием, может быть использован как аргумент критерий БДФункции.

БСЧЁТ(база_данных;поле;критерий) ДСРЗНАЧ Усредняет значения в столбце списка или базы данных, удовлетворяющих заданным условиям. Синтаксис: ДСРЗНАЧ(база_данных;поле;критерий) ДМАКС Возвращает наибольшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям. Синтаксис: ДМАКС(база_данных;поле;критерий) 197

198

Рекомендуемая литература 1. Абрамов С.А., Гнездилова Г.Г., Капустина Е.Н., Селюн М.И. Задачи по программированию.–М.:Наука,1988.–224 с. 2. Бернс П.,.Николсон Д. Секреты Excl для Windows 95. – ДК.:Диалектика.1996.–576 с. 3. Вагнер Д. Основы исследования операций: В 3 т. Т.1.–М.:Мир, 1972.–336 с. 4. Вентцель Е.С. Исследование операций. –М.:Советское радио, 1972.–552 с. 5. Винтер Р., Винтер П.. Microsoft Office для Windows 95 в подлиннике. – СПб.:BHV–Санкт–Петербург,1996.–1056 с. 6. Гончаров А. EXEL 7.0 в примерах. – СПб:Питер, 1996.–256 с. 7. Дубина Д., Орлова С., Шубина И., Хромов А.. Excel для экономистов и менеджеров.–.СПб.:Питер, 2004. – 295 с. 8. Капустин В.Ф. Практические занятия по курсу математического программирования.– Л.:Изд–во Ленингр. ун–та, 1976.–192 с. 9. Каратыгин С.А., Тихонов А.Ф., Долголаптев В.Г., Ильина М.М., Тихонова Л.И. Электронный ОФИС: В 2 т. Т.1.–М.:Нолидж, 1999.–768 с. 10. Карлберг К. EXEL 5 для Windows в вопросах и ответах. – СПб.: BHV – Санкт-Петербург, 1995. – 416 с. 11. Колесников А., Пробитюк А. EXEL 7.0 для Windows 95. – К: Торговоиздательское бюро BHV, 1996. – 464 с. 12. Костевич Л.С.. Математическое программирование. – Минск: Новое знание, 2003. – 424 с. 13. Методические рекомендации по курсу «Автоматизация офисной деятельности»/Сост. И.Н.Ревчук, В.К.Пчельник. – Гродно:ГрГУ, 2001. –60 с. 14. Морозов В.В., Сухарев А.Г., Федоров В.В. Исследование операций в задачах и упражнениях. –М.:Высш.шк., 1986. –287 с. 15. Мур Дж., Уэдерфорд Л. и др. Экономическое моделирование в Microsoft Excel. – М.: Издательский дом «Вильямс», 2004. –1024 с. 16. Осейко Н.Н. EXEL 5.0 для пользователя. – К.: Торгово-издательское бюро BHV. 1994 – 416 с. 17. Персон Р. Excel для Windows 95 в подлиннике. –СПб.:BHV–СанктПетербург,1996.–1056 с. 18. Ревчук И.Н., Пчельник В.К. Автоматизация офисной деятельности. –Гродно: ГрГУ, 2004. –128 с. 19. Таха Х. Введение в исследование операций: В 2 т. Т.1. – М.:Мир, 1985. – 479 с.

199

20. Уокенбах Д. Диаграммы в Excel. – М.: Издательский дом «Вильямс», 2003. –448 с. 21. Филипс Д., Гарсиа–Диас. Методы анализа сетей. – М.:Мир, 1984. –496 с. 22. Форд Л., Фалкерсон Д. Потоки в сетях. –М.:Мир, 1966. –276 с.

СОДЕРЖАНИЕ 1. ВВЕДЕНИЕ В ЭЛЕКТРОННЫЕ ТАБЛИЦЫ MS EXCEL ……. 2. СОСТАВЛЕНИЕ ФОРМУЛ ……………………………. Примеры решения задач……………………………… Задачи для самостоятельного решения……………… 3. ИСПОЛЬЗОВАНИЕ НАДСТРОЙКИ «ПОИСК РЕШЕНИЯ»… Примеры решения задач……………………………… Задачи для самостоятельного решения……………… 4. ПОСТРОЕНИЕ ГРАФИКОВ ФУНКЦИЙ И ДИАГРАММ …. Примеры решения задач……………………………… Задачи для самостоятельного решения……………… 5. УПРАВЛЕНИЕ СПИСКАМИ (БАЗАМИ ДАННЫХ).. Примеры решения задач……………………………… Задачи для самостоятельного решения……………… 6. СОРТИРОВКА СПИСКОВ. ПРОМЕЖУТОЧНЫЕ ИТОГИ…… Сортировка списка…………………..…….………….. Примеры решения задач………………….…..………. Промежуточные итоги………………………………... Примеры решения задач…………………………..….. Задачи для самостоятельного решения……………… 7. АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ………………………………………………….. Примеры решения задач……………………………… Задачи для самостоятельного решения……………… ПРИЛОЖЕНИЕ……………………………………..……….. РЕКОМЕНДУЕМАЯ ЛИТЕРАТУРА……………….……...

200

3 21 27 45 51 55 65 75 75 99 105 106 121 134 134 137 140 141 149 150 153 175 184 199

Учебное издание Ревчук Ирина Николаевна Пчельник Владимир Константинович КОМПЬЮТЕРНЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ

Учебное пособие

Редактор И.А.Ушакова Компьютерная верстка: И.Н.Ревчук

Сдано в набор 21.03.05. Подписано в печать 21.04.05 Формат 60х84/16. Бумага офсетная. Печать RISO. Гарнитура Таймс. Усл.печ.л. 11,16. Уч.-изд.л. 10,40. Тираж 100 экз. Заказ . Учреждение образования «Гродненский государственный университет имени Янки Купалы». ЛИ № 02330/0133257 от 30.04.2004. Ул. Пушкина, 39, 230012, Гродно. Отпечатано на технике издательского центра Учреждения образования «Гродненский государственный университет имени Янки Купалы». ЛП № 02330/0056882 от 30.04.2004.Ул. Пушкина, 39, 230012, Гродно.

201

202