С. Е.

Черкезов

MS EXCEL

Ростов-на-Дону 2019
 ОГЛАВЛЕНИЕ

Электронная таблица MS Excel .................................................................................3

Лабораторная работа № 1. Создание и редактирование таблицы ..........................9

Лабораторная работа № 2. Вычисления в MS Excel ............................................... 13

Лабораторная работа № 3. Выполнение расчетов и оптимизация таблицы...........16

Лабораторная работа № 4. Визуализация данных ....................................................18

Лабораторная работа № 5. Использование таблицы в качестве базы данных....... 22

Лабораторная работа № 6. Таблицы. Подведение промежуточных итогов.

Сводные таблицы …………………………………………………………………….27

Лабораторная работа № 7. Логические функции.......................................................34

Лабораторная работа № 8. Пакет анализа...................................................................39

Библиографический список..........................................................................................44

2
 MS Excel

Общее представление MS Excel

Электронная таблица Excel – одна из составных частей пакета прикладных
программ Microsoft Office, работающего в среде Windows. Она предназначена для
обработки числовых данных, проведения математического моделирования
различных процессов, изготовления различных документов и форм, а также
использования в качестве простой базы данных.
Табличный процессор – компьютерная программа для хранения и
обработки информации, представленной в табличной форме.
Структура программы – двумерный массив, состоящий из строк и
столбцов, поэтому эти программные средства называют еще электронными
таблицами. С их помощью можно не только создавать таблицы, но и
автоматизировать обработку данных.
Функции табличного процессора:
- создание и редактирование таблиц;
- оформление и печать таблиц;
- создание многотабличных документов, объединенных формулами;
- построение диаграмм;
- выборка данных по запросам;
- создание итоговых и сводных таблиц;
- решение задач типа «что-если» путем подбора параметров;
- решение оптимизационных задач;
- создание программ-макросов с помощью встроенного языка
программирования Visual Basic for Applications (VBA).
После запуска Excel на экране появляется окно следующей структуры:
1. Заголовок программы.
2. Панель быстрого доступа с кнопкой настройки панели, расположенная в
верхней части окна приложения Excel (рис. 1), предназначена для быстрого
доступа к наиболее часто используемым функциям. По умолчанию панель
содержит всего три кнопки: Сохранить, Отменить, Вернуть (Повторить).
Панель быстрого доступа можно настраивать, добавляя в нее новые команды.

Рис. 1. Панель быстрого доступа

3. Лента. В Excel меню и панели инструментов выполнены в виде ленты.

Команды упорядочены в логические группы, собранные на вкладках. Вкладки
ориентированы на выполнение задач. Группы на каждой вкладке разбивают
задачу на ее составляющие. Кнопки команд в каждой группе служат для
выполнения команд или отображения меню команд.
Значок (кнопка) группы – маленький квадрат в правом нижнем углу группы
элементов во вкладке (рис. 2). Щелчок по значку открывает соответствующее

3
этой группе диалоговое окно или область задач для расширения функциональных
возможностей. Например, значок группы Шрифт вкладки Главная открывает
диалоговое окно Формат ячейки. А значок группы Буфер обмена отображает
область задач Буфер обмена. Не каждая группа имеет значок.
По умолчанию в окне отображается семь постоянных вкладок: Главная,
Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид.
Вкладка Главная открывается по умолчанию после запуска программы.

Рис. 2. Кнопки групп

Помимо постоянных имеется целый ряд контекстных вкладок, например
для работы с рисунками, диаграммами и т. п., которые появляются автоматически
при переходе в соответствующий режим либо при выделении объекта или
установке на него курсора. В некоторых случаях появляется сразу несколько
вкладок, например при работе с диаграммами появляются три вкладки:
Конструктор, Макет и Формат. Не существует способов принудительного
отображения контекстных вкладок.
Перемещаться по ленте можно с помощью клавиатуры: если нажать
клавишу <Alt>, на ленте появятся подсказки для перехода к соответствующей
вкладке (рис. 3).

Рис. 3. Клавиши перемещения по ленте

Перейдя на вкладку, можно увидеть подсказки по клавишам перехода по
текущей вкладке (рис. 4).

Рис. 4. Клавиши перехода вкладки Главная

Ленту можно настраивать: переименовывать и изменять последовательность
расположения постоянных вкладок, создавать новые вкладки и удалять их,
4
создавать, удалять, изменять расположение групп элементов на вкладках,
добавлять и удалять отдельные элементы и др. Щелкните правой кнопкой мыши в
любом месте ленты и в контекстном меню выберите команду Настройка ленты.
Также настройка ленты производится в окне Параметры Excel в категории
Настройка ленты меню Файл.
4. Вкладка (меню) «Файл». Вкладка Файл всегда расположена в ленте
первой слева. Меню содержит команды для работы с файлами (Сохранить,
Сохранить как, Открыть, Закрыть, Последние, Создать), для работы с текущим
документом (Сведения, Печать, Доступ), а также для настройки Excel (Справка,
Параметры). Кнопка Выход завершает работу с приложением.
5. Мини-панели инструментов. Мини-панели инструментов (рис. 5)
содержат основные наиболее часто используемые элементы для оформления
текста документа, рисунков, диаграмм и других объектов. В отличие от других
приложений Office (Word, PowerPoint и др.) в Excel мини-панель не отображается
автоматически при выделении фрагмента листа. Для ее отображения щелкните
правой кнопкой по выделенной области. Состав элементов мини-панелей
постоянный и неизменный.

Рис. 5. Мини-панель инструментов и контекстное меню

6. Строка формул, состоящая из трех частей:

a) адреса активной ячейки
b) кнопок Отмена, Ввод, которые появляются в режиме ввода или правки,
и кнопки Вставка функции fx;
c) содержимого активной ячейки.
7. Координатная строка – содержит имена столбцов.
8. Координатный столбец – содержит номера строк.
9. Рабочее поле.
10. Горизонтальная и вертикальная полосы прокрутки.
11. Ярлычки листов с кнопками перехода по рабочим листам.
12. Строка состояния, в которой указываются режимы работы Готово,
Ввод, Правка; режимы просмотра книги Обычный, Разметка страницы,
Страничный; масштабная линейка.
Основные объекты Excel
1. Столбец. Таблица Excel содержит 16384 столбца (214). Столбцы
идентифицированы латинскими буквами. Так как их только 26, то после Z

5
обозначение столбцов идет сдвоенными буквами AA, AB, AC, …, GA, GB, GC,
…, HX, HY, HZ, а после столбца ZZ – строенными ААА, ААВ, ААС, …, AAZ,
ABA , … Заканчивается нумерация на столбце XFD. Для быстрого перехода к
первому или последнему столбцу (строке) рабочего листа, нужно нажать клавишу
<Ctrl> и соответствующую клавишу управления курсором.
2. Строка – их в таблице 1048576 (220).
3. Ячейка – место пересечения строки и столбца. Каждая ячейка имеет
уникальный адрес, в котором указывается имя столбца и номер строки, на
пересечении которых она расположена.
Excel поддерживает альтернативную систему указания ячеек, называемую
R1C1 (от англ. слов Row – строка и Column – колонка). В этой системе и столбцы,
и строки таблицы пронумерованы, а номер строки предшествует номеру столбца.
Например, ячейка А1 называется R1C1 (строка 1, столбец 1). Ячейка В1 – это
R1C2 (строка 1, столбец 2). Перейти к альтернативному стилю и обратно можно,
зайдя в меню Файл → Параметры→ Формулы → категория Работа с формулами
→ стиль ссылок R1C1.
Ячейка, где находится курсор, называется текущей, и в данный момент
времени с ней выполняются определенные действия.
4. Блок ячеек – прямоугольник, в котором указываются адреса ячеек левого
верхнего и нижнего правого углов, разделенных двоеточием, например А1:С5.
Если в выполняемом действии указан блок ячеек, то задействованы все его
ячейки.
5. Рабочий лист – созданная таблица для решения задачи, диаграмма,
макрос, рисунок. Стандартное имя листа – Лист1, Лист2, …. С рабочими листами
можно выполнять следующие действия:
- переименование;
- удаление;
- вставка;
- перемещение;
- копирование.
Эти действия выполняются с помощью контекстного меню при
установленном указателе мыши на ярлычке листов или в группе Ячейки вкладки
Главная (рис. 6).

Рис. 6. Группа Ячейки вкладки Главная

6. Рабочая книга – это файл, который хранится на диске и содержит один
или несколько листов. По умолчанию рабочая книга имеет имя Книга1, Книга 2,
…. Сохранить файл можно с помощью пункта меню Файл →Сохранить. Команда
Сохранить как выбирается, если нужно сохранить уже записанный на диск файл
под новым именем. Файлам Excel по умолчанию присваивается расширение .xlsx.

6
 7. Диаграмма – это графическое отображение данных таблицы. Может
храниться на отдельном листе, а может сопровождаться текстом или таблицей.
8. Рисунок – создается с помощью группы Иллюстрации вкладки Вставка
в самой среде Excel или может быть вставлен из другого графического редактора.
9. Модули Visual Basic – программы, называемые макросами и созданные
на языке программирования Visual Basic.
Типы данных
В MS Excel используются следующие типы данных:
1. Текст – любая последовательность символов, используемая в основном
для заголовков таблиц, строк, столбцов и комментариев.
2. Число. В ячейке Excel можно отобразить три типа числовых данных
(констант):
a) целые числа – это последовательность цифр от 0 до 9 со знаком или без
него: +25; –100.
b) вещественные числа с фиксированной запятой – это десятичные дроби, в
которых целая часть отделяется от дробной запятой: 28,25; –3,765.
c) вещественные числа с плавающей запятой – это числа, записанные в
следующей форме: 1,5Е+03 (1,5 ×103) или 2Е-08 (2 ×10-8) . Такую запись еще
называют экспоненциальной формой записи числа (научный формат).
По умолчанию правильно введенное число выравнивается по правому краю
ячейки. Неправильно введенное число считается текстом и выравнивается по
левому краю. Если число не поместилось по ширине ячейки, то вся ячейка
заполняется символом # (рис. 7).

Рис. 7. Вид чисел на рабочем листе

3. Формула – выражение, состоящее из чисел, адресов ячеек, функций и
знаков операций и начинающееся со знака =. Например:
=(C6+30)*A1-12
=А1*20%+12*A1*D12.
Порядок выполнения арифметических действий в формуле такой же, как
принятый в математике.
4. Функция – запрограммированные формулы, позволяющие производить
часто встречающиеся последовательности вычислений (стандартные функции
разного назначения).
5. Дата – дата, представленная в различных форматах, с которой можно
производить арифметические и логические операции. Например, 21.10.2011; 21
октября 2011 г. и др.
7
 Копирование ячеек
Копирование можно осуществить следующими способами:
1) вкладка Главная → группа Буфер обмена → команда Копировать;
2) с помощью команды Копировать контекстного меню;
3) с помощью комбинации клавиш <Ctrl+C>;
4) с помощью техники «перетащить-и-оставить» при нажатой клавише
<Ctrl>. В этом случае рядом с указателем мыши появляется знак + (признак
копирования);
5) с использованием маркера в правом нижнем углу копируемой ячейки.
При отсутствии этого маркера необходимо воспользоваться командой Файл →
Параметры → Дополнительно → категория Параметры правки → Разрешить
маркеры заполнения и перетаскивания ячеек.
Ссылки на ячейки и диапазоны ячеек (адресация ячеек)
По умолчанию в новых формулах Excel использует относительные ссылки.
В этом случае при копировании ячейки с формулой адреса ячеек в формулах
изменяются соответственно перемещению.
Абсолютная адресация - ссылки на ячейки, адреса которых не меняются
при копировании содержимого ячеек вдоль строк и вдоль столбцов. Сама ссылка
остается неизменной независимо от того, куда перенесена ячейка с формулой, т. е.
абсолютная ссылка в формуле всегда ссылается на ячейку, расположенную в
определенном месте. Для задания абсолютных ссылок используется знак $.
Например: $B$2.
Эквивалентным абсолютной адресации является присвоение ячейки или
диапазону ячеек имени. Это делается в диалоговом окне Создание имени
команды Присвоить имя, находящейся в группе Определенные имена вкладки
Формулы (рис. 8). Команду Присвоить имя также можно выбрать в контекстном
меню, нажав правую кнопку мыши при установленном курсоре на выбранной
ячейке.

Рис. 8. Команда Присвоить имя… и диалоговое окно Создание имени

Для удаления имени ячейки необходимо выбрать в группе Определенные
имена вкладки Формулы команду Диспетчер имен и в открывшемся диалоговом

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

Рис. 9. Диалоговое окно Диспетчер имен

ЛАБОРАТОРНАЯ РАБОТА № 1
СОЗДАНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦЫ

1. Введите данные на рабочий лист (рис. 1.1).

Рис. 1.1
Указание. Для копирования и заполнения данных в смежных ячейках
можно воспользоваться маркером заполнения. Это черный квадрат в правом
нижнем углу выделенных ячеек . При наведении на маркер указатель мыши
принимает вид черного креста. Для заполнения выделите ячейки, которые станут
источником данных, а затем протяните маркер вниз, вверх или в стороны на
ячейки, которые необходимо заполнить. Для копирования элементов списка
(месяцы, дни недели и др.) при протаскивании мышью маркера удерживайте
нажатой клавишу Ctrl. Для выбора варианта заполнения можно протягивать
маркер правой кнопкой мыши.
2. Отредактируйте заголовки колонок: Категория измените на Товар, Цена
измените на Цена, р.

9
 3. Разместите между строками с информацией о шоколаде и кофе две
пустых строки и введите в них данные (диапазон А6:Е7):

4. Вставьте между колонками Цена и Поставщик колонку Количество и

заполните ее данными:

5. Разместите колонку Поставщик после колонки Товар.

Указание. Выделите столбец Поставщик, наведите указатель мыши на
границу выделения, когда он примет вид перетащите этот столбец правой
кнопкой мыши на столбец Цена и в появившемся меню выберите
команду Сдвинуть вправо и переместить.
6. Дополните таблицу (диапазон A13:F16) следующей информацией:

7. Вставьте перед колонкой Поступление пустую колонку и введите

заголовок № п/п.
8. Используя маркер заполнения, пронумеруйте строки таблицы цифрами от
1до 15 в колонке № п/п.
9. Удалите из таблицы строку под номером 4 в колонке № п/п и исправьте
нумерацию строк в данной колонке.
10. Используя команду Главная → Редактирование → Найти и выделить →
Заменить, в колонке Поставщик замените Ланта на Лавита.
11. Разместите над заголовками колонок две пустые строки и введите в
ячейку А1 название таблицы: Реализация товаров со склада № 22.
12. Используя команду Главная → Выравнивание → Объединить и
поместить в центре, разместите заголовок по центру колонок.

10
 13. В ячейку А2 введите слово Дата, в ячейку В2 введите текущую дату, в
ячейку Е2 введите слово Время, в ячейку F2 введите текущее время.
14. Нарисуйте границы в таблице.
15. Сравните созданную Вами таблицу с таблицей, представленной на рис.
1.2. При наличии расхождений внесите исправления.

Рис. 1.2
16. Установите параметры страницы: ориентация – альбомная; верхнее и
нижнее поле – 2 см, левое поле – 3 см, правое поле – 1 см, центрирование на
странице – горизонтальное и вертикальное.
17. С помощью команды Вставка → Текст → Колонтитулы создайте для
рабочего листа верхний и нижний колонтитулы. В верхнем колонтитуле в левой
части напечатайте название лабораторной работы, а в правой Вашу фамилию и
инициалы. В нижнем колонтитуле в центре укажите текущую страницу из общего
количества страниц.
18. Выведите таблицу на экран в режиме предварительного просмотра
(команда Файл → Печать).
19. Переименуйте Лист 1 на Таблица.
20. Выделите колонки Товар, Цена, Количество и скопируйте их на Лист 2.
21. После Листа 3 вставьте новый лист.
22. Создайте копию рабочего листа Таблица.
23. Скопируйте рабочий лист Таблица в новую рабочую книгу.
Указание. В контекстном меню ярлыка листа Таблица выберите команду
Переместить или скопировать, в раскрывающемся списке Переместить
выбранные листы в книгу укажите Новая книга, → Создать копию.
24. Сохраните созданную рабочую книгу в своей папке на диске под именем
Фамилия_Работа_1.
25. Перейдите на Лист 3 рабочей книги.

11
 26. Переместите табличный курсор:
а) в последнюю строку рабочего листа;
б) в последний правый столбец рабочего листа и запишите в активную
ячейку ее адрес (для возвращения в начало рабочего листа нажмите Ctrl + Home);
в) в ячейку S3456 (клавиша F5).
27. Выполните поочередно выделение с помощью мыши:
а) диапазона C3:H9;
б) диапазонов A1:A5, C3:E3, H2:I8;
в) строк 4,5,6,7;
г) столбцов B, C, F, G;
д) строк с 18 по 48;
е) всех ячеек рабочего листа;
ж) столбца XEV;
з) строки 10000.
28. Выделите текущую область рабочего листа Таблица, используя команду
Главная → Редактирование → Найти и выделить → Выделение группы ячеек.
29. Заполните строку значениями от 0 до 0,5 с шагом 0,05, используя маркер
заполнения.

Указание. Введите в соседние ячейки два первых значения. Выделите их и

протяните за маркер заполнения.
30. Заполните строку значениями арифметической прогрессии от –1 до 0 с
шагом 0,1, используя команду Главная → Редактирование → Заполнить →
Прогрессия.

31. Заполните столбец значениями геометрической прогрессии:

32. Заполните данными Лист 4, используя маркер заполнения и команду

Прогрессия.

12
 33. Введите значения элементов матрицы на рабочий лист.

34. Транспонируйте матрицу.

Указание. Для транспонирования матрицы ее необходимо скопировать в
буфер обмена и вставить в произвольном месте рабочего листа с помощью ко-
манды Главная → Буфер обмена → Вставить → Специальная вставка.
35. Сохраните рабочую книгу.

ЛАБОРАТОРНАЯ РАБОТА № 2

ВЫЧИСЛЕНИЯ В MS EXCEL

Задание 1. Заполните данными таблицу и выполните вычисление в ней.

13
 Задание 2. В ячейках введены Фамилия, Имя, Отчество. Напишите формулу
для вывода в ячейке фамилии и инициалов в виде Фамилия И. О.

Указание. В формуле используйте операцию объединения строк & и

функцию ЛЕВСИМВ().

Задание 3. Вычислите сумму и произведение цифр двузначного числа.

Указание. Используйте функции ЦЕЛОЕ() для вычисления количества

десятков в двузначном числе (1 цифра) и ОСТАТ() для вычисления единиц (2
цифра).
Задание 4. Определите, в какой день недели (понедельник, вторник, …) Вы
родились.
Указание. 1-й способ. В ячейку введите дату. В контекстном меню ячейки
выберите команду Формат ячеек… → Число → (все форматы) и в поле Тип
введите ДДДД.
2-й способ. В ячейку введите дату. В соседней ячейке воспользуйтесь
функцией ТЕКСТ().

Задание 5. Вычислите количество полных прожитых лет на текущий день.

Дата рождения

Возраст

Указание. Для вычисления возраста человека, день рождения которого

записан в ячейке А1, используется формула:

14
 Задание 6. Дан протокол соревнования по конькобежному спорту. По
данному протоколу определите время пробега дистанции для каждого спортсмена
в минутах.

Указание. Для отображения количества минут между двумя моментами

времени (аналогичный прием подходит для часов и секунд) установите формат
[мм]. В контекстном меню ячейки выберите команду Формат ячеек → Число →
(все форматы) и в поле Тип введите [мм].
Задание 7. Имеются 2 таблицы с данными.

Используя функции вертикального поиска ВПР() для цены и

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

15
 ЛАБОРАТОРНАЯ РАБОТА № 3

ВЫПОЛНЕНИЕ РАСЧЕТОВ И ОПТИМИЗАЦИЯ ТАБЛИЦЫ

1. Введите данные на рабочий лист (рис. 3.1).

Рис. 3.1

2. Вставьте формулы для вычислений в столбцах Районный коэффициент,

Начислено, Подоходный налог, Сумма к выдаче (в квадратных скобках указаны
номера столбцов):
[4] = [2] ∙ k
[5] = [2] + [4]
[6] = ([5] – [3]) ∙ n
[7] = [5] – [6]
3. В последней строке вставьте формулы для вычисления итоговых сумм по
столбцам Подоходный налог и Сумма к выдаче.
4. Ниже таблицы вставьте формулы для вычисления:
а) максимальной суммы к выдаче;
б) среднего оклада;
в) минимального налога;
г) количества рабочих, оклады которых превышают 16 000 руб. (функция
СЧЁТЕСЛИ());
д) суммарный подоходный налог рабочих, имеющих налоговые вычеты
(функция СУММЕСЛИ());
е) суммарный подоходный налог рабочих, оклады которых превышают 16
000 руб. и не имеющих налоговые вычеты (функция СУМ-МЕСЛИМН()).
5. Введите поясняющую информацию к формулам.
6. Отобразите значения во всей таблице в денежном формате с двумя
знаками после десятичной запятой.

16
 7. Установите в итоговой строке заливку ячеек черным цветом, белый цвет
шрифта, полужирное начертание.
8. Отформатируйте таблицу согласно образцу, представленному на рис. 3.2.

Рис. 3.2
9. Сохраните созданную Вами рабочую книгу в своей папке на рабочем
диске под именем Фамилия_Работа_3.
10. Скопируйте лист с именем Лист 1.
11. Переименуйте Лист 1 на лист с именем Ведомость, а Лист 1(2) на
Формулы.
12. На листе Формулы отобразите формулы в ячейках таблицы.
13. Скопируйте с листа Ведомость на Лист 3 столбцы Ф.И.О., Сумма к
выдаче. Для вставки из буфера обмена используйте специальную вставку
(команда Главная → Буфер обмена → Вставить → Специальная вставка
→ значения).
14. Добавьте к таблице поля Сообщение о надбавке, Величина надбавки,
Итоговая сумма. Введите заголовок таблицы Расчет надбавки. Введите
нумерацию столбцов (рис. 3.3).
15. Введите в столбец Сообщение о надбавке формулу, которая выводит со-
общение Да, если сумма к выдаче составляет менее 20 000 р., и Нет в противном
случае: =ЕСЛИ(В4<20000;"Да";"Нет").
16. Введите в столбец Величина надбавки формулу, которая выводит сумму
надбавки равную 20% от суммы к выдаче, если данная сумма составляет менее 20
000 р., и 0 в противном случае.
17. Вставьте формулу для вычисления значений по столбцу Итоговая сумма.

17
 18. Сравните полученную Вами таблицу с таблицей, представленной на рис.
3.3. При расхождении откорректируйте таблицу.

Рис. 3.3

ЛАБОРАТОРНАЯ РАБОТА № 4

ВИЗУАЛИЗАЦИЯ ДАННЫХ

Задание 1. Построение графиков математических функций

1. Создайте на Листе 1 таблицу для построения графиков функций и

y2 =2 на отрезке [–3; 3] с шагом 0,5. При заполнении используйте формулы для
x

вычисления y1 и y 2 .

2. Ниже таблицы вставьте диаграмму. Тип диаграммы – точечная с

гладкими кривыми и маркерами.
3. Установите цвета линий графика и маркеров: для y 1 – черный, для y2 –
темно-синий. Измените тип маркеров на графиках (рис. 4.1).
4. Добавьте название диаграммы. Отобразите вертикальные и
горизонтальные линии сетки.
5. Установите отображение значений горизонтальной оси на отрезке от –3
до 3.

18
 6. Сравните построенную Вами диаграмму с представленной на рис. 4.1.
При наличии расхождений между ними внесите в Вашу диаграмму необходимые
изменения.

Рис. 4.1
Задание 2. Построение диаграмм
1. Введите данные на Лист 2.

2. Скопируйте их на Лист 3.
3. На Листе 2 ниже таблицы постройте диаграмму график с маркерами.
4. Увеличьте размер диаграммы.
5. Измените для ряда Продукты питания тип диаграммы на гистограмму с
группировкой (рис. 4.2).

Рис. 4.2

19
 6. Установите для гистограммы ряда Продукты питания градиентную
заливку «Рассвет».
7. Установите для линий графика следующие цвета: коммунальные платежи
– красный, обслуживание автомобиля – синий, выплата кредитов – оранжевый,
прочие расходы – зеленый.
8. Вставьте название диаграммы «Динамика расходов за первое полугодие».
9. Установите вертикальное выравнивание подписей на горизонтальной оси
категорий.
10. Сравните построенную Вами диаграмму с представленной на рис. 4.2.
При наличии расхождений, внесите в Вашу диаграмму необходимые изменения.
11. На этом же рабочем листе для исходных данных постройте линейчатую
диаграмму с накоплениями.
12. Установите размеры диаграммы: высота – 8 см., ширина – 20 см.
13. Вставьте название диаграммы и подписи данных (рис. 4.3).
14. Сравните построенную Вами диаграмму с представленной на рис. 4.3.
При наличии расхождений между ними внесите в Вашу диаграмму необходимые
изменения.

Рис. 4.3
15. В исходной таблице вычислите суммарные расходы за полугодие и
постройте по ним кольцевую диаграмму.
16. Вставьте название диаграммы и подписи данных.

Рис. 4.4

20
 17. Сравните построенную Вами диаграмму с представленной на рис. 4.4.
При наличии расхождений между ними внесите в Вашу диаграмму необходимые
изменения.
18. В исходной таблице вычислите суммарные расходы по каждому месяцу
и постройте по ним объемную круговую диаграмму.
19. С помощью команды Конструктор → Переместить диаграмму
расположите ее на отдельном листе.
20. Отформатируйте область диаграммы: граница – сплошная линия темно-
синего цвета, шириной 2пт. с тенью.
21. Удалите легенду.
22. Измените подписи данных: у каждого сектора диаграммы отобразите
название месяца и долю в процентах от общих расходов за первое полугодие (рис.
4.5).
23. Сектор с максимальными расходами расположите отдельно от
остальных секторов.
24. Сравните построенную диаграмму с рис. 4.5. Покажите результаты
Вашей работы преподавателю.

Рис. 4.5.

Задание 3. Построение спарклайнов

1. В таблице на Листе 3 вычислите ежемесячные расходы, добавьте строку
ежемесячных доходов и определите ежемесячные накопления (рис. 4.6).
2. Добавьте в таблицу столбец Тенденции и постройте в ячейках этого
столбца спарклайны следующих типов: для расходов – спарклайн График, для до-
ходов – спарклайн Столбец (Гистограмма), для накоплений – спарклайн
Выигрыш/проигрыш.

21
 3. Измените высоту строк и ширину столбца со спарклайнами для
наглядного отображения тенденций.
4. Отметьте маркерами на графиках спарклайнов минимальные и
максималь-ные значения.
5. На гистограмме спарклайна выделите цветом минимальное значение.
6. Сравните построенный Вами результат с представленным на рис. 4.6. При
наличии расхождений между ними внесите необходимые изменения.

Рис. 4.6

ЛАБОРАТОРНАЯ РАБОТА № 5

ИСПОЛЬЗОВАНИЕ ТАБЛИЦЫ В КАЧЕСТВЕ БАЗЫ ДАННЫХ

1. Введите данные на рабочий лист (рис. 5.1). Стоимость заказа вычисляется

как произведение количества оплаченных единиц товара в заказе на цену единицы
товара.
2. Сохраните созданную рабочую книгу в своей папке на рабочем диске под
именем Фамилия_Работа_5.
3. Последовательно выполните в таблице сортировку записей (команда
Данные → Сортировка и фильтр → Сортировка):
а) по фамилиям заказчиков в алфавитном порядке;
б) по стоимости заказов в убывающем порядке;
в) по наименованию товаров в алфавитном порядке, а внутри каждой
полученной группы по количеству единиц товара в заказе по возрастанию;

22
 г) по фамилиям заказчиков в алфавитном порядке, а внутри каждой
полученной группы по дате заказа.

Рис. 5.1
4. С помощью фильтра (команда Данные → Сортировка и фильтр →
Фильтр) получите выборку данных в таблице по следующим условиям отбора:
а) определить все заказы Михайловой Н. А.

б) определить заказы за период с 03.05.14, цена единицы товара в которых

более 3000 руб.

в) определить записи с фамилиями заказчиков, начинающихся на букву Б

или М.

23
 г) выбрать заказы пароварок за апрель.

д) определить заказы за месяц май, количество единиц товара в которых

составляет от 10 до 20.

е) определить заказ с максимальной стоимостью.

ж) определить первые четыре заказа с наибольшей стоимостью.

з) выбрать заказы, цена товаров которых выше средней цены по ведомости.

24
 5. С помощью расширенного фильтра (команда Данные → Сортировка и
фильтр → Дополнительно), получите выборку данных в таблице согласно
приведенным условиям (критерии отбора расширенного фильтра и результаты
фильтрации сохраните на рабочем листе):
а) определить заказы Седовой Н. Р., цена за единицу товара в которых более
2000 руб.

б) определить заказчиков, у которых в заказе количество единиц товара

более 15 или цена единицы товара менее 1000 руб.

в) выбрать заказы пароварки за апрель.

г) определить заказы, в которых количество единиц товара больше

количества оплаченных единиц.

25
 д) определить заказы за вторую половину мая или заказы, количество
единиц товара в которых более 15.

е) определить заказы, количество оплаченных единиц товара в которых

менее 16. Из списка исключить кофеварки и кофемолки.

ж) определить заказы, цена единицы товара в которых превышает среднюю

цену по ведомости или меньше 800 руб.

6. Сохраните рабочую книгу.

26
 ЛАБОРАТОРНАЯ РАБОТА № 6

ТАБЛИЦЫ. ПОДВЕДЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ.

СВОДНЫЕ ТАБЛИЦЫ

1. Используя операции копирования и заполнения, введите данные на

рабочий лист (рис. 6.1).

Рис. 6.1
27
 2. Преобразуйте введенные данные в таблицу (команда Вставка → Таблицы
→ Таблица).
3. Последовательно выполните сортировку в таблице, используя кнопки
фильтра:
а) по регионам в алфавитном порядке;
б) по плановым показателям от максимального к минимальному;
в) по фактическим показателям от минимального к максимальному;
г) по городам в алфавитном порядке.
4. Добавьте в таблицу столбец Процент выполнения и вычислите значения в
нем по формуле. Отобразите результат с двумя знаками после запятой.

5. В режиме Работа с таблицами с помощью команды Конструктор → Па-

раметры стилей таблицы → Строка итогов вставьте строку с итоговыми
значениями.
6. В строке итогов отобразите суммарные значения по столбцам План, Факт
и среднее значение по столбцу Процент выполнения.
7. На Листе 2 создайте таблицу (рис. 6.2).

Рис. 6.2
8. В исходной таблице, используя кнопки фильтра, последовательно
отобразите итоги по каждому городу и скопируйте их в новую таблицу на Листе
2. Для вставки из буфера обмена используйте команду Специальная вставка →
Значения.
9. Снимите фильтр с поля Город.
10. Отобразите в строке итогов максимальные плановые и фактические
значения, минимальный процент выполнения.
11. Сохраните созданную рабочую книгу в своей папке на рабочем диске
под именем Фамилия_Работа_6.
12. Покажите результаты Вашей работы преподавателю.
13. Уберите строку итогов и преобразуйте таблицу в обычный диапазон с
помощью команд контекстной вкладки Конструктор.
14. Удалите столбец Процент выполнения.
15. Используя команду Данные → Структура → Промежуточный итог,
определите итоговые плановые и фактические продажи для каждого квартала
(рис. 6.3).
28
 Рис. 6.3
16. Покажите результаты Вашей работы преподавателю.
17. Отмените вычисление итоговых значений.
18. Определите итоговые плановые и фактические продажи для каждого
города.

29
 19. С помощью кнопок структуры 1, 2, 3 или +/–, расположенных слева от
таблицы, установите отображение итогов по городам (рис. 6.4).

Рис. 6.4
20. Отмените вычисление итоговых значений.
21. Определите итоговые плановые и фактические продажи для каждого
региона и количество продаж в регионе (рис. 6.5).

Рис. 6.5
22. Покажите результаты Вашей работы преподавателю.
23. Отмените вычисление итоговых значений.
24. На новом листе создайте сводную таблицу (команда Вставка → Таблицы
→ Сводные таблицы) с данными о фактических продажах для каждого города по
кварталам (рис. 6.6).
25. Для отображения наименования полей используйте команду
Конструктор → Макет отчета → Показать в табличной форме.

30
 Рис. 6.6
26. Для данных в сводной таблицы установите денежный формат.
27. Не изменяя структуру сводной таблицы, с помощью команды
Параметры → Активное поле → Параметры поля отобразите максимальные
фактические продажи для каждого города по кварталам (рис. 6.7).

Рис. 6.7
28. На новом листе рабочей книги создайте сводную диаграмму,
отображающую плановые продажи по регионам для каждого месяца (рис. 6.8).

Рис. 6.8
31
 29. На новом листе рабочей книги создайте сводную таблицу с фильтром по
кварталу (рис. 6.9).

Рис. 6.9
30. Отобразите сводные данные в таблице только по первому кварталу.
31. На новом листе рабочей книги создайте сводную таблицу фактических
продаж по месяцам для каждого квартала (рис. 6.10).
32. Добавьте срез по городам с помощью команды Параметры → Сортиров-
ка и фильтр → Вставить срез.

Рис. 6.10
33. Используя срез, отобразите фактические продажи для города
Хабаровска.
34. Сохраните рабочую книгу.

32
 ЛАБОРАТОРНАЯ РАБОТА № 7

ЛОГИЧЕСКИЕ ФУНКЦИИ

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

правильного решения нужно проверить выполнение одного или нескольких
условий. Наиболее часто используемые функции этой категории – ЕСЛИ, ИЛИ,
И, НЕ.
1. Функция ЕСЛИ. Формат записи функции:
ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)
Эта функция позволяет осуществить выбор варианта вычислений. Если
значение логического выражения истинно, то выбирается значение_истина; если
ложно – выбирается значение_ложь.
2. Функция И. Формат записи функции:
И(логическое_значение1;логическое_значение2;…)
Результат функции – истина, если значения всех составных логических
выражений истинны; в противном случае – ложь.
3. Функция ИЛИ. Формат записи функции:
ИЛИ(логическое_значение1;логическое_значение2;…)
Результат функции – ложь только в том случае, если значения всех
составляющих ложны; в остальных случаях – истина.
4. Функция НЕ. Формат записи функции:
И(логическое_значение)
Изменяет значение ЛОЖЬ на ИСТИНА и ИСТИНА на ЛОЖЬ.
Таблица истинности функций И, ИЛИ, НЕ представлена в таблице 7.1.
Таблица 7.1.

В ячейке А1 имеется число 5, в ячейке А2 – 10, в ячейке А3 – 8. Выясним

результаты вычислений некоторых функций (табл. 7.2.).
Таблица 7.2.

33
 Даны три положительных числа. Выяснить, образуют ли они треугольник
(т. е. являются ли они сторонами треугольника).

Занесем исходные данные задачи в ячейки таблицы, как показано на рис.7.1.

В ячейку D3 внесем формулу:
Е=СЛИ(И (A3+B3>C3;B3+C3>A3;A3+C3>B3);"образуют треугольник"; "не
образуют треугольник") и скопируем в ячейки D4 и D5.

Рис. 7.1. Исходные данные задачи и результат вычисления функции

Заполнение диалогового окна функции ЕСЛИ показано на рис. 7.2. функции
И – на рис. 7.3.

Рис. 7.2. Диалоговое окно функции ЕСЛИ

Рис. 7.3. Диалоговое окно функции И

34
 Логические функции помогают создавать сложные формулы, которые в
зависимости от выполнения тех или иных условий будут совершать различные
виды обработки данных.
Использование логических функций при создании сложных условий
рассмотрим на примерах.
Пример 1. Для группы сотрудников из пяти человек страховой компании
известны оклад и сумма договоров страхования за один месяц.
Страховой агент получает процент от суммы страхования. Если сумма
договора до 30 000 руб., то вознаграждение составляет 5 % от суммы договора,
если сумма от 30 000 до 50 000 руб., то 7 %, если выше 50 000, то 10 %.
Определить сумму заработную плату каждого сотрудника.
Решение. Заполните таблицу по образцу, представленному на рис. 7.4. В
ячейку Е3 внесите формулу, используя логическую функцию ЕСЛИ, и скопируйте
в ячейки E4:E7. В ячейку F3 внесите формулу, используя функцию СУММ, и
скопируйте в ячейки F4:F7. Результат произведенных вычислений показан на рис.
7.5.

Рис. 7.4. Использования функций для решения задачи

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

Рис. 7.5. Результаты вычислений

Пример 2. Дана ведомость итогов сессии по трем предметам для группы из
5 студентов. Определить размер стипендии для каждого студента с учетом

35
полученных оценок: если все экзамены сданы на «отлично», делается надбавка 50
% к минимальной стипендии, если на «хорошо» и «отлично» – надбавка 25 %,
если имеются оценки «удовлетворительно» – надбавки нет, если есть
«неудовлетворительно», то стипендия не начисляется. Минимальный размер
стипендии считать равным 1400 руб.
Решение. Заполните таблицу по образцу, представленному на рис. 7.6.
В ячейку F3 внесите формулу, используя логические функции ЕСЛИ, ИЛИ,
И и делая абсолютную ссылку на ячейку С9, в которую занесен минимальный
размер стипендии. Скопируйте формулу в ячейки F4:F7. Измените значения
оценок по предметам и оцените результат работы формулы.
F3 → =ЕСЛИ(И(C3=5;D3=5;E3=5);$C$9+$C$9*50%;
ЕСЛИ(ИЛИ(C3=2;D3=2;E3=2);0;
ЕСЛИ(ИЛИ(C3=3;D3=3;E3=3);$C$9;$C$9+$C$9*25%)))

Рис. 7.6. Форматирования таблицы с результатом вычислений

Решите задачу согласно Вашему варианту.

В первой строке рабочего листа поместите название таблицы,
соответствующее тематике задачи. Количество столбцов таблицы и их названия
определите самостоятельно. При заполнении столбца «№ п/п» используйте
автозаполнение. Если какое-либо данное помещено в отдельную ячейку (как на
рис. 7.6. минимальная стипендия), то в формуле на него делается абсолютная
ссылка.
Вариант 1. На складе фирмы хранятся скоропортящиеся продукты. Для
пяти продуктов известны объем, цена единицы продукта и срок хранения к
текущему моменту. По истечении срока хранения производится уценка продукта
или его списание по следующей схеме: если продукт хранится к настоящему
моменту не более 10 суток, то цена остается прежней; от 10 до 15 суток – уценка
на 15 % от первоначальной цены; от 15 до 20 суток – на 30 %; от 20 до 30 суток –
на 50 %; свыше 30 суток – продукт списывается (т. е. его цена становится равной
нулю). Определить, на какую сумму к началу месяца хранится каждого вида
продукции.

36
 Вариант 2. При продаже квартир сотрудникам строительной фирмы в
зависимости от стажа работы предусмотрены следующие скидки: при стаже менее
5 лет скидки нет, при стаже от 5 до 7 лет скидка 3 % от номинальной стоимости
квартиры; при стаже от 7 до 10 лет скидка 5 %; при стаже от 10 до 15 лет скидка
10 %; при стаже больше 15 лет скидка 20 % от номинальной стоимости квартиры.
Рассчитать стоимость квартиры с учетом скидки в зависимости от стажа
работы сотрудника. Номинальная стоимость квартиры (это данное поместите в
отдельную ячейку) и стаж работы сотрудников известны. Стоимость квартиры =
Номинальная стоимость – Номинальная стоимость × Процент скидки. Данные
известны для пяти сотрудников.
Вариант 3. Для сотрудников отдела из пяти человек известны: фамилии,
количество отработанных дней в месяце, тариф (сумма однодневного заработка,
это данное поместите в отдельную ячейку) и количество отработанных дней,
пришедшихся на выходные и праздники. Величина начисляемой премии
сотрудникам учреждения зависит от количества отработанных дней в выходные и
праздники: если 0 дней, то премии нет, если 1 или 2 дня, то надбавка к основному
заработку составляет 5 %, если 3–5 дней – 7 %, больше 5 дней – 10 %. Удержания
составляют: профсоюзный взнос 1 % и подоходный налог 13 % от заработка с
учетом премии. Рассчитать заработную плату за отработанные дни, величину
премии, сумму заработной платы каждого сотрудника и суммарную заработную
плату всего отдела в зависимости от указанных параметров.
Вариант 4. Для группы продавцов из пяти человек известны: фамилии,
минимальная заработная плата сотрудников (это данное поместите в отдельную
ячейку), стаж работы, сумма продажи товаров каждым сотрудником.
Рассчитать: надбавку за стаж (если Стаж меньше 3 лет, то равна 0, иначе равна
20 % от минимальной заработной платы), размер комиссионного вознаграждения:
если сумма продаж меньше 20 000 руб., то комиссионные составляют 10 % от
этой суммы, если больше 20 000, но меньше 30 000, то 20 %, а если больше 30
000, то 30 %. Найти суммарную заработную плату каждого продавца и всего
отдела.
Вариант 5. Для жильцов каждой из пяти квартир известен расход
электроэнергии (в кВт/ч) за один месяц (например, 200, 250, 300). Компания по
снабжению электроэнергией взимает плату с клиентов по тарифу 1,2 руб. за 1
кВт/ч. (это данное поместите в отдельную ячейку). Средняя норма расхода
электроэнергии 250 кВт/ч (это данное поместите в отдельную ячейку). Рассчитать
плату за каждый кВт/ч сверх нормы по следующей схеме: если расход за месяц
был меньше 250 кВт/ч (т. е. меньше средней нормы), доплаты нет, если расход от
250 до 300 кВт/ч, доплата составляет 1,5 руб. на каждый перерасходованный
кВт/ч, если расход от 300 до 400 кВт/ч, доплата составляет 1,6 руб., если расход
больше 400 кВт/ч, доплата составляет 1,7 руб. Высчитать сумму оплаты за
электроэнергию для каждого клиента и для всей группы жильцов.
Вариант 6. Для группы сотрудников из пяти человек известны: фамилии,
оклад, стаж работы, количество отработанных дней в месяце, а также
минимальный размер заработной платы (это данное поместите в отдельную
ячейку). Начисления составляют: Начислено по окладу (оклад умножить на

37
Отработано дней и разделить на количество рабочих дней в месяце), Надбавка за
стаж (если Стаж меньше 3 лет, то надбавка равна 0, если от 3 до 5 лет, то 10 % от
Начислено по окладу, если от 5 до 7 лет, то 15 % от Начислено по окладу, иначе
равна 20 % от Начислено по окладу), Районный коэффициент (30 % от Начислено
по окладу). Итого начислено = Начислено по окладу + Надбавка за стаж +
Районный коэффициент. Удержания составляют: Профсоюзные взносы (1 % от
Итого начислено), Подоходный налог (если Итого начислено больше
минимальной заработной платы, то вычисляется по формуле: Итого начислено ×
13 %, в противном случае равен 0). Рассчитать сумму удержаний и сумму к
выдаче.
Вариант 7. Для жильцов каждой из пяти квартир известен расход
водоснабжения (в м3) за холодную и горячую воду за один месяц (например, 3, 4,
5). Компания по водоснабжению взимает плату с клиентов по тарифу 8 руб. за 1
м3 за холодную воду и 62 руб. за 1 м3 за горячую воду. Средняя норма суммарного
расхода воды 10 м3 (это данное поместите в отдельную ячейку). Рассчитать
плату за каждый м3 сверх нормы по следующей схеме: если расход за месяц был
меньше 10 м3 (т. е. средней нормы), доплаты нет, если расход от 11 до 12 м3,
доплата составляет 1,5 руб. на каждый перерасходованный м3, если расход от 13
до 14 м3, доплата составляет 1,6 руб., если расход больше 14 м3, доплата
составляет 1,7 руб. Вычислить плату за водоснабжение для каждого клиента и
для всей группы жильцов.
Вариант 8. Для пяти арендаторов известны: название организации-
арендатора, площадь арендуемого помещения (в м2), минимальная оплата за
аренду (это данное поместите в отдельную ячейку). Составить таблицу расчета
оплаты за аренду помещений в зависимости от площади с учетом поправочного
коэффициента, используя формулу: Минимальная плата за аренду × Площадь +
Минимальная плата за аренду × Коэффициент. Если арендуемая площадь меньше
100 м2, то коэффициент равен 0,5, если арендуемая площадь больше, чем 100 м2,
но не превышает 200 м2, то коэффициент равен 0,7, если площадь более 200 м2,
коэффициент – 0,8.
Вариант 9. Продуктовый склад отпускает муку и сахар для предприятий по
оптовым ценам в зависимости от объема закупок. Для муки используются
следующие цены: более 10 000 кг – по 12 руб. за 1 кг, от 5 000 до 10 000 кг – по 13
руб. за 1 кг, от 1000 до 5000 кг – по 14 руб. за 1 кг, менее 1000 кг – по 15 руб. за 1
кг. Для сахара: более 5000 кг – 13 руб. за 1 кг, от 3000 до 5000 кг – по 14 руб. за 1
кг, от 1000 до 3000 кг – по 16 руб. за 1 кг, менее 1000 кг – по 17 руб. за 1 кг. Пять
предприятий города закупили муку и сахар в определенном объеме. Рассчитать
стоимость закупленных продуктов и общую стоимость для каждого
предприятия.
Вариант 10. При продаже квартир строительной компанией цена одного
квадратного метра площади жилья рассчитывается следующим образом: если
квартира однокомнатная, то стоимость одного квадратного метра составляет 41
500 руб., если двухкомнатная – 40 000 руб., если трехкомнатная – 39 500 руб.
Стоимость квадратного метра квартиры, имеющей больше 3 комнат, составляет
38 500 руб. Каждая из квартир имеет балкон определенной площади, стоимость

38
которой рассчитывается как Стоимость одного квадратного метра (данной
квартиры) × Коэффициент. Известны данные о пяти квартирах. Рассчитать
общую стоимость каждой квартиры. Коэффициент, равный 0,5, поместите в
отдельную ячейку.

ЛАБОРАТОРНАЯ РАБОТА № 8

ПАКЕТ АНАЛИЗА

MS Excel имеет набор встроенных средств, позволяющих проводить анализ

статистических, научных и финансовых данных - Пакет анализа, являющийся
надстройкой электронной таблицы.
Надстройки − это специальные программы, расширяющие набор команд и
добавляющие специальные возможности. Для подключения Пакета анализа или
другого расширения на вкладке Файл → Параметры → Надстройки.
Далее из списка Управление, расположенного в нижней части окна
параметров MS Excel, выбирают Надстройки Excel и нажимают кнопку Перейти.
В окне Надстройки (рис. 8.1.) устанавливают флажок в требуемой строке и
нажимают OK. После этого команда вызова расширения добавляется на вкладку
Данные в группу Анализ.

Рис. 8.1. Выбор в Надстройках Пакет анализа

Далее средства Пакета анализа, а их около двадцати, вызывают командой
Данные → Анализ → Анализ данных, выбирают требуемый инструмент и
нажимают OK.
Инструменты Пакета анализа
1. Генерация случайных чисел используется для заполнения диапазона
случайными числами согласно известному закону распределения вероятностей.
Применяется при моделировании объектов и явлений случайного характера, в том
числе из области экономических исследований.
Способ применения

39
 1. Вызвать инструмент Генерация случайных чисел: Данные → Анализ
данных → Генерация случайных чисел.
2. В отобразившемся диалоговом окне указать Число переменных −
количество столбцов случайных чисел.
3. Задать Число случайных чисел − количество чисел в каждом столбце.
4. Выбрать Распределение и его Параметры.
5. Указать параметры вывода − место, куда следует поместить случайные
числа, например, новый рабочий лист или выходной интервал (достаточно указать
верхнюю левую ячейку итогового диапазона).
В MS Excel можно задать следующие типы распределения:
- равномерное − характеризуется нижней и верхней границами интервала,
для которого случайные значения извлекаются с одной и той
же вероятностью;
- нормальное − плотность вероятности
характеризуется двумя параметрами: а − математическое
ожидание (среднее), σ − среднее квадратичное отклонение (стандартное
отклонение);
- Бернулли − для двух вероятных исходов (0 или 1). Характеризуется
вероятностью успеха (величина p) в данной попытке;
- биномиальное − определяется вероятностью
появления некоторого события в n испытаниях с
двумя возможными исходами, вероятности наступления которых p и (1−p)
постоянны, ровно k раз (0 ≤ k ≤ n). Характеризуется вероятностью успеха
(величина p) для n попыток;
- Пуассона − вероятность массовых (значение n велико)
и редких (р − мало) событий, причем np = λ − значение
постоянное. Часто используется для характеристики числа случайных событий,
происходящих в единицу времени. Характеризуется значением λ (лямбда);
- модельное − распределение неслучайных чисел на интервале с некоторым
шагом. Характеризуется нижней и верхней границами интервала, шагом, числом
повторений значений и числом повторений последовательности;
- дискретное − в котором исходный диапазон значений случайной величины
и их вероятностей задается пользователем (в два столбца). Сумма вероятностей
должна быть равна 1.
2. Описательная статистика служит для создания статистического отчета,
содержащего информацию об основных статистических характеристиках,
тенденции и изменчивости входных данных.
Способ применения
1. Вызвать инструмент Описательная статистика: Данные → Анализ
данных → Описательная статистика.
2. В отобразившемся диалоговом окне надо указать Входной интервал −
диапазон исходных данных.
3. Задать Параметры вывода, например, новый рабочий лист или выходной
интервал.
4. Выбрать статистические параметры Итоговая статистика.

40
 3. Гистограмма − это столбчатая диаграмма (ступенчатая фигура,
состоящая из прямоугольников), используемая для иллюстрации статистического
распределения выборки.
В гистограмме по оси абсцисс откладываются интервалы длины h
(необязательно одинаковой), которые называют карманами.
По оси ординат откладывают отрезки на расстоянии n i \ h от оси абсцисс, где
n i − сумма частот значений (вариант) выборки объемом n, попадающих в i-й
интервал. Таким образом, площадь i-го прямоугольника равна n i , а площадь всей
гистограммы − объему выборки.
Способ применения
1. Подготовить диапазон исходных данных (выборку).
2. Создать интервалы карманов (необязательно). Карманы разной длины
следует расположить по возрастанию. Карманы одинаковой длины создают
маркером автозаполнения.
3. Вызвать инструмент: Данные → Анализ данных → Гистограмма.
4. В диалоговом окне Гистограмма выбрать входной интервал.
5. Указать интервал карманов.
6. Задать Параметры вывода (новый рабочий лист или выходной интервал).
7. Выбрать параметры гистограммы - Вывод графика.
Порядок выполнения работы
1. Загрузите MS Excel.
2. Переименуйте листы:

3. Перейдите на лист Равномерное.

4. Подключите Пакет анализа: Файл → Параметры → Надстройки →
Надстройки Excel → Перейти… → Пакет анализа
5. Выполните следующий пример.
Сгенерировать столбец из 100 случайных чисел, равномерно
распределенных на интервале от 0 до 20. Получить статистические
характеристики сгенерированных данных с помощью инструмента
Описательная статистика. Подтвердить правильность результатов
вычислением значений
соответствующих
статистических
функций. Построить
гистограмму,
иллюстрирующую статистическое распределение сгенерированных данных.
1. Выполним Данные → Анализ данных → Генерация случайных чисел, OK.
2. Заполним окно:

41
 3. Параметры распределения укажем от 0 до 20.
4. Поле Случайное рассеивание, куда вводится произвольное значение,
которое позже можно использовать для получения тех же самых случайных
чисел, оставим пустым.
5. Выходной интервал укажем $A$1.
6. Нажмем OK: 100 случайных величин сгенерировано.
7. Округлим полученные числа до десятых: в ячейку B1 введем формулу
=ОКРУГЛ(A1;1)
и скопируем её на диапазон $B$1:$B$100, используя автозаполнение:

8. Для получения статистических характеристик совокупности данных

выполним Данные → Анализ данных → Описательная статистика, OK.
9. Входной интервал укажем $B$1:$B$100, а выходной интервал − $D$1.
10. Установим флажок в строке Итоговая статистика и нажмем OK.
11. Выполним проверку правильности статистического анализа, используя
соответствующую статистическую функцию, как показано на рис. 8.2.

Рис. 8.2. Итоговая статистика и статистические функции.

12. Проиллюстрируем статистическое распределение данных.
13. Создадим интервал карманов: в ячейке H1 напечатаем «Карманы», а
ячейки H2:H12 заполним числами 0, 2, …, 20. Для более информативной
визуализации данных верхнюю и нижнюю границы интервала карманов следует
выбирать, опираясь на максимальное и минимальное значения переменной.
14. Выполним Данные → Анализ данных → Гистограмма.
15. Укажем входные данные:

42
 Опцию Метки следует выбирать, если заданные интервалы включают
названия диапазонов данных.
16. Выберем Выходной интервал, например, $D$18.
17. Укажем галочкой Вывод графика и нажмем OK. Результат построения
Гистограммы представлен на рис. 8.3.

Рис. 8.3. Графическая интерпретация распределения данных

18. На листах Нормальное, Биномиальное, Пуассона выполните задание

своего варианта из таблицы 8.1.
Таблица 8.1.

43
 БИБЛИОГРАФИЧЕСКИЙ СПИСОК

1. Волков, В. Понятный самоучитель Excel 2010 / В. Волков. – СПб. : Питер,

2010. – 256 с.
2. Голдобина Т. А. Экономические расчеты в MS Excel 2010: практикум / Т.
А. Голдобина; М-во трансп. и коммуникаций Респ. Беларусь, Белорус. гос. ун-т
трансп. – Гомель: БелГУТ, 2016. – 65 с.
3. Долженков, В. Microsoft Excel 2010. В подлиннике / В. Долженков, А.
Стученков. – СПб. : БХВ-Петербург, 2011. – 816 с.
4. Иванов, И. Microsoft Excel 2010 для квалифицированного пользователя /
И. Иванов. – М.: Академия АЙТИ, 2011. – 244 с.
5. Михаэлис С.И. MS Excel 2010: лабораторный практикум / С.И. Михаэлис.
– Иркутск: ИрГУПС, 2013. – 76 с.
6. Пташинский, В. Excel 2010 для начинающих / В. Пташинский. – М. :
Эксмо, 2013. – 288 с.
7. Работа с электронными таблицами Microsoft Excel 2010: методические
указания к выполнению лабораторных работ по информатике для обучающихся
по всем программам бакалавриата и специалитета дневной формы обучения /
сост. Н. Д. Берман, В. В. Стригунов, Н. И. Шадрина. – Хабаровск: Изд-во
Тихоокеан. гос. ун-та, 2014. – 36 с.
8. Сергеев, А. Microsoft Office 2010. Самоучитель / А. Сергеев. – М. :
Вильямс, 2010. – 624 с.
9. Сурядный, А. Microsoft Office 2010 / А. Сурядный. – М.: АСТ: Астрель,
2011. – 640 с.
10. Фрай, К. Д. Microsoft Office 2010. Русская версия / К. Д. Фрай, Дж. Кокс,
Дж. Ламберт. – М. : ЭКОМ Паблишерз, 2011. – 800 с. – (Серия «Шаг за шагом»).

44