close

Вход

Забыли?

вход по аккаунту

?

AgranovskiyIlinskya

код для вставкиСкачать
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
Федеральное государственное автономное
образовательное учреждение высшего образования
САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ
ОБРАБОТКА ДАННЫХ СРЕДСТВАМИ
ЭЛЕКТРОННЫХ ТАБЛИЦ
MICROSOFT EXCEL
Методические указания по выполнению лабораторных работ
по курсу «Информационные системы и технологии»
Санкт-Петербург
2015
Составители: А. В. Аграновский, Д. М. Ильинская, Е. Л.Турнецкая 
Рецензент – доцент, кандидат технических наук Л. А. Кулыгина
Методические указания по выполнению лабораторных работ по
курсу «Информационные системы и технологии» предназначены для
бакалавров, обучающихся по направлению 09.03.03. Часть 1. Обработка данных средствами электронных таблиц Microsoft Excel.
Публикуется в авторской редакции.
Компьютерная верстка М. И. Дударева
Подписано к печати 26.10.15. Формат 60 × 84 1/16.
Бумага офсетная. Усл. печ. л. 3,2. Тираж 100 экз. Заказ № 385.
Редакционно-издательский центр ГУАП
190000, Санкт-Петербург, Б. Морская ул., 67
© Санкт-Петербургский государственный
университет аэрокосмического
приборостроения, 2015
ПРЕДИСЛОВИЕ
Выполнение лабораторных работ по дисциплине «Информационные системы и технологии» позволит студентам, обучающимся
по направлению 09.03.03 «Прикладная информатика», приобрести
практические навыки работы в среде Microsoft Excel, которые необходимы для построения успешной карьеры в кампании, занимающейся разработкой, внедрением и поддержкой информационных
систем любого профиля на всем протяжении их жизненного цикла.
В лабораторных работах рассматриваются следующие вопросы:
– решение числовых задач, требующих больших вычислений;
– создание и настройка диаграмм и графиков;
– эффективная организация структурированных таблиц и списков;
– обеспечение безопасности данных в электронных таблицах;
– автоматизация рутинных операций в Excel .
В приложении представлены индивидуальные задания по каждой лабораторной работе.
Приведенные литературные источники содержат достаточные
сведения для выполнения всех лабораторных работ и могут рассматриваться в качестве справочников, в которых приведены множество примеров использования документированных функций и дополнительных возможностей среды Microsoft Excel.
3
Лабораторная работа №1
ТЕХНОЛОГИИ СОЗДАНИЯ И ФОРМАТИРОВАНИЯ
ЭЛЕКТРОННОЙ ТАБЛИЦЫ
Цель работы: Знакомство с приложением MS EXCEL. Приобретение элементарных навыков работы в среде пакета: ввод и редактирование данных; форматирование электронной таблицы.
Краткие теоретические сведения
1. Форматирование таблицы с использованием контекстного
меню
Форматирование таблиц в MS EXCEL необходимо для улучшения восприятия пользователем записанных в них данных, что позволит существенно повысить производительность труда.
Назначение формата производится следующим образом:
1. Выделяется ячейка (диапазон ячеек).
2. После нажатия на правую кнопку мыши выбирается команда
Формат→Ячейки.
3. В появившемся окне диалога вводятся нужные параметры
форматирования.
Форматированная ячейка сохраняет свой формат, пока к ней
не будет применен новый формат или не удален старый. При вводе
значения в ячейку к нему применяется уже используемый в ячейке
формат.
Удаление формата осуществляется следующим порядком действий:
1. Выделяется ячейка (диапазон ячеек).
2. После нажатия на правую кнопку мыши выбирается команда
Правка → Очистить → Форматы.
3. Для удаления значений в ячейках требуется применить команду Все подменю Очистить.
Следует учитывать, что при копировании ячейки наряду с ее содержимым копируется и формат ячейки. Таким образом, можно
сберечь время, форматируя исходную ячейку до использования команд копирования и вставки.
Форматирование с помощью ленточных команд
Наиболее часто используемые команды форматирования вынесены в группу Число ленточной вкладки Главная
1. Чтобы применить формат с помощью кнопки панели инструментов, выделяется ячейка или диапазон ячеек и затем производится клик на кнопку мышью.
2. Для удаления формата надо нажать кнопку повторно.
4
3. Для быстрого копирования форматов из выделенных ячеек
в другие ячейки можно использовать кнопку Формат по образцу
панели Форматирование.
Форматирование отдельных символов
Форматирование можно применять к отдельным символам текстового значения в ячейке так же, как и ко всей ячейке.
1. Для этого выделяют нужные символы и затем в меню Формат
выбирают команда Ячейки.
2. Устанавливают нужные атрибуты и нажимают кнопку Ок.
3. После нажатия клавиши Enter можно увидеть результат форматирования
Применение автоформата
Автоматические форматы Excel – это заранее определенные сочетания числового формата, шрифта, выравнивания, границ, узора, ширины столбца и высоты строки [1].
Чтобы использовать автоформат, надо выполнить следующие
действия:
1. Ввести нужные данные в таблицу.
2. Выделить диапазон ячеек, которые необходимо форматировать.
3. В меню Формат выбрать команду Автоформат. При этом откроется окно диалога.
4. В окне диалога Автоформат нажать кнопку Параметры, чтобы
отобразить область Изменить.
5. Выбрать подходящий автоформат и нажать кнопку Ок.
6. Выделить ячейку вне таблицы для снятия выделения с текущего блока.
2. Форматирование чисел и текста
Окно диалога Формат ячеек, представленное на рис. 1, позволяет
управлять отображением числовых значений и изменять вывод текста.
Перед открытием окна диалога необходимо выделить ячейку, содержащую число, которое надо форматировать. В этом случае всегда
будет виден результат в поле Образец. Не следует забывать о различии
между хранимыми и отображаемыми значениями. На хранимые числовые или текстовые значения в ячейках форматы не действуют.
Рассмотрим наиболее часто встречающиеся форматы ячеек.
Общий формат
Любое введенное текстовое или числовое значение по умолчанию отображается в формате «Общий». При этом оно отображается
точно так, как было введено в ячейку за исключением трех случаев:
1. Длинные числовые значения отображаются в экспоненциальной форме записи или округляются.
2. Формат не отображает незначащие нули (456,00 = 456).
5
Рис. 1. Скриншот окна диалога «Формат ячеек»
3. Десятичная дробь, введенная без числа слева от десятичной
запятой, выводится с нулем (,23 = 0,23).
Числовой формат
Этот формат позволяет выводить числовые значения в виде целых чисел или чисел с фиксированной запятой, а также выделять
отрицательные числа с помощью цвета.
Денежный формат
Этот формат аналогичен числовому формату за исключением того, что вместо разделителя групп разрядов он позволяет управлять
выводом символа денежной единицы, который можно выбрать
в списке Обозначение.
Текстовый формат
Применение к ячейке текстового формата означает, что значение в этой ячейке должно трактоваться как текст, о чем свидетельствует выравнивание по левому краю ячейки.
Если числовое значение отформатировано как текст, то Excel распознает числовые значения. Ошибка возникает, если в ячейке, имеющей текстовый формат, стоит формула. В этом случае формула рассматривается как простой текст, поэтому возможны ошибки.
3. Создание бланков в электронных таблицах.
При работе с электронным документооборотом в организациях часто
требуется создавать унифицированные бланки на основе имеющихся
в электронных таблицах данных. Для их создания авторы методического пособия предлагают воспользоваться следующим алгоритмом [2]:
6
1. На отдельный лист из электронной таблице копируются необходимые для заполнения бланка данные.
2. На другом листе требуется создать бланк установленного образца.
3. Для выполнения автоматической подстановки данных в ячейки
на бланке с помощью абсолютных ссылок требуется связать оба листа.
Добавление проверки данных в ячейке или диапазоне ячеек
Для настройки проверки данных в диапазоне ячеек используется окно диалога Проверка вводимых значений. Параметры, настраиваемые в этом окне, зависят от типа ограничения, налагаемого на
ячейки диапазона.
Порядок выполнения работы
1. Сформировать таблицу учета, которая должна иметь структуру, соответствующую выданному варианту задания. Присвоить
листу, на котором размещается таблица, имя, соответствующее содержанию находящихся в ней данных.
2. Заполнить таблицу реальным (правдоподобным) непротиворечивым содержимым, состоящим не менее чем из 20 записей. Значения полей могут повторяться. Столбцы, требующие вычисления,
оставить нетронутыми.
3. Выполнить форматирование таблицы
– Выбрать форматы представления данных в таблице соответствующие хранимой в ячейках информации (текстовый, числовой,
дата, денежный и др.).
– При необходимости определить способ выравнивания и ориентацию информации в ячейке, объединить смежные ячейки, а также установить способ переноса текста по словам.
– Выполнить обрамление границ таблицы.
– Изменить параметры шрифта и задать цвет фона заголовков
таблицы.
4. Подобрать оптимальную ширину столбцов и высоту строк.
5. Скопировать значения полученной таблицы на другой лист.
Выполнить автоформатирование таблицы.
6. Сформировать бланк документа, соответствующий теме задания. Бланк должен быть подготовлен к печати.
Состав отчета
– Цель работы
– Вариант задания
– Описание выполненных действий
– Скриншоты полученных таблиц
– Выводы
7
Лабораторная работа № 2
ТЕХНОЛОГИЯ ВЫЧИСЛЕНИЙ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ
Цель работы: Приобретение навыков работы с формулами. Рассмотрение простейших функций: математических, статистических, текстовых и логических. Использование абсолютной и относительной ссылки. Определение и использование имен в формулах.
Применение условного форматирования.
Краткие теоретические сведения
1. Рассмотрим математические функции: СУММ, СУММЕСЛИ.
Функция СУММ ().
Функция СУММ суммирует множество чисел. Эта функция
имеет следующий синтаксис: =СУММ(числа).
Аргумент числа может включать до 30 элементов [3], каждый
из которых может быть числом, формулой, диапазоном или ссылкой на ячейку, содержащую или возвращающую числовое значение. Функция СУММ игнорирует аргументы, которые ссылаются
на пустые ячейки, текстовые или логические значения. Аргументы не обязательно должны образовывать непрерывные диапазоны
ячеек. Например, чтобы получить сумму чисел в ячейках А5, В15 и
в ячейках от С1 до Е10, введите каждую ссылку как отдельный аргумент: =СУММ(А5;В15;С1:Е10).
Функция СУММЕСЛИ().
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазона, соответствующие указанному критерию. Например, в столбце с числами необходимо просуммировать только значения, превышающие значение 10. Для этого можно использовать формулу: =СУММЕСЛИ(С2:С25,">10").
В данном примере на соответствие критерию проверяются суммируемые значения. При необходимости условие можно применить к одному диапазону, просуммировав соответствующие значения из другого диапазона. Например, формула
=СУММЕСЛИ(А1:А5; "Тигр"; C1:C5) суммирует только те значения из диапазона А1:А5, для которых соответствующие значения
из диапазона С1:С5 равны "Тигр".
Рассмотрим аргументы функции СУММЕСЛИ:
СУММЕСЛИ(диапазон, условие, [диапазон_суммирования])
Диапазон. Обязательный аргумент. Диапазон ячеек, оцениваемых по критериям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые ячейки
и ячейки, содержащие текстовые значения, пропускаются.
8
Условие. Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее,
какие ячейки необходимо просуммировать. Например, условие может быть представлено в следующем виде: 41, ">41", С3, "4", "банан" или СЕГОДНЯ(). Следует помнить, что все текстовые условия
и условия с логическими и математическими знаками необходимо
заключать в двойные кавычки ("). Если условием является число,
использовать кавычки не требуется.
Диапазон_суммирования. Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от
ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Microsoft Excel суммирует ячейки,
указанные в аргументе диапазон (те же ячейки, к которым применяется условие).
2. Рассмотрим статистические функции: МИН, МАКС,
СРЗНАЧ, СЧЕТ, СЧЕТЗ, СЧЕТЕСЛИ, НАИБОЛЬШЕЕ, НАИМЕНЬШЕЕ.
Функция СЧЁТ()
Подсчитывает количество числовых значений в диапазоне.
Синтаксис: =СЧЁТ(значение1; [значение2]; …), где значение1 – обязательный аргумент, принимающий значение, ссылку
на ячейку, диапазон ячеек или массив. Логические значения в диапазонах и массивах игнорируются. Если такое значение задано явно в аргументе, то оно учитывается как число.[3]
Пример использования:
=СЧЁТ(2; 3; "41") – результат функции 3, т.к. значение
"41" конвертируется в число. =СЧЁТ({2; 3; "41"}) – результатом выполнения функции будет значение 2, так как, в отличие
от первого примера, число в виде строки записано в массиве, поэтому не будет преобразовано.
=СЧЁТ(2; 3; ИСТИНА) – результат функции 3. Если бы логическое значение находилось бы в массиве, то оно не засчиталось
как число.
Функция СЧЁТЗ()
Подсчитывает непустые ячейки в указанном диапазоне. [4]
Синтаксис: =СЧЁТЗ(значение1; [значение2]; …), где значение1
является обязательным аргументом, все последующие аргументы
до значения 255 необязательны. В качестве значения может содержаться ссылка на ячейку или диапазон ячеек.
Ячейки, содержащие пустые строки (=""), засчитываются как
НЕпустые.
9
Функция СЧЕТЕСЛИ()
Функцию СЧЕТЕСЛИ (диапазон; критерий) можно эффективно использовать при создании формул для подсчета с одним критерием. Аргументами функции являются:
Диапазон. Диапазон ячеек который подсчитывается.
Критерий. Логическое условие, по которому определяется, какие ячейки будут включены в подсчет.
Функция СРЗНАЧ()
Возвращает среднее арифметическое значение заданных аргументов.
Синтаксис: =СРЗНАЧ(число1; [число2]; …), где число1 является обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические
значения в диапазонах и массивах игнорируются.
Функция МАКС ()
Возвращает максимальное числовое значение из списка аргументов.
Синтаксис: =МАКС(число1; [число2]; …), где число1 является
обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические
значения в диапазонах и массивах игнорируются.
Пример использования:
=МАКС({2;3;4;5;0;–6;6;"41"}) – возвращает результат 6, при
этом строка "41" игнорируется, т.к. задана в массиве.
=МАКС(2;3;4;5;0;–6;6;"41") – результатом функции будет
41, т.к. строка явно задана в виде отдельного аргумента и может
быть преобразована в число.
=МАКС(–6; ИСТИНА) – возвращает 1, т.к. логическое значение
задано явно, поэтому не игнорируется и преобразуется в единицу.
Функция МИН()
Возвращает минимальное числовое значение из списка аргументов.
Синтаксис: =МИН(число1; [число2]; …), где число1 является
обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические
значения в диапазонах и массивах игнорируются.
Пример использования:
=МИН({2;3;4;5;0;–6;6;"–41"}) – возвращает результат –6, текстовая строка игнорируется.
10
=МИН(2;3;4;5;0;–6;6;"–41") – результатам функции будет
–41, так как строка "–41" задана в виде отдельного аргумента и может быть преобразована в число.
=МИН(6; ИСТИНА) – возвращает 1, так как логическое значение задано явно в виде аргумента, поэтому не игнорируется и преобразуется в единицу.
Функция НАИБОЛЬШИЙ()
Возвращает значение элемента, являвшегося n-ым наибольшим, из указанного множества элементов. Например, второй наибольший, четвертый наибольший.
Синтаксис: =НАИБОЛЬШИЙ(массив; n), где
массив – диапазон ячеек либо массив элементов, содержащий
числовые значения. Текстовые и логические значения игнорируются.
n – натуральное число (кроме нуля), указывающее позицию элемента в порядке убывания. Если задать дробное число, то оно округляется до целого в большую сторону (дробные числа меньше единицы возвращают ошибку). Если аргумент превышает количество
элементов множества, то функция возвращает ошибку. [5]
Массив или диапазон может быть не отсортирован.
Функция НАИМЕНЬШИЙ()
Возвращает значение элемента, являвшегося n-ым наименьшим, из указанного множества элементов. Например, третий наименьший, шестой наименьший.
Синтаксис: =НАИМЕНЬШИЙ(массив; n), где
массив – диапазон ячеек либо массив элементов, содержащий
числовые значения. Текстовые и логические значения игнорируются.
n – натуральное число (кроме нуля), указывающее позицию элемента в порядке возрастания. Если задать дробное число, то оно
округляется до целого в меньшую сторону (дробные числа меньше
единицы возвращают ошибку). Если аргумент превышает количество элементов множества, то функция возвращает ошибку.
Массив или диапазон НЕ обязательно должен быть отсортирован.
На рис. 2 показан скриншот выполненного задания с использованием функций СЧЕТЕСЛИ(), СУММ(), НАИБОЛЬШИЙ(),
НАИМЕНЬШИЙ().
3. Логические функции: ЕСЛИ, И, ИЛИ.
Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или
логические значения. Любое логическое выражение должно содер11
Рис. 2. Скриншот выполненного задания с использованием функций
СЧЕТЕСЛИ(), СУММ(), НАИБОЛЬШИЙ(), НАИМЕНЬШИЙ()
жать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения. В Excel
принят следующий список операторов сравнения [1]: = Равно; >
Больше; < Меньше; >= Больше или равно; <= Меньше или равно;
<> Не равно.
Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
Функция ЕСЛИ()
Функция ЕСЛИ() имеет следующий синтаксис: =ЕСЛИ( логическое_выражение;значение_если_истина;значение_если_ложь)
Следующая формула возвращает значение 21, если значение
в ячейке А1 больше 10, а в противном случае 5: =ЕСЛИ(А1>10;21;5)
В качестве аргументов функции ЕСЛИ можно использовать текстовые аргументы. Например: =ЕСЛИ(А1>=4;"Вода живая";"Вода
мертвая").
Можно использовать текстовые аргументы в функции ЕСЛИ(),
чтобы при невыполнении условия она возвращала пустую строку
вместо 0. 
Например: =ЕСЛИ(СУММ(А2:А11)=10;А10;"").
Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например: =ЕСЛИ(А1="Волшебная палочка";20;35). Эта формула возвращает значение 20, если ячейка
А1 содержит строку "Волшебная палочка", и 35, если в ней находится любое другое значение. Совпадение всех символов происходит без учета регистров их написания.
На рис. 3 показан пример скриншота одного из вариантов задания к данной лабораторной работы.
12
Рис. 3. Скришот фрагмента выполненного задания  
лабораторной работы №2
Функции И(), ИЛИ(), НЕ()
Функции И (AND), ИЛИ (OR), НЕ (NOT) обычно применяются
в сочетании с простыми операторами сравнения и позволяют создавать сложные логические выражения [6]. Функции И() и ИЛИ()
могут иметь до 30 вложенных друг в друга логических аргументов.
Синтаксис этих функций таков:
=И(логическое_значение1;логическое_значение2...).
=ИЛИ(логическое_значение1;логическое_значение2...).
Функция НЕ() имеет только один аргумент и следующий синтаксис:
=НЕ(логическое_значение).
Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения. Например. Пусть Excel возвращает текст «Допущен», если обучающийся имеет средний балл более 4 (ячейка
А3) и пропуск занятий меньше 3 (ячейка А4). Формула примет вид:
=ЕСЛИ(И(А3>4;А4<3);"Допущен";"Не допущен")
Условное форматирование
Условное форматирование является удобным инструментом
анализа данных. Выбранный пользователем цвет будет соответствовать определенным данным, что позволит визуально определить критические ячейки.
Для применения условного форматирования служит кнопка
Условное форматирование на панели Стили ленты Главная.
Для быстрого нахождения ячейки с определенным параметром
необходимо нажать кнопку Условное форматирование и выбрать
пункт Правила выделения ячеек.
Создание имен ячеек и диапазонов
Имя можно присвоить диапазону ячеек, формуле, константе
или таблице. Использование формул позволяет упростить состав13
ление формул, снизить количество опечаток и неправильных ссылок. Создать имя диапазона можно через контекстное меню. Варианты создания имен и диапазонов представлены в табл.
Таблица
Объект
наименования
Пример
Имя
ЗарплатаЗа1Квартал
Присвоено диапазону
ячеек A10:A20
КонИмя НДС присвоено
станта
константе 0, 22
Имя
ЗатратыЗа1квартал
Форприсвоено формуле
мула
ВПР(A2;
$B$2:$E$25;5; ИСТИНА)
Имя МаксПродажи2015 присвоено таТаблиблице, которая создана
ца
через меню Вставка/
Таблицы/Таблицы
МасИмя ДиапазонА присив
своено диапазону
кончисел 2,3,4
стант
Диапазон
Формула без
использования
имени
=СУММ
(A10:A20)
Формула с использованием
имени
=СУММ
(ЗарплатаЗа1Квартал)
=ПРОИЗВЕД
(A1;0,22)
= ПРОИЗВЕД
(A1;НДС)
= СУММ
(ВПР(A2;
$B$2:
$E$25;5; ИСТИНА))
=СУММ (ЗатратыЗа1квартал)
=$C$10:
$L$56
=МаксПродажи2015
=СУММ
({2;3;4})
=СУММ(ДиапазонА)
Порядок выполнения работы
1. Открыть рабочую книгу, созданную при выполнении лабораторной работы №1.
2. Произвести расчеты в вычисляемых ячейках основной таблицы.
3. На отдельном листе электронной таблицы (Далее ЭТ) создать
новую таблицу, структура которой должна соответствовать выданному заданию.
4. Заполнить первые два поля таблицы, используя все уникальные значения, присутствующие в таблице учета.
5. В остальных полях произвести расчеты, соответствующие варианту задания. Формулы для расчета должны вводиться в ячейки
первой строки с данными. Заполнение остальных ячеек столбцов
выполнить с помощью команды копирования. Для успешного ко14
пирования обратить внимание на использование относительных и
абсолютных ссылок при составлении формул.
6. Ниже полученной таблицы произвести итоговые вычисления
(см. ВАРИАНТЫ ЗАДАНИЙ). Левее вычисляемых ячеек необходимо ввести краткое описание выполненного действия.
7. Использовать команды форматирования для установления
необходимого числового формата, выравнивания данных в ячейках, задания границ и фона.
8. Скопировать полученную таблицу на другой лист. Удалить
все расчетные данные. При построении формул, вместо ссылок на
ячейки, использовать имена диапазонов.
9. Выполнить условное форматирование таблиц (см. ВАРИАНТЫ ЗАДАНИЙ).
15
Лабораторная работа №3
ТЕХНОЛОГИЯ СОЗДАНИЯ РАСЧЕТНЫХ ДОКУМЕНТОВ
С ИСПОЛЬЗОВАНИЕМ СПРАВОЧНЫХ ТАБЛИЦ
Цель работы: Получение навыков работы с набором данных,
используемых в нескольких таблицах, как в качестве параметров
расчетов, так и для построения отчетных форм Создание и использование отдельных таблиц-справочников позволяющие ускорить
процесс разработки, повышающие надежность расчетов. Установка ограничений ввода.
Краткие теоретические сведения:
1 .Ссылки и массивы: ВПР, ГПР, ВЫБОР, ПОИСКПОЗ. Формулы просмотра и поиска значений.
Функция ВПР().
Функция ВПР() ищет значение в первом (в самом левом) столбце таблицы и возвращает значение из той же строки, но другого
(правого) столбца таблицы.
Синтаксис функции:
ВПР(искомое_значение;таблица;номер_столбца; интервальный_просмотр)
Аргумент искомое_значение задает параметры поиска. Результат поиска в таблице (соответствующем диапазоне ячеек) выведет
найденные данные из ячейки, которая находится правее ячейки,
в которой найдется заданный параметр. Если первый столбец не
содержит искомое_значение, то функция ВПР() вернет ошибку
#Н/Д.
Аргумент интервальный_просмотр может принимать 2 значения: ИСТИНА и ЛОЖЬ.
Интервальный_просмотр =ЛОЖЬ используется для создания
структур типа Справочник.
В этом случае в левом столбце таблицы имеется единственное искомое_значение.
Предположим, что в первом столбце имеются повторяющиеся значения. В этом случае будет выводиться значение, соответствующее только первому найденному (поиск производится сверху вниз). Данные, соответствующие последующим значениям, выведены не будут [7].
Интервальный_просмотр = ИСТИНА (приблизительное соответствие).
Функцию ВПР() с параметром интервальный_просмотр = ИСТИНА (приблизительное соответствие) можно использовать для
нахождения ближайшего числа к искомому_значению.
16
Рис. 4. Скриншот фрагмента таблицы со вставкой функции ВПР
Особенности работы функции ВПР():
– значения в первом столбце аргумента таблицы должны быть
предварительно отсортированы в возрастающем порядке, иначе
функция вернет неправильный результат;
– функция ищет наибольшее значение, которое меньше или равно, чем искомое_значение. Например, если в первом столбце таблицы введены значения 10; 20; 30; 40 и искомое_значение = 120, то
функция вернет 100.
– искомое_значение ищется в самом левом столбце. Поэтому
в некоторых случаях вместо функции ВПР() удобнее использовать
формулу на основе функций ИНДЕКС() и ПОИСКПОЗ().
На рис. 4 представлен скриншот фрагмента таблицы, в которой
для нахождения данных использована функция ВПР.
Функция ПОИСКПОЗ()
Функция ПОИСКПОЗ() возвращает позицию значения в диапазоне ячеек.
Например, если в ячейке А12 содержится значение "апельсин»,
то формула =ПОИСКПОЗ("апельсин";A9:A25;0) вернет 4, т. е.
искомое значение "апельсин" содержится в четвертой ячейке диапазона A9:A25: А9 – первая ячейка (предполагается, что в ней не
содержится значение "апельсин"), А10 - вторая, А11 - третья и т.д.
(подсчет позиции производится от верхней ячейки).
Функция ПОИСКПОЗ() возвращает позицию искомого значения, а не само значение.
Например:
ПОИСКПОЗ("2";{"1";"2";"3";"2"};0)
возвращает число 2 – относительную позицию буквы "2" в массиве
{"1";"2";"3";"2"}. Позиция второй цифры "2" будет проигнорирована, функция вернет позицию только первой цифры. [5]
Синтаксис функции
ПОИСКПОЗ(искомое_значение; просматриваемый_массив;
тип_сопоставления).
Искомое_значение – значение, используемое при поиске значения в просматриваемом_массиве. Искомое_значение может быть
значением (числом, текстом или логическим значением (ЛОЖЬ или
17
ИСТИНА)) или ссылкой на ячейку, содержащую число, текст или логическое значение.
Просматриваемый_массив – непрерывный диапазон ячеек,
возможно, содержащих искомые значения. Просматриваемый_
массив может быть только одностолбцовым диапазоном ячеек, например А9:А25 или диапазоном, расположенным в одной строке,
например, А5:D5. Таким образом, формула =ПОИСКПОЗ(«Пере
льман»;A10:B13;0) работать не будет (выдаст ошибку #Н/Д), так
как Просматриваемый_массив представляет собой диапазон ячеек, который размещен одновременно в нескольких столбцах и нескольких ячейках.
Тип_сопоставления – число –1, 0 или 1. 
– Если тип_сопоставления равен 0, то функция ПОИСКПОЗ()
находит первое значение, которое в точности равно аргументу искомое_значение. При этом Просматриваемый_массив может быть
не упорядочен.
– Если тип_сопоставления равен 1, то функция ПОИСКПОЗ()
находит наибольшее значение, которое меньше либо равно, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: ..., –2, –1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА. Если тип_сопоставления опущен, то предполагается, что он равен 1.
– Если тип_сопоставления равен –1, то функция ПОИСКПОЗ() находит наименьшее значение, которое больше либо
равно чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ...,
2, 1, 0, –1, –2, ..., и так далее.
Функция ПОИСКПОЗ() не различает РеГИстры при сопоставлении текстов.
Если функция ПОИСКПОЗ() не находит соответствующего
значения, то возвращается значение ошибки #Н/Д.
Функция ИНДЕКС()
Функция ИНДЕКС() возвращает значение из диапазона ячеек по номеру строки и столбца. Например, формула
=ИНДЕКС(A9:A25;2) вернет значение из ячейки А10, т. е. из
ячейки расположенной во второй строке диапазона.
Синтаксис функции
ИНДЕКС(массив; номер_строки; номер_столбца)
Массив – ссылка на диапазон ячеек.
Номер_строки – номер строки в массиве, из которой требуется
возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца»является обязательным.
18
Номер_столбца – номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен,
аргумент «номер_строки» является обязательным.
Если используются оба аргумента – и «номер_строки», и «номер_столбца», – то функция ИНДЕКС() возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.
Значения аргументов «номер_строки» и «номер_столбца»
должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС() возвращает значение ошибки
#ССЫЛКА! Например, формула =ИНДЕКС(A2:A10;32) вернет
ошибку, т.к. в диапазоне А2:А10 только 9 строк.
Функции ПОИСКПОЗ() и ИНДЕКС() часто используются
вместе, т.к. позволяют по найденной позиции в одном диапазоне
вывести соответствующее значение из другого диапазона (левый
ВПР()).[7]
Например. Формула =ВПР("яблоки";A35:B38;2;0) аналогична
формуле =ИНДЕКС(B35:B38;ПОИСКПОЗ("яблоки";A35:A38;0))
Функция ВЫБОР()
Функция ВЫБОР() возвращает значение из заданного списка
аргументов-значений в соответствии с заданном индексом. Например, формула =ВЫБОР(2;"апельсин";"мандарин";"лимон")
вернет значение “мандарин”. Здесь 2 – это значение индекса, а
"апельсин";"мандарин";"лимон" – это первый, второй и третий аргумент соответственно.
Синтаксис функции ВЫБОР()
ВЫБОР(номер_индекса ;значение1;значение2;…), где
Номер_индекса – номер выбираемого аргумента-значения. Номер_индекса должен быть числом от 1 до 254. Если номер_индекса
равен 1, то функция ВЫБОР() возвращает значение1; если он равен 2, возвращается значение2 и так далее;
Если номер_индекса меньше 1 или больше, чем номер последнего значения в списке, то функция ВЫБОР возвращает значение
ошибки #ЗНАЧ!;
Если номер_индекса представляет собой дробь, то он усекается
до меньшего целого;
Сами значения – значение1;значение2; ... могут быть числами,
текстовыми строками и ссылками на диапазоны ячеек. Если в качестве значений введены конкретные значения, то функция возвращает одно из этих значений в зависимости от индекса, а если
в качестве значений введены ссылки на ячейки, то функция возвращает соответственно ссылки.
19
Функция ГПР()
Буква Г в аббревиатуре «ГПР» означает «горизонтальный» ВПР.
Выполняет поиск значения в первой строке таблицы или массива
значений и возвращает значение, находящееся в том же столбце в заданной строке таблицы или массива. Функция ГПР() используется,
когда сравниваемые значения расположены в первой строке таблицы данных, а возвращаемые – на несколько строк ниже.
Синтаксис
ГПР(искомое_значение, таблица, номер_строки, [интервальный_просмотр])
Аргументы функции ГПР():
Искомое_значение – обязательный аргумент. Значение, которое
требуется найти в первой строке таблицы. «Искомое_значение»
может быть значением, ссылкой или текстовой строкой.
Таблица – обязательный аргумент. Таблица, в которой производится поиск данных. Можно использовать ссылку на диапазон или
имя диапазона.
Значения в первой строке аргумента «таблица» могут быть текстом, числами или логическими значениями.
Если аргумент «интервальный_просмотр» имеет значение ИСТИНА, то значения в первой строке аргумента «таблица» должны
быть расположены в возрастающем порядке: ...–2, –1, 0, 1, 2, ..., A-Z,
ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если аргумент «интервальный_просмотр»
имеет значение ЛОЖЬ, таблица может быть не отсортирована.
Особенности функции ГПР().
– В текстовых строках регистр букв не учитывается.
– Значения сортируются по возрастанию, слева направо.
Номер_строки – обязательный аргумент. Номер строки в массиве «таблица», из которой будет возвращено соответствующее
значение. Если значение аргумента «номер_строки» равно 1, возвращается значение из первой строки аргумента «таблица», если
оно равно 2 – из второй строки и т. д. Если значение аргумента «номер_строки» меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе «таблица», возвращается значение ошибки #ССЫЛ!.[5]
2. Проверка свойств и значений: ЕОШИБКА, ЕПУСТО, ЕЧИСЛО,
ЕТЕКСТ.
Функция ЕОШИБКА()
Функция ЕОШИБКА() проверяет на равенство значениям #Н/Д,
#ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! и возвращает
в зависимости от этого ИСТИНА или ЛОЖЬ. [5]
20
Синтаксис функции ЕОШИБКА()
ЕОШИБКА(значение)
Значение – ссылка на ячейку или результат вычисления выражения, которое необходимо проверить.
Для обработки ошибок #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,
#ЧИСЛО!, #ИМЯ? или #ПУСТО! используют формулы следующего
вида:
=ЕСЛИ(ЕОШИБКА(A2);”ОШИБКА!”;A2);
=ЕСЛИ(ЕОШИБКА(A4/A5);»ОШИБКА!»;A4/A5).
В случае наличия в ячейке А2 ошибки или ошибки при вычислении выражения A4/A5, формулой выводится слово ОШИБКА!.В противном случае – содержимое ячейки A2 или, соответственно, результат вычисления выражения A4/A5.
Функция ЕПУСТО()
Задача функции ЕПУСТО() – проверять есть ли в ячейке число, текстовое значение, формула или нет. Если в ячейке А1 имеется
значение 41, то формула =ЕПУСТО(А1) вернет ЛОЖЬ, а если ячейка А1 пуста, то ИСТИНА.
Синтаксис функции ЕПУСТО()
ЕПУСТО(значение)
Значение – значением может быть все что угодно: текст, число,
ссылка, имя, значение ошибки, логическое выражение.
Функция ЕЧИСЛО()
Функция ЕЧИСЛО() – проверяет являются ли значения
числами или нет. Формула =ЕЧИСЛО(3) вернет ИСТИНА, а
=ЕЧИСЛО(«Информатика») вернет ЛОЖЬ.
Синтаксис функции ЕЧИСЛО()
ЕЧИСЛО(значение)
Значение – значением может быть все что угодно: текст, число,
ссылка, имя значение ошибки, логическое выражение.
Функция ЕТЕКСТ()
Функции ЕТЕКСТ() – проверяет является ли содержимое ячейки текстовым значением или нет. Например, Формула
=ЕТЕКСТ(5) вернет ЛОЖЬ, а =ЕТЕКСТ("Привет!") вернет ИСТИНА.
Синтаксис функции ЕТЕКСТ()
ЕТЕКСТ(значение)
Значение – значением может быть все что угодно: текст, число,
ссылка, имя значение ошибки, логическое выражение.
21
3.Функции категории «Дата и время»: ВРЕМЯ, ГОД, ДАТА,
ДЕНЬ, ДЕНЬНЕД, МЕСЯЦ, МИНУТЫ, СЕГОДНЯ, СЕКУНДЫ,
ТДАТА, ЧАС.
Рассмотрим некоторые из них.
Функция ВРЕМЯ()
Функция ВРЕМЯ() возвращает число, представляющее определенное время. Например, записав формулу =ВРЕМЯ(10;30;0),
получим число 0,4375 соответствующее 10:30 утра. EXCEL хранит
значения времени в числовом формате. Числа принадлежат интервалу от 0 (0:00:00) до 0,99999 (23:59:59). Если формат ячейки, содержащей число 0,5 перевести в формат Время, то 0,5 будет преобразовано в 12:00:00 утра. [7]
Синтаксис функции
ВРЕМЯ(часы;минуты;секунды)
Часы – число от 0 до 32767, задающее часы. Если значение
больше 23, оно делится на 24; остаток от деления будет соответствовать значению часов. Например, ВРЕМЯ(27;0;0) = ВРЕМЯ(3;0;0) =0,125, то есть 03:00 утра.
Минуты – число от 0 до 32767, задающее минуты. Если значение больше 59, оно будет пересчитано в часы и минуты. Например,
ВРЕМЯ(0;750;0) = ВРЕМЯ(12;30;0) = 0,520833, то есть 12:30.
Секунды – число от 0 до 32767, задающее секунды. Если значение больше 59, оно будет пересчитано в часы, минуты и секунды.
Например, ВРЕМЯ(0;0;2000) = ВРЕМЯ(0;33;22) = 0,023148, то
есть 12:33:20.
Функция ГОД()
Функция ГОД() возвращает год, соответствующий заданной дате. Год определяется как целое число в диапазоне от 1900 до 9999.
Синтаксис функции
ГОД(дата)
Дата – дата, год которой необходимо найти.
Примеры.
Используемая в качестве аргумента Дата должна быть в формате воспринимаемым EXCEL.
Например,=ГОД(«01.05.2015»); =ГОД(«01/05/2015»). В EXCEL
даты хранятся в виде последовательности чисел (1, 2, 3, ...), что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 г. имеет номер 1.
Пример использования:=ГОД(A1) Если в ячейке А1 введена дата в одном из вышеуказанных форматов, то формулой будет возвращен год (2015).
22
Совместное использование с функцией ДАТА()
Для того, чтобы прибавить к дате 01.05.2015, например, 7 лет,
можно использовать следующую формулу:
=ДАТА(ГОД(A1)+7;МЕСЯЦ(A1);ДЕНЬ(A1))
Функция ДАТА()
Функция ДАТА() возвращает целое число, представляющее
определенную дату. Формула =ДАТА(2011;02;28) вернет число 40602. Если до ввода этой формулы формат ячейки был задан как Общий, то результат будет отформатирован как дата, т. е.
28.02.2011.
Синтаксис функции
ДАТА(год;месяц;день)
Год – аргумент, который может иметь от одной до четырех цифр.
Месяц – положительное или отрицательное целое число в диапазоне от 1 (январь) до 12 (декабрь), представляющее месяц года.
День – положительное или отрицательное целое число в диапазоне от 1 до 31, представляющее день месяца.
Функция СЕГОДНЯ()
Задача функции СЕГОДНЯ() – вернуть текущую дату. Записав,
формулу =СЕГОДНЯ() получим 01.09.2016 (если конечно сегодня
этот день).
Функция СЕГОДНЯ() возвращает текущую дату в числовом
формате
Т. е. формат ячейки, содержащей эту формулу должен быть
Общий или Дата, иначе получим число 40684 вместо даты
21.05.2011.
Синтаксис функции СЕГОДНЯ()
У функции СЕГОДНЯ() нет аргументов.
1. Функция СЕГОДНЯ() полезна, если на листе требуется отображать текущую дату независимо от времени открытия книги. Записав в ячейке А1 формулу =СЕГОДНЯ() при каждом открытии
книги будем видеть текущую дату.
2. Функция используется для вычисления интервалов. Например, если известно, что проект заканчивается 31/12/2017 (предполагается, что срок еще не наступил), то узнать сколько дней осталось до конца проекта можно с помощью следующей формулы =ДАТАЗНАЧ(«31/12/2020»)–СЕГОДНЯ().
Функция ДЕНЬНЕД()
Функция ДЕНЬНЕД() возвращает день недели, соответствующий дате. По умолчанию день недели определяется как целое число в интервале от 1 (воскресенье) до 7 (суббота).
23
Синтаксис функции
ДЕНЬНЕД(дата;[тип])
Дата – дата, день недели который требуется найти. Даты должны вводиться в числовом формате, или как результат вычисления
других формул или с использованием функции ДАТА(). Например,
для указания даты 01.05.2015 следует воспользоваться выражением =ДАТА(2015;5;01).
Тип – число 1, 2 или 3. Если аргумент тип равен 1 или опущен,
то Воскресенье считается первым днем недели, Понедельник – вторым
и т.д. Если аргумент тип равен 2, то Понедельник считается первым
днем недели, Вторник – вторым и т.д. (так принято в РФ). Если аргумент тип равен 3, то Понедельнику соответствует 0, Вторнику – 1 и т.д.
Функция СЕКУНДЫ
Возвращает секунды, соответствующие заданному значению
времени. Секунды определяются как целое число в интервале от
0 до 59.
Синтаксис функции
СЕКУНДЫ (время_в_числовом_формате)
время_в_числовом_формате – время, для которого требуется
определить значение секунд. Время может быть задано текстовой
строкой в кавычках (например, "18:45"), десятичным числом (например, значение 0,78125 соответствует 18:45) или являться результатом вычисления других формул или функций (например,
=ВРЕМЗНАЧ("18:45")).
4. Текстовые функции: СЦЕПИТЬ, ЛЕВСИМВ,
Функция СЦЕПИТЬ ()
Функция СЦЕПИТЬ()объединяет до 255 текстовых строк в одну. Объединяемые элементы могут быть текстом, числами, ссылками на ячейки или сочетанием этих элементов. Например, если
в ячейке A1 листа содержится имя человека, а в ячейке B1 – его
фамилия, объединить эти два значения в третей ячейке можно с помощью следующей формулы:=СЦЕПИТЬ(A1;" ";B1).
Второй аргумент в этом примере ("") – символ пробела. Все пробелы и знаки пунктуации, которые должны быть включены в результат, необходимо указывать в качестве аргумента, заключенного в кавычки.[6]
Синтаксис функции
СЦЕПИТЬ(текст1;[текст2];…)
Текст1 – обязательный аргумент. Первый текстовый элемент,
подлежащий сцеплению.
Текст2, ...– необязательные аргументы. Дополнительные текстовые элементы (до 255 штук).
24
Рис. 5. Скриншот таблицы, в которой использована
функция СЦЕПИТЬ.
Для объединения текстовых элементов вместо функции СЦЕПИТЬ можно также использовать оператор & (амперсанд). Например, формула =A1 & B1 возвращает то же значение, что и формула
=СЦЕПИТЬ(A1;B1).
На рис.5 показан скриншот таблицы, в которой в ячейках столбца Д объединены соответствующие ячейки из столбцов A, B и C.
Функция ЛЕВСИМВ()
Функция ЛЕВСИМВ() возвращает первые (левые) знаки текстовой строки исходя из заданного количества знаков. Формула
ЛЕВСИМВ("Мария Викторовна";5) вернет значение «Мария».
Синтаксис функции
ЛЕВСИМВ(Исходный_текст;количество_знаков)
Исходный_текст – текстовая строка или ссылка на ячейку
с текстовой строкой, содержащей извлекаемые знаки.
Количество_знаков – количество знаков, извлекаемых функцией ЛЕВСИМВ() с начала строки Исходный_текст.
Порядок выполнения работы
1. Открыть рабочую книгу, созданную при выполнении лабораторной работы №2.
2. Разместить справочные таблицы на отдельных листах, присваивая им имена, соответствующие содержанию находящихся
в них данных. Таблицы должны иметь структуры, соответствующие выданному варианту задания.
3. Заполнить таблицу реальным (правдоподобным) непротиворечивым содержимым, состоящим не менее чем из 20 записей.
4. Диапазонам таблицы, ячейки которых содержат справочные
данные, присвоить имена. Имена диапазонов использовать при построении формул.
5. В основной таблице и таблице отчета создать выпадающие
списки, выбирающие данные из справочных таблиц.
6. Значения, зависящие от поля с выпадающим списком должны автоматически обновляться. Формулы для расчета должны вво25
диться в клетки первой строки с данными. Заполнение остальных
клеток колонок выполнить с помощью команды копирования.
7. Формулы должны содержать проверку на ошибочное значение
#Н/Д, которое могут возвращать функции ВПР, ГПР, ПОИСКПОЗ.
8. Использовать команды форматирования для установления
необходимого числового формата, выравнивания данных в ячейках, задания границ и фона.
9. Установить ограничение ввода значений на поля (Тип данных: Целое число, список, дата, длина текста). Для каждого из проверяемых значений выводить сообщение для ввода и сообщение об
ошибке.
26
Лабораторная работа № 4
ТЕХНОЛОГИЯ ПОСТРОЕНИЯ ГРАФИКОВ И ДИАГРАММ
Цель работы: Приобретение практических навыков построения
графиков и диаграмм для визуального анализа табличных данных.
Закрепление навыков работы с формулами.
Краткие теоретические сведения
В программе Excel термин «диаграмма» используется для обозначения всех видов графического представления числовых данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах
отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.
Диаграмма представляет собой вставной объект, внедренный на
один из листов рабочей книги. Она может располагаться на том же
листе, на котором находятся данные, или на любом другом листе (часто для отображения диаграммы отводят отдельный лист). Диаграмма сохраняет связь с данными, на основе которых она построена, и
при обновлении этих данных немедленно изменяет свой вид.
Для построения диаграммы обычно используют окно Вставка
диаграммы, запускаемое командой Вставка → Диаграмма → Создать диаграмму. Элементы управления для создания отдельных видов диаграммы доступны в группе Вставка → Диаграмма.
Возможно заранее выделить область, содержащую данные, которые будут отображаться на диаграмме, или задать эту же информацию после вставки диаграммы командой группы Работа с диаграммами Конструктор → Данные → Выбрать данные.
При настройке оформления диаграммы можно задать:
– название диаграммы, подписи осей (вкладка Формат → Макет →
Подписи);
– отображение и маркировка осей координат (вкладка Формат →
Макет → Оси → Оси);
– отображение сетки линий, параллельных осям координат
(вкладка Макет → Оси → Линии сетки);
– описание построенных графиков (вкладка Формат → Макет →
Подписи → Легенда);
– отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Формат → Макет → Подписи →
Подписи данных);
– представление данных, использованных при построении графика, в виде таблицы (вкладка Формат → Макет → Подписи →Таблица
данных).
27
Порядок выполнения работы
1. Открыть рабочую книгу, созданную при выполнении лабораторной работы №3.
2. Предложить свои варианты диаграмм (гистограмма, круговая,
график) и структуры таблиц, и согласовать их с преподавателем.
3. На отдельном листе провести обработку данных журнала учета, с целью получения таблиц с исходными данными для построения диаграмм. Построить и проанализировать графические отчеты
по полученным результатам.
4. Полученные диаграммы должны содержать Заголовок.
5. Необходимо указывать названия осей, если откладываемые
по ним значения требуют комментария. Например, необходимо
указать единицы измерения и/или название анализируемого показателя.
6. Следует размещать на диаграммах легенды, если на них отображаются несколько рядов (серий) значений. Использовать для идентификации рядов значений содержательные, но короткие названия.
7. Использовать режим вывода «подписей данных» при необходимости уточнения отображаемых значений.
8. Использовать команды изменения формата области диаграммы и ее элементов (цвет фона, осей, линий, подписей; размер
шрифта и т. д.).
28
Лабораторная работа №5
ОРГАНИЗАЦИЯ И РАБОТА СО СПИСКОМ (БАЗОЙ ДАННЫХ)
Цель работы: Приобретение практических навыков работы со
списком Microsoft Excel как с однотабличной базой данных.
Теоретические сведения по основным функциям категории
«Работа с базой данных».
Табличный процессор MS Excel имеет достаточно простые и
удобные средства для создания таблиц, во многом похожих на таблицы баз данных СУБД и средства для работы с этими таблицами.
Это позволяет получить начальные представления о базах данных,
как об основных объектах информационно-поисковых систем, и
получить практические навыки создания и работы с ними. [3]
Базы данных в терминологии MS Excel называют “списками”.
Список – это обычная многоколоночная таблица MS Excel, каждая
строка которой представляет собой запись сведений о некотором информационном объекте, включённом в этот список (таблицу). Каждая запись состоит из полей – клеток таблицы, в которых содержатся данные – значения определённых свойств объекта (в виде чисел,
дат, периодов времени и т.п.). Названия свойств (характеристик,
атрибутов) объекта, указываются в колонках шапки таблицы. Каждую такую таблицу, далее будем использовать термин таблица база
данных (ТБД), удобно размещать на отдельном листе MS Excel.
Исходная таблица должна быть преобразована в базу данных
с соблюдением следующих условий:
– исходная таблица имеет заголовки столбцов;
– критерии оформлены в виде небольшой таблицы с заголовками;
– заголовки таблицы критериев совпадают с заголовками исходной таблицы (если критерий не задается формулой).
Для работы с хранящейся в сформированной базе данных информацией используют специальные функции.
Функция БДСУММ()
Функция БДСУММ() суммирует числа в таблице данных, которые удовлетворяют заданным условиям. Эту функцию имеет смысл
использовать, когда необходимо просуммировать значения с учетом нескольких условий.
Синтаксис функции БДСУММ()
БДСУММ(база_данных;поле;условия).
База_данных представляет собой диапазон ячеек с данными
связанными логически.
29
Поле – Заголовок столбца, по которому производится суммирование (т. е. столбец с числами).
Условия – интервал ячеек, который содержит задаваемые условия (т. е. таблица критериев).
Функция БСЧЁТ()
Функция БСЧЁТ() подсчитывает строки в таблице данных, которые удовлетворяют заданным критериев с подстановочными знаками или критериев на основе формул.
Альтернативными функциями БСЧЁТ() могут быть функции
СУММПРОИЗВ(), СЧЁТЕСЛИМН().
Синтаксис функции БСЧЁТ()
БСЧЁТ(база_данных;поле;условия)
База_данных представляет собой диапазон ячеек с данными
связанными логически.
Поле – Заголовок столбца, по которому производится подсчет.
Условия – интервал ячеек, который содержит задаваемые условия (т. е. таблица критериев).
Для функции БСЧЁТ() значения могут быть только числовыми, а для функции БСЧЁТА() могут быть и числовыми и текстовыми. Поэтому чаще используют последнюю функцию [4].
Функция ДМИН()
Функция ДМИН() находит в столбце минимальное значение,
удовлетворяющее заданным условиям.
Синтаксис функции ДМИН().
ДМИН(база_данных;поле;условия)
База_данных представляет собой диапазон ячеек с данными
связанными логически.
Поле – заголовок столбца, в котором ищется минимальное значение, если выполняется условие.
Условия – интервал ячеек, который содержит задаваемые условия (таблица критериев).
Функция ДМАКС()
Функция ДМАКС() находит в столбце максимальное значение,
удовлетворяющее заданным условиям.
Синтаксис функции ДМАКС()
ДМАКС(база_данных;поле;условия)
База_данных представляет собой диапазон ячеек с данными
связанными логически.
Поле – заголовок столбца, в котором ищется максимальное значение, если выполняется условие. Существует несколько вариантов заполнения аргумента
30
Рис. 6. Скриншот примера выполнения задания  
к лабораторной работе № 5
Условия – интервал ячеек, который содержит задаваемые условия (т. е. таблица критериев).
Функция ДСРЗНАЧ()
Функция ДСРЗНАЧ() вычисляет среднее значение в столбце,
но только среди значений удовлетворяющих заданным условиям.
Синтаксис функции ДСРЗНАЧ() и ее использование аналогичны функции БДСУММ().
На рис.6 показан фрагмент выполненного задания по данной лабораторной работе с использованием вышеперечисленных
функций.
Порядок выполнения работы
1. Открыть рабочую книгу, созданную при выполнении лабораторной работы №4.
2. Проверить таблицу журнала учета на соответствие требованиям оформления списка.
При необходимости привести к соответствующему виду.
3. Диапазону с данными, определяющему БД, желательно присвоить имя.
4. Выполнить сортировку данных в списке.
– По двум полям различного типа.
– Определить пользовательский порядок сортировки для одного из полей для чего необходимо создать новый пользовательский
список автозаполнения.
31
5. Сформулировать и реализовать пять запросов на поиск и отбор информации из созданного списка.
– Два из них (простые) реализовать с помощью автофильтра. Необходимо задать критерий из одного или двух условий, используя
пользовательский автофильтр.
– Три оставшиеся (более сложные) – используя расширенный
фильтр. Созданные интервалы критериев должны содержать не менее трех условий, относящихся, как минимум, к двум различным
полям. Среди критериев должны присутствовать вычисляемые поля, в том числе содержащие текстовые функции и функции даты и
времени.
6. Сформулировать и реализовать не менее двух задач расчетного характера, используя функции для анализа списков.
7. Реализовать задачи пп.6 с использованием автоматического
подведения итогов.
32
Лабораторная работа №6
КОНСОЛИДАЦИЯ ДАННЫХ. СОЗДАНИЕ СВОДНЫХ ТАБЛИЦ
Цель работы: Получение навыков вычислений и подведения итогов в MS Excel для различных диапазонов с помощью технологий построения сводных таблиц и выполнения процедур консолидации.
Краткие теоретические сведения
Консолидация – это объединение или накопление информации
из двух или более рабочих листов, которые могут находиться в разных рабочих книгах [5].
Для консолидации такой информации эффективнее всего воспользоваться диалоговым окном Консолидация, которое открывается после выбора команды Данные → Работа с данными → Консолидация. С помощью данной возможности можно выполнять статическую (без формул связи) или динамическую (с формулами связи)
консолидацию. Средство Консолидация поддерживает два метода
консолидации:
– По позициям. Этот метод применим только в том случае, если
способы размещения информации на исходных листах идентичны.
– По категории. В Excel существует возможность подбора данных с исходных рабочих листов по заголовкам и столбцам. Этим
методом удобно воспользоваться, если данные на исходных рабочих листах размещены по-разному или на некоторых исходных рабочих листах разное количество строк или столбцов.
Сводная таблица.
Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах,
в различных разрезах для создания комбинированных отчетов.
Требования к исходной таблице:
1. каждый столбец должен иметь заголовок;
2. в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» –
названия компаний только в текстовом формате);
3. в таблице должны отсутствовать незаполненные строки и
столбцы;
4. в ячейки должны вводиться «атомарные» значения, т. е.
только те, которые нельзя разнести в разные столбцы. Например,
нельзя в одну ячейку вводить адрес в формате: «Город, Название
улицы, дом №». Нужно создать 3 одноименных столбца, иначе
Сводная таблица будет работать неэффективно.
33
Для создания Сводной таблицы достаточно выделить любую
ячейку исходной таблицы и выбрать Вставка → Сводная таблица.
На экране отобразится макет будущей сводной таблицы. Пользователь может выбрать источник данных и место вывода сводной
таблицы. Для редактирования переносят необходимые поля в нижнюю часть окна Списка полей сводной таблицы, в которой представлены области:
– Названия строк;
– Названия столбцов;
– Значения (в этой области происходят вычисления);
– Фильтр отчета.
Для пересчета сводных таблиц при изменении данных в исходном списке необходимо щелкнуть по любой ячейке сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду
Обновить.
При возникновении вопросов о том, какие данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном
значении в Сводной таблице, чтобы автоматически был создан отдельный лист с отобранными из исходной таблицей строками.
Порядок выполнения работы
1. Открыть рабочую книгу, созданную при выполнении лабораторной работы №5.
2. На двух листах рабочей книги разместить отчеты по продаже/оказании товаров/услуг за три месяца различными филиалами
фирмы. Структура таблиц должна быть одинаковой. Значения для
одного из отчетов получить, ссылаясь на данные журнала учета, а
для другого ввести произвольно.
3. Создать новую рабочую книгу и скопировать один из созданных отчетов и изменить структуру созданной таблицы (добавить
новые товары/услуги).
4. Выполнить консолидации по расположению и категории для
полученных отчетов.
5. Построить полученные ранее отчеты использую технологию
построения сводных таблиц.
6. По сводным таблицам построить диаграммы.
34
Лабораторная работа №7
ТЕХНОЛОГИЯ СОЗДАНИЯ МАКРОСОВ.
ЗАЩИТА ИНФОРМАЦИИ.
Цель работы: Приобретение навыков автоматизации процесса
выполнения задач, требующих частого обращении. Защита информации.
Краткие теоретические сведения
Для автоматизации часто выполняемых в Microsoft Excel задач
можно записать макрос. Макрос представляет собой действие (или
набор действий), которое можно выполнять любое количество раз
[8]. Например: быструю вставку группы ячеек, применение сложного форматирования, выполнение операций над текстом, печать
данных в определенном формате, анализ выделенного диапазона
ячеек и т.д.
Макросы записываются на полноценном языке программирования, называемом VBA (Visual Basic for Applications). Для создания
простого макроса используют специальный инструмент Excel, называемый макрорекордером.
В Excel каждый макрос привязывается к книге и сохраняется
в XLSM-файле этой книги. Как только пользователь открывает книгу, которая содержит какие-либо макросы, Excel делает эти макросы
доступными любой другой открытой в настоящий момент книге.
Запись макроса
Для создания макроса выполните следующие действия:
1. Отобразите вкладку Разработчик на ленте. Для этого:
a) Откройте вкладку Файл, выберите команду Параметры, а затем – категорию Настройка ленты.
b) В разделе Настройка ленты в списке Основные вкладки выберите пункт Разработчик и нажмите кнопку ОК. На ленте появится
вкладка Разработчик с группой Код. На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.
3. В окне диалога Запись макроса выполните следующие действия:
a) В поле Имя макроса ведите описательное имя для вашего макроса.
Имя макроса может включать буквы, числа и символ подчеркивания, но не может содержать пробелы или другие специальные
символы. В разделе Сочетание клавиш укажите букву, которая в сочетании с клавишей [Ctrl] будет использоваться для запуска макроса
на выполнение.
35
В выпадающем списке Сохранить в выберите место сохранения
макроса: Личная книга макросов, Новая книга или Эта книга.
d) В поле Описание введите описание макроса, чтобы помочь себе впоследствии идентифицировать макрос.
e) Нажмите клавишу ОК, чтобы начать запись макроса.
В этот момент начинается запись макроса. В строке состояния
отображается кнопка Остановить запись.
5. Последовательно выполните все действия, которые должны
быть записаны Excel в качестве макроса. Макрорекордер записывает все выполняемые вами действия, от редактирования и форматирования листа, до вызова команд ленты.
6. На вкладке Разработчик в группе Код нажмите кнопку Остановить запись (или щелкните по голубому квадрату в строке состояния).
Созданный макрос будет сохранен в указанном при его записи
расположении.
Чтобы просмотреть список доступных макросов на вкладке Разработчик в группе Код нажмите кнопку Макрос. Откроется окно диалога Макрос. Активация макроса произойдет, если в окне диалога
Макрос нажать кнопку Выполнить.
Для удаления макроса выделите его в окне диалога Макрос и нажмите кнопку Удалить.
Защита документа
В Microsoft Excel предусмотрено несколько уровней защиты, позволяющих управлять доступом к документам [7].
– Пометить как окончательный. Позволяет предотвратить внесение в документ случайных изменений рецензентами или читателями.
– Зашифровать паролем. Ограничить всем доступ к документу,
предоставив его просмотр и редактирование только «доверенным»
пользователям.
– Защитить текущий лист. Это позволяет включить защиту паролем, чтобы разрешить или запретить пользователям выделять,
форматировать, вставлять, удалять, сортировать и редактировать
области таблицы.
– Защитить структуру книги. Блокирует структуру книги, чтобы пользователи не могли добавлять или удалять листы или отображать скрытые листы. Позволяет запретить пользователям изменять размер или положение окон листа. Защита структуры и
окна книги распространяется на всю книгу.
– Добавление цифровой подписи. Цифровые подписи используются для проверки подлинности цифровых данных, например доку36
ментов, сообщений электронной почты и макросов, с помощью криптографии. Они создаются путем ввода или на основе изображения и
позволяют обеспечить подлинность и целостность. документа [2].
Все уровни защиты являются не взаимоисключающими,а взаимодополняющими друг друга.
Рассмотрим подробнее вариант создания Защиты книги от изменений.
Защита элементов листа
1. Выберите лист, который нужно защитить.
2. Чтобы разблокировать все ячейки или диапазоны, которые
должны быть доступны другим пользователям для изменения, выполните указанные ниже действия:
а) Выберите ячейки или диапазоны, которые нужно разблокировать.
б) На вкладке Главная в группе Ячейки нажмите кнопку Формат,
а затем выберите команду Формат ячеек.
в) На вкладке Защита снимите флажок Защищаемая ячейка, а
затем нажмите кнопку ОК.
3. Чтобы скрыть все формулы, которые не должны отображаться, выполните указанные ниже действия:
а) Выделите ячейки, содержащие формулы, которые необходимо скрыть.
б) На вкладке Главная в группе Ячейки нажмите кнопку Формат,
а затем выберите команду Формат ячеек.
в) На вкладке Защита установите флажок Скрыть формулы, а затем нажмите кнопку OK.
4. Для защиты листа выполните одно из следующих действий:
– На вкладке Файл выберите команду Сведения, нажмите кнопку Защитить книгу и выберите команду Защитить текущий лист.
– На вкладке Рецензирование в группе Изменения нажмите
кнопку Защитить лист.
6. В окне диалога Защита листа в списке Разрешить всем пользователям этого листа отметьте флажками элементы, изменение которых должно быть доступно пользователям.
7. В поле Пароль для отключения защиты листа введите пароль
для защиты листа, нажмите кнопку ОК, а затем еще раз введите пароль для его подтверждения.
Порядок выполнения работы
1. Открыть рабочую книгу, созданную при выполнении лабораторной работы №6.
2. Автоматизировать процесс создания запросов, реализованных в лабораторной работе №5. Создать пользовательские кнопки,
37
задать на них надписи, соответствующие запросам и назначить на
кнопки соответствующие макросы
3. Создать макрос для автоматического заполнения бланка документа, созданного в лабораторной работе №1. Создать кнопку на
панели инструментов и назначить макрос.
4. При создании выше указанных макросов внимательно изучить VBA код, полученный при записи макроса автоматически.
5. На листе бланка документа защитить лист от изменений,
оставив активными лишь поля, в которые возможно вводить информацию для заполнения бланка.
6. Защитить все листы от изменения формул.
7. Скрыть столбцы, содержащие критерии для выполнения запросов.
38
Список используемой литературы и электронных источников:
1. Дж. Уокенбах. Microsoft Excel 2010. Библия пользователя.:
Пер. с англ. М.: ООО “И.Д.Вильямс”, 2014. 
2. Павлов Н. В. Excel – готовые решения. М.: Книга по Требованию, 2014. 
3. Дж.Демарко. Excel для профессионалов. : Пер. с англ. М.:
АСТ НТ Пресс, 2014.
4. Златопольский Д. 1700 заданий по Microsoft Excel. БХВПетербург, 2014.
5. Симонович С.В. Информатика. Базовый курс: Учебник для
вузов. 3-е изд. Стандарт третьего поколения. СПб.: Питер, 2015.
6. http://support.office.microsoft.com
(дата
посещения
22.03.2015).
7. http://excel2.ru (дата посещения 22.03.2015).
8. http://www.planetaexcel.ru (дата посещения 22.03.2015).
39
ПРИЛОЖЕНИЕ
Варианты заданий к лабораторной работе №1
ТЕХНОЛОГИЯ СОЗДАНИЯ
И ФОРМАТИРОВАНИЯ
ЭЛЕКТРОННОЙ ТАБЛИЦЫ
1 вариант
Журнал учета выдачи книг (Код книги, Название, Автор, Код
читателя, ФИО, Дата выдачи, Дата возврата, Цена проката, Скидка (0 или 10%), Сумма проката).
2 вариант
Журнал учета выполнения рейсов (Код маршрута, Код водителя, ФИО, Дата отправки, Кол-во дней в пути, Дата возвращения,
Оплата за день, Премия, Сумма оплаты).
3 вариант
Журнал учета выполненных работ (Код сотрудника, ФИО, Код
вида работы, Описание работы, Дата начала, Дата окончания,
Оплата за день, Сумма оплаты).
4 вариант
Журнал учета нагрузки преподавателя (Код преподавателя,
ФИО, Номер группы, Специальность, Количество часов, Предмет,
Тип занятия (лекция, практика), Дата начала курса, Дата окончания, Оплата за день, Сумма оплаты).
5 вариант
Журнал учета выдачи автомобилей (Код автомобиля, Код клиента, ФИО, Дата выдачи, Дата возврата, Стоимость проката / в день,
Сумма оплаты).
6 вариант
Журнал учета продаж товаров (Код клиента, ФИО, Код товара,
Название, Количество, Дата продажи, Цена, Стоимость).
7 вариант
Журнал учета обращений к врачу (Код пациента, ФИО пациента, Код врача, Специализация врача, Диагноз, Дата обращения,
Оплата за прием, Стоимость лечения, Сумма к оплате)
8 вариант
Журнал учета полученных заказов (Код изделия, Вид изделия,
Цена изделия, Код ткани, Тип ткани, Цена ткани, Дата заказа,
Факт выполнения, Сумма к оплате)
40
9 вариант
Журнал учета телефонных переговоров (Код абонента, ФИО абонента, Код города, Город, Дата, Количество минут, Время суток,
Цена за минуту, Сумма к оплате).
10 вариант
Журнал учета проданных путевок (Код маршрута, Страна, Стоимость маршрута, Код клиента, ФИО, Дата отправления, Количество путевок, Скидка, Сумма к оплате).
11 вариант
Журнал учета заселения номеров (Код клиента, ФИО, Номер,
Цена/день, Дата поселения, Дата выбытия, Сумма к оплате).
12 вариант
Журнал учета продаж продукции (Код товара, Название, Код
клиента, ФИО, Дата продажи, Дата доставки, Количество, Цена,
Стоимость).
13 вариант
Журнал учета выданных фильмов (Код фильма, Название, Код
клиента, ФИО, Дата выдачи, Дата возврата, Стоимость проката/
день, Сумма к оплате).
14 вариант
Журнал учета поступивших заказов в химчистку (Код вида услуги, Название, Код клиента, ФИО, Дата приема, Дата возврата,
Цена услуги, Скидка, Стоимость).
15 вариант
Журнал учета посещений ветеринара (Код специалиста, ФИО,
Код животного, Вид животного, Диагноз, Дата посещения, Стоимость посещения, Стоимость лечения, Сумма к оплате)
16 вариант
Журнал учета продажи ювелирных изделий (Код изделия, Название, Вес, Цена за работу, Код материала, Название (платина, золото, серебро), Цена за грамм Дата продажи, Стоимость).
17 вариант
Журнал учета поставок деталей (Код детали, Название, Цена,
Код поставщика, Название организации, Количество, Дата покупки, Скидка (5% и 10%, нет), Стоимость).
18 вариант
Журнал учета оказанных парикмахерских услуг (Код клиента,
ФИО, Код стрижки, Название стрижки, Дата, Цена, Скидка, Стоимость)
19 вариант
Журнал учета сделок совершенных нотариальной конторой
(Код клиента, Название фирмы, Код услуги, Название (договоры
41
купли-продажи долей, договоры купли-продажи квартир, Удостоверение договоров отчуждения доли в Уставном капитале Обществ
и т.д.) Сумма, Комиссионные, Дата Стоимость к оплате).
20 вариант
Журнал учета подписанных договоров (Дата заключения, Код
филиала, Наименование филиала, Страховая сумма, Тарифная
ставка / %, Код вида страхования, Наименование (страхование автотранспорта, страхование домашнего имущества, добровольное
медицинское страхование), Стоимость).
Варианты заданий к лабораторной работе №2
ТЕХНОЛОГИЯ ВЫЧИСЛЕНИЙ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ
1 вариант
Отчет по читателям (Код читателя, ФИО читателя, Кол-во выданных книг, Сумма затрат, Карта постоянного посетителя)
Осуществить следующие расчеты:
– Подсчитать количество книг выданных в прокат каждому из
читателей.
– Вычислить сумму, потраченную каждым читателем, на приобретение книг в прокат.
– Вывести значение «Выдать карту», тем читателям, которые
взяли в библиотеке больше 10 книг или потратили напрокат книг
более 100р. Иначе выводить значение «Карта отсутствует».
Итоговые расчеты:
– Процент читателей, получивших карту постоянного посетителя от общего количества читателей;
– Выручка, полученная библиотекой за весь период времени;
– Наименьшее количество выданных книг;
– Три наибольшие суммы, затраченные напрокат книг.
Условное форматирование.
– В таблице “Отчет по читателям” выделить цветом информацию о клиентах, которые взяли в прокат менее 5 книг.
– В журнале учета выделить цветом книги, которые взяли лишь
один раз.
2 вариант
Отчет по водителям (Код водителя, ФИО водителя, Кол-во маршрутов, Сумма, Уровень водителя)
Осуществить следующие расчеты:
– Подсчитать количество маршрутов совершенных каждым водителем.
42
– Вычислить общую сумму, полученную от поездок каждым водителем.
– Вывести значение «Класс С», тем водителям, которые совершили менее 5 поездок, значение «Класс В» – от 5 до 10 поездок,
значение «Класс А» – более 10 поездок.
Итоговые расчеты:
– Процент водителей, имеющих уровень вождения класса А от
всего количества водителей;
– Сумма заработка, полученная от поездок водителями за весь
период времени;
– Наибольшее количество совершенных маршрутов;
– Вторая наименьшая сумма, полученная от поездок водителями.
Условное форматирование.
– В таблице “Отчет по водителям ” выделить цветом информацию о водителях, у которых уровень вождения класса С.
– В журнале учета выделить цветом маршруты, перевозки по
которым совершались лишь дважды.
3 вариант
Отчет по сотрудникам (Код сотрудника, ФИО сотрудника, Колво работ, Сумма, Премия)
Осуществить следующие расчеты:
– Подсчитать количество выполненных работ каждым сотрудником.
– Вычислить сумму, полученную каждым сотрудником.
– Сотрудникам, которые выполнили более 5 работ, и их сумма
заработка составила более 1000р рассчитать премию, составляющую 10% от суммы заработка сотрудника. Иначе выводить премию равную нулю.
Итоговые расчеты:
– Процент сотрудников, получивших премию;
– Общая сумма заработка сотрудников за весь период времени;
– Максимальное количество выполненных работ;
– Третья наименьшая сумма заработка сотрудников.
Условное форматирование.
– В таблице “Отчет по сотрудникам” выделить цветом информацию о сотрудниках, у которых сумма заработка превышает 5000р.
– В журнале учета выделить различным цветом стоимости
с максимальным и минимальным значением.
4 вариант
Отчет по преподавателям (Код преподавателя, ФИО преподавателя, Кол-во часов, Сумма, Премия)
43
Осуществить следующие расчеты:
– Подсчитать количество часов занятий, проведенных каждым
преподавателем.
– Вычислить сумму заработка, полученную каждым преподавателем.
– Преподавателям, сумма заработка которых составила от
1000 до 5000р вывести премию равную 100р, При заработке более
5000р рассчитать премию, составляющую 10% от суммы заработка
преподавателя. Иначе выводить премию равную нулю.
Итоговые расчеты:
– Процент преподавателей, имеющих заработок более 5000р;
– Общая сумма заработка преподавателей за весь период времени;
– Общее количество часов занятий, проведенных преподавателями;
– Средняя сумма заработка преподавателей.
Условное форматирование.
– В таблице “Отчет по преподавателям” выделить цветом информацию о преподавателях, у которых сумма заработка превышает
5000р и количество часов проведенных занятий больше 7.
– В журнале учета выделить цветом предметы, по которым проводилось лишь одно занятие.
5 вариант
Отчет по клиентам (Код клиента, ФИО клиента, Кол-во автомобилей, Сумма, Карта постоянного клиента)
Осуществить следующие расчеты:
– Подсчитать количество автомобилей, взятых напрокат каждым клиентом.
– Вычислить сумму, потраченную напрокат автомобилей каждым клиентом.
– Вывести значение «Выдать карту», тем клиентам, которые
взяли в прокат больше 3 автомобилей и потратили сумму более
50000р. Иначе выводить значение «Карта отсутствует».
Итоговые расчеты:
– Процент клиентов, получивших карту постоянного клиента от
всего количества клиентов;
– Выручка, полученная автопарком за весь период времени;
– Наименьшее количество выданных автомобилей;
– Три наименьшие суммы, затраченные на аренду автомобилей.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию
о клиентах, которые взяли в прокат менее 3 автомобилей.
44
– В журнале учета выделить цветом марки автомобилей, которые арендовали в прокат более 5 раз.
6 вариант
Отчет по клиентам (Код клиента, ФИО клиента, Кол-во товаров,
Сумма, Карта постоянного клиент)
Осуществить следующие расчеты:
– Подсчитать количество товаров, приобретенных каждым клиентом.
– Вычислить сумму, потраченную каждым клиентом.
– Вывести значение «Выдать карту», тем клиентам, которые купили более 5 товаров или потратили сумму более 5000р. Иначе выводить значение «Карта отсутствует».
Итоговые расчеты:
– Процент клиентов, получивших карту постоянного клиента от
общего количества покупателей;
– Выручка, полученная магазином за весь период времени;
– Наибольшее количество приобретенного товара;
– Третья наименьшая сумма, затраченная на покупку товара.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию
о клиентах, у которых количество приобретенного товара составляет более 10 шт.
– В журнале учета выделить цветом товары, которые покупали
менее двух раз.
7 вариант
Отчет по работникам (Код врача, ФИО врача, Кол-во обращений, Сумма, Премия)
Осуществить следующие расчеты:
– Подсчитать количество обращений к каждому врачу.
– Вычислить сумму заработка, полученную каждым врачом.
– Врачам, сумма заработка которых составила от 1000 до
3000р вывести премию равную 100р, При заработке более
3000р рассчитать премию, составляющую 20% от суммы заработка
врача. Иначе выводить премию равную нулю.
Итоговые расчеты:
– Процент врачей, получивших премию в размере 100р;
– Общая сумма заработка врачей за весь период времени;
– Минимальное количество обращений к врачу;
– Средняя сумма заработка врача.
Условное форматирование.
45
– В таблице “Отчет по работникам” выделить цветом информацию о врачах, у которых сумма заработка превышает 5000р или
количество обращений более 10 пациентов.
– В журнале учета выделить цветом клиентов, которые обращались в медицинский центр всего лишь раз.
8 вариант
Отчет по продукции (Код изделия, Вид изделия, Кол-во заказов,
Сумма, Спрос на изделие)
Осуществить следующие расчеты:
– Подсчитать количество заказов на пошив каждого из изделий.
– Вычислить сумму, потраченную клиентами на пошив каждого
из изделий (цена тканей в расчет не входит).
– Вывести значение «Пользуется спросом», для изделий, которые были заказаны более 10 раз. Иначе выводить значение «Не востребован».
Итоговые расчеты:
– Процент не востребованных изделий;
– Выручка, полученная ателье за пошив изделий;
– Наибольшее количество заказов;
– Вторая наименьшая сумма, затраченная на пошив изделия.
Условное форматирование.
– В таблице “Отчет по продукции” выделить цветом информацию о видах изделия, у которых количество заказов составляет более 7 раз.
– В журнале учета выделить цветом виды товаров, которые покупали больше 10 раз.
9 вариант
Отчет по клиентам (Код абонента, ФИО абонента, Кол-во минут,
Сумма, Скидка)
Осуществить следующие расчеты:
– Подсчитать количество минут, потраченных на переговоры
каждым абонентом.
– Вычислить суммарную стоимость разговоров каждого абонента.
– Абонентам, с продолжительностью разговоров более 1000 минут – скидка 10%. Остальным скидка на оплату переговоров составляет 0%.
Итоговые расчеты:
– Процент стоимости разговоров клиентов, превышающих
7000р от суммарной стоимости за весь период времени;
– Суммарная стоимость разговоров за весь период времени;
46
– Продолжительность самого длинного и самого короткого разговора;
– Среднее количество минут.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию об абонентах, которые получили скидку на оплату переговоров.
– В журнале учета выделить разным цветом города, с самой
длинной и самой короткой продолжительностью разговора.
10 вариант
Отчет по клиентам (Код клиента, ФИО клиента, Кол-во путевок,
Сумма, Карта постоянного клиента)
Осуществить следующие расчеты:
– Подсчитать количество путевок, приобретенных каждым клиентом.
– Вычислить сумму, потраченную каждым клиентом.
– Вывести значение «Выдать карту», тем клиентам, которые купили более 8 путевок и потратили сумму более 300000р. Иначе выводить значение «Карта отсутствует».
Итоговые расчеты:
– Процент клиентов, получивших карту постоянного клиента от
общего количества;
– Выручка, полученная турфирмой за весь период времени;
– Минимальное количество приобретенных путевок;
– Вторая наибольшая сумма, потраченная на приобретение путевок.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить различным цветом
информацию о клиентах, у которых суммарная стоимость путевок
меньше 10000р и больше или равна 500000р.
– В журнале учета выделить цветом маршруты, на которые приобретено больше 10 путевок.
11 вариант
Отчет по клиентам (Код клиента, ФИО клиента, Кол-во поселений, Сумма, Карта постоянного клиента)
Осуществить следующие расчеты:
– Подсчитать количество поселений каждого клиента.
– Вычислить сумму, потраченную каждым клиентом.
– Вывести значение «Выдать карту», тем клиентам, потратили
сумму на поселения более 30000р. Иначе выводить значение «Карта отсутствует».
47
Итоговые расчеты:
– Процент клиентов, не имеющих карты постоянного клиента
от общего количества;
– Выручка, полученная гостиницей за весь период времени;
– Минимальное и максимальное количество поселений;
– Средняя сумма, полученная за поселение клиентов.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию
о клиентах, у которых суммарная стоимость поселений составляет
10000р.
– В журнале учета выделить цветом номера, на который минимальный спрос.
12 вариант
Отчет по клиентам (Код клиента, ФИО клиента, Кол-во товаров,
Сумма, Карта постоянного клиент)
Осуществить следующие расчеты:
– Подсчитать количество товаров, приобретенных каждым клиентом.
– Вычислить сумму, потраченную каждым клиентом.
– Вывести значение «Выдать карту», тем клиентам, которые
приобрели более 7 товаров и потратили сумму более 5000р. Иначе
выводить значение «Карта отсутствует».
Итоговые расчеты:
– Процент клиентов, с общей стоимостью приобретенных товаров более 1000р;
– Выручка, полученная компанией за весь период времени;
– Наибольшее количество приобретенного товара;
– Вторая наименьшая сумма, затраченная на покупку товара.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию
о клиентах, у которых количество приобретенного товара составляет менее 5 шт.
– В журнале учета выделить цветом второй по востребованности
товар.
13 вариант
Отчет по клиентам (Код клиента, ФИО клиента, Кол-во фильмов, Сумма, Карта постоянного клиент)
Осуществить следующие расчеты:
– Подсчитать количество фильмов, взятых напрокат каждым
клиентом.
– Вычислить сумму, потраченную напрокат фильмов каждым
клиентом.
48
– Вывести значение «Выдать карту», тем клиентам, которые
взяли в прокат больше 17 фильмов или потратили сумму более
1000р. Иначе выводить значение «Карта отсутствует».
Итоговые расчеты:
– Процент клиентов, получивших карту постоянного клиента;
– Выручка, полученная компанией за весь период времени;
– Наименьшее количество выданных фильмов;
– Три наименьшие суммы, затраченные напрокат фильмов.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию
о клиентах, которые взяли напрокат менее 3 фильмов.
– В журнале учета выделить цветом названия фильмов, на которые нет спроса.
14 вариант
Отчет по клиентам (Код клиента, ФИО клиента, Кол-во заказов,
Сумма, Карта постоянного клиента)
Осуществить следующие расчеты:
– Подсчитать количество заказанных услуг каждым клиентом.
– Вычислить сумму, потраченную клиентами на услуги химчистки.
– Вывести значение «Выдать карту», тем клиентам, у которых
суммарная стоимость заказа более 7000р. Иначе выводить значение «Карта отсутствует».
Итоговые расчеты:
– Процент клиентов, получивших карту постоянного клиента;
– Выручка, полученная химчисткой за оказание услуг;
– Наибольшее количество заказов;
– Вторая наибольшая сумма, затрат на услуги.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию
о клиентах, сделавших более 7 заказов.
– В журнале учета выделить цветом виды услуг, на которые
оформляли заказы более 10 раз.
15 вариант
Отчет по работникам (Код специалиста, ФИО специалиста, Колво обращений, Сумма, Премия)
Осуществить следующие расчеты:
– Подсчитать количество обращений к каждому специалисту.
– Вычислить сумму заработка, полученную каждым специалистом.
– Специалистам, сумма заработка которых составила от
10000 до 30000р вывести премию составляющую 20% от суммы за49
работка специалиста, При заработке более 30000р - премия составит 10000р. Иначе премия не назначается.
Итоговые расчеты:
– Процент специалистов, получивших премию в размере менее
10000р;
– Общая сумма заработка специалистов за весь период времени;
– Минимальное количество обращений к специалисту;
– Средняя сумма заработка специалиста.
Условное форматирование.
– В таблице “Отчет по работникам” выделить цветом информацию о специалистах, у которых сумма заработка превысила
40000р или количество обращений превысило 10 пациентов.
– В журнале учета выделить цветом виды животных, которые
обследовались чаще других.
16 вариант
Отчет по продукции (Код изделия, Название изделия, Кол-во
купленных изделий, Сумма, Спрос на изделие)
Осуществить следующие расчеты:
– Подсчитать количество купленных изделий каждого вида.
– Вычислить сумму, потраченную клиентами на приобретение
изделий.
– Вывести значение «Пользуется спросом», для изделий, которые были приобретены более 3 раз. Иначе выводить значение «Не
востребован».
Итоговые расчеты:
– Процент не востребованных изделий;
– Выручка, полученная мастерской за весь период времени;
– Наибольшее количество купленных изделий;
– Вторая наименьшая сумма, затраченная на приобретение изделий.
Условное форматирование.
– В таблице “Отчет по продукции” выделить цветом информацию об изделиях, суммарная выручка которых составила более
50000р.
– В журнале учета выделить цветом названия материалов, которые покупали больше 10 раз.
17 вариант
Отчет по поставщикам (Код поставщика, ФИО поставщика,
Кол-во поставок, Сумма, Статус)
Осуществить следующие расчеты:
– Подсчитать количество поставленных деталей каждым поставщиком.
50
– Вычислить сумму, полученную каждым поставщиком за поставку деталей.
– Вывести значение «Важный», тем поставщикам, которые реализовали фирме более 10 деталей. Для остальных поставщиков поле оставить пустым.
Итоговые расчеты:
– Процент поставщиков, имеющих статус «Важный»;
– Суммарные затраты фирмы на приобретение товаров у поставщиков;
– Наибольшее количество поставок;
– Вторая наибольшая сумма, полученная поставщиками.
Условное форматирование.
– В таблице “Отчет по поставщикам” выделить цветом информацию о поставщиках, которые получили сумму более 1000р.
– В журнале учета выделить цветом названия деталей, которые
поставлялись на сумму более 3000р.
18 вариант
Отчет по клиентам (Код клиента, ФИО клиента, Кол-во стрижек, Сумма, Скидка)
Осуществить следующие расчеты:
– Подсчитать количество стрижек сделанных для каждого клиента.
– Вычислить сумму, потраченную клиентами на услуги парикмахерской.
– Клиентам, суммарной стоимостью оказанных им услуг более
3000р – скидка 10%. Остальным клиентам скидка 0%.
Итоговые расчеты:
– Процент клиентов, получивших скидку;
– Выручка, полученная парикмахерской за оказание услуг клиентам;
– Наибольшее количество стрижек;
– Максимальная сумма, затрат на услуги.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию
о клиентах, сделавших более 7 стрижек.
– В журнале учета выделить цветом стрижки, на которые не было спроса.
19 вариант
Отчет по клиентам (Код клиента, Название фирны клиента,
Кол-во услуг, Сумма, Карта постоянного клиента)
Осуществить следующие расчеты:
– Подсчитать количество оказанных услуг каждому клиенту.
51
– Вычислить сумму, потраченную на оказание услуг каждым
клиентом.
– Вывести значение «Выдать карту», тем клиентам, которые потратили сумму на оказание услуг более 5000р или с которыми совершено более 7 сделок. Иначе выводить значение «Карта отсутствует».
Итоговые расчеты:
– Процент клиентов, не имеющих карту постоянного клиента;
– Выручка, полученная нотариальной конторой за весь период
времени;
– Наименьшее количество оказанных услуг;
– Три наименьшие суммы, затраченные на совершение сделок.
Условное форматирование.
– В таблице “Отчет по клиентам” выделить цветом информацию
о клиентах, которые совершили лишь одну сделку с нотариальной
конторой.
– В журнале учета выделить цветом название услуги, которая
принесла наибольшую прибыль.
20 вариант
Журнал учета подписанных договоров (Дата заключения, Код
филиала, Наименование филиала, Страховая сумма, Тарифная
ставка / %, Код вида страхования, Наименование (страхование автотранспорта, страхование домашнего имущества, добровольное
медицинское страхование), Стоимость);
Отчет по филиалам (Код филиала, Наименование филиала, Колво договоров, Сумма, Премия)
Осуществить следующие расчеты:
– Подсчитать количество подписанных договоров каждым филиалом.
– Вычислить сумму, полученную филиалом за весь период времени.
– Филиалам, суммарной стоимостью подписанных им договоров
более 30000р – премия в 7000р. Остальным филиалам премия не
выдается.
Итоговые расчеты:
– Процент филиалов, получивших премию;
– Выручка, полученная филиалами;
– Наибольшее количество договоров;
– Максимальная сумма, затрат страхование.
Условное форматирование.
– В таблице “Отчет по филиалам” выделить цветом информацию
о филиалах, подписавших более 10 договоров или составивших
сумму сделок равную 2000р.
52
– В журнале учета выделить цветом виды страховок, на которые
не было спроса.
Варианты заданий к лабораторной работе № 3
ТЕХНОЛОГИЯ СОЗДАНИЯ РАСЧЕТНЫХ ДОКУМЕНТОВ С ИСПОЛЬЗОВАНИЕМ СПРАВОЧНЫХ ТАБЛИЦ
1 вариант
– Книги (Код книги, Название, Автор, Стоимость проката, Жанр);
– Читатели (Код читателя, Фамилия, Имя, Отчество, Год рождения, Адрес, Телефон).
2 вариант
– Маршруты (Код маршрута, Название, Дальность, Количество
дней в пути, Оплата за день);
– Водители (Код водителя, Фамилия, Имя, Отчество, Пол, Стаж,
Фотография);
3 вариант
– Сотрудники (Код сотрудника, Фамилия, Имя, Отчество, Год
рождения, Пол, Оклад, Фотография);
– Виды работ (Код вида, Описание, Оплата за день).
4 вариант
– Группы (Номер группы, Специальность, Отделение, Количество студентов);
– Преподаватели (Код преподавателя, Фамилия, Имя, Отчество,
Пол, Телефон, Стаж, Оплата за день, Фотография);
5 вариант
– Автомобили (Код автомобиля, Марка, Стоимость автомобиля,
Стоимость проката / в день, Тип, Фотография);
– Клиенты (Код клиента, Фамилия, Имя, Отчество, Адрес, Год
рождения, Пол, Телефон);
6 вариант
– Товары (Код товара, Название, Вид, Цена, Фотография);
– Клиенты (Код клиента, Фамилия, Имя, Отчество, Адрес, Пол,
Телефон, e-mail);
7 вариант
– Пациенты (Код пациента, Фамилия, Имя, Отчество, Пол, Год
рождения);
– Врачи (Код врача, Фамилия, Специализация, Категория,
Оплата за прием, Фотография);
8 вариант
– Ткани (Код ткани, Тип ткани, Цена ткани, Цвет);
53
– Изделия (Код изделия, Вид изделия, Цена изделия, Размер,
Фотография);
9 вариант
– Абоненты (Код абонента, Номер телефона, ИНН, Адрес, Карта
проезда);
– Города (Код города, Название, Тариф дневной, Тариф ночной);
10 вариант
– Маршруты (Код маршрута, Страна, Фотография, Климат,
Длительность, Отель, Стоимость);
– Клиенты (Код клиента, Фамилия, Имя, Отчество, Адрес, Телефон);
11 вариант
– Клиенты (Код клиента, Фамилия, Имя, Отчество, Паспортные
данные, Комментарий);
– Номера (Номер, Размещение (одноместное, двуместное), Категория (люкс, полулюкс, обычный), Фотография, Цена / день);
12 вариант
– Продукция (Код товара, Тип продукции, Название, Фотография, Цена, Единица измерения);
– Клиенты (Код клиента, Фамилия, Имя, Отчество, Пол, Телефон, Дата рождения);
13 вариант
– Фильмы (Код фильма, Название, Страна, Год, Режиссер, Стоимость проката / день, Жанр, Обложка диска);
– Клиенты (Код клиента, Фамилия, Имя, Отчество, Пол, Адрес,
Телефон);
14 вариант
– Виды услуг (Код вида услуг, Название, Тип белья, Фотография, Цена услуги);
– Клиенты (Код клиента, Фамилия, Имя, Отчество, Пол, Телефон);
15 вариант
– Животное (Код животного, Кличка, Вид животного, ФИО владельца, Телефон);
– Специалисты (Код специалиста, Фамилия, Имя, Отчество,
Пол, Специализация, Квалификация, Стоимость посещения, Фотография);
16 вариант
– Изделия (Код изделия, Название, Тип (серьги, кольца, броши,
браслеты), Вес, Цена, Фотография);
– Материалы (Код материала, Название (платина, золото, серебро, различные драгоценные камни и т.д.), Цена за грамм);
54
17 вариант
– Поставщики (Код поставщика, Название, Адрес, Карта проезда, Телефон);
– Детали (Код детали, Название, Артикул, Цена);
18 вариант
– Клиенты (Код клиента, Фамилия, Имя, Отчество, Телефон);
– Стрижки (Код стрижки, Название, Тип (мужская, женская,
универсальная), Фотография, Цена);
19 вариант
– Клиенты (Код клиента, Название фирмы, Вид деятельности,
Адрес, Карта проезда, Телефон);
– Услуги (Код услуги, Название (договоры купли-продажи долей, договоры купли-продажи квартир, Удостоверение договоров
отчуждения доли в Уставном капитале Обществ и т.д.), Сумма,
Описание);
20 вариант
– Филиал (Код филиала, Наименование, Адрес, Карта проезда,
Телефон);
– Вид страхования (Код вида страхования, Наименование (страхование автотранспорта, страхование домашнего имущества, добровольное медицинское страхование), Тарифная ставка / %);
СОДЕРЖАНИЕ
Предисловие.............................................................................
3
Лабораторная работа №1. Технологии создания и форматирования
электронной таблицы.................................................................
4
Лабораторная работа № 2. Технология вычислений
в электронных таблицах.............................................................
8
Лабораторная работа №3. Технология создания расчетных
документов с использованием справочных таблиц..........................
16
Лабораторная работа № 4 . Технология построения графиков
и диаграмм...............................................................................
27
Приложение..............................................................................
40
55
Документ
Категория
Без категории
Просмотров
0
Размер файла
968 Кб
Теги
agranovskiyilinskya
1/--страниц
Пожаловаться на содержимое документа