Дополнительные возможности Excel.
Анализ данных. Формирование больших документов
Закрепление областей
В больших документах существует потребность видеть заголовки при пролистывании документа. Для этого в MS Excel предусмотрена команда Закрепить области. Для закрепления заголовков таблицы необходимо поместить курсор в ячейку под строкой заголовков, выбрать главное меню ВИД / Окно/ Закрепить области и выбрать тип закрепления. Для снятия закрепления выбрать ВИД /Окно/ Закрепить области /Снять закрепление областей.
Задание 1. Создайте книгу «Анализ данных». На листе «1Счет» создать заголовки таблицы – Дата, Счет, Сумма, Тип, Отделение.
Закрепите область заголовков таблицы.
В MS Excel применяется несколько способов автозаполнения таблиц:
(1) Копирование содержимого ячейки в смежные, используя способ перетаскивания. Для этого выделяется ячейка (или несколько смежных ячеек) содержимое которой нужно скопировать, в правом нижнем углу ячейки (или группы ячеек) находим изображение указателя мыши в виде 
черного крестика. Удерживая левую клавишу мыши нажатой, перемещаем ее в нужном направлении (по столбцу или по строке).
Перемещение мыши выполняют без клавиши < Ctrl> клавиатуры или удерживая эту клавишу. При этом происходит заполнение выделенного интервала либо копиями данных, расположенных в активной ячейке, либо некоторой последовательностью с изменением этих данных. Способ заполнения интервала зависит от того, удерживается ли клавиша < Ctrl>. Об этой зависимости позднее будет рассказано подробнее.
Прием автозаполнения широко используется при создании электронных таблиц, поскольку позволяет значительно ускорить процесс ввода данных и повысить надежность работы.
(2) Ввод одного значения в диапазон ячеек листа (Выделите ячейки, в которые необходимо ввести данные. Они не обязательно должны быть смежными. Введите данные и нажмите клавиши < Ctrl+Enter>).
(3) Автоматическое заполнение повторяющихся записей в столбце.
Если несколько первых знаков, вводимых в ячейку, совпадают со знаками записи, ранее введенной в этом столбце, то недостающая часть набора будет произведена автоматически.
В MS Excel автоматический ввод производится только для тех записей, которые содержат текст или текст в сочетании с числами. Записи, полностью состоящие из чисел, дат или времени, необходимо вводить самостоятельно.
Для подтверждения предлагаемого варианта, нажмите клавишу < Enter >. Законченная запись по формату полностью совпадает с существующей записью, включая знаки верхнего и нижнего регистра.
Для замены автоматически введенных знаков продолжите ввод самостоятельно.
Для удаления автоматически введенных знаков нажмите клавишу <← BACKSPACE>.
Задание 2. На листе «2Списки» Введите в ячейки А1-А4 список дисциплин. В ячейку А5 введите первую букву уже имеющейся в списке дисциплины. Excel предлагает записанный ранее вариант.
Чтобы выбрать значение из списка записей, которые уже имеются в столбце, нажмите правую кнопку мыши, а затем выберите в контекстном меню команду Выбрать из раскрывающегося списка. Выберите другую дисциплину.
В Excel уже хранятся зарезервированные стандартные последовательности слов, например, последовательность дней недели (понедельник, вторник, воскресение или пн., вт., …, вс), последовательность месяцев года (январь, февраль, …, декабрь). Эти последовательности называются списками.
Например, в таблице ниже приведены примеры продолжения начальных значений. Элементы, разделенные запятыми, находятся в соседних ячейках.
| Начальное значение | Продолжение ряда |
| 1, 2, 3 | 4, 5, 6... |
| 09:00 | 10:00, 11:00, 12:00... |
| пн | вт, ср, чт... |
| понедельник | вторник, среда, четверг... |
| янв | фев, мар, апр... |
| янв, апр | июл, окт, янв... |
| янв-99, янв-99 | июл-99, окт-99, янв-00... |
| 15-янв, 15-янв | 15-июл, 15-окт... |
| 1999, 2000 | 2001, 2002, 2003... |
| 1-янв, 1-мар | 1-май, 1-июл, 1-сен... |
| кв.3 (или квартал3) | кв.4, кв.1, кв.2... |
| текст1, текстA | текст2, текстA, текст3, текстA... |
| 1-й период | 2-й период, 3-й период... |
| товар 1 | товар 2, товар 3... |
Чтобы заполнить столбец пользовательским списком необходимо в первую ячейку будущего списка ввести какой-либо элемент списка (например слово «май») и произвести автозаполнение (без удерживания < Ctrl>) остальных пустых ячеек будущего списка. При автозаполнении происходит циклическое заполнение интервала последовательными элементами списка.
Сделайте это в ячейках В1-В5:
| Май |
| Июнь |
| Июль |
| Август |
Замечание. При автозаполнении можно буксировать не только отдельную активную ячейку, но и выделенный интервал. Например, при буксировке за значок автозаполнения выделенной строки таблицы происходит автозаполнение сразу нескольких столбцов.
Ввод данных из раскрывающегося списка в ячейку
Можно создать раскрывающийся список допустимых значений, который будет доступен из любой помеченной ячейки на листе. А затем вводить значения путем выбора из этого списка.
Этот метод создания списков обычно используется, если существует необходимость контроля ввода данных.
1. Введите данные для будущего раскрывающегося списка в один произвольный удалённый столбец рабочего листа. Не включайте в список пустые ячейки.
2. Выберите ячейку, в которую требуется поместить раскрывающийся список.
3. По пути ДАННЫЕ / Работа с данными / Проверка данных выберите команду Проверка данных… а затем, в открывшемся окне откройте вкладку Параметры.
4. В строке Тип данных выберите «Список».
5. Далее укажите ссылку на написанный Вами список в поле Источник
Если списка нет, то можно ввести его поле Источник с клавиатуры через точку с запятой «;».
6. Установите флажок «Список допустимых значений».
7. Определите, может ли ячейка оставаться пустой, для чего установите или снимите флажок «Игнорировать пустые ячейки».
8. Для отображения дополнительных инструкций по вводу при выделении ячейки, перейдите на вкладку Сообщение и установите флажок «Отображать подсказку, если ячейка является текущей», после чего введите заголовок и текст для сообщения.
9. Определите способ, которым Microsoft Excel будет сообщать о вводе неправильных данных:
1. Перейдите на вкладку Сообщение об ошибке и установите флажок Выводить сообщение об ошибке.
2. В поле Вид выберите один из следующих параметров.
Для отображения информационного сообщения, не запрещающего ввод неправильных данных, выберите значение «Сообщение».
Для отображения предупреждения, не запрещающего ввод неправильных данных, выберите значение «Предупреждение».
Чтобы запретить ввод неправильных данных, выберите значение «Останов».
3. Введите заголовок и текст для сообщения (до 225 знаков).
Примечание. Если заголовок и текст не введены, по умолчанию вводится заголовок «Microsoft Excel » и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен »
Затем в рабочей области в указанные ячейки можно вводить данные только путем выбора из заданного списка.
Примечание. Применение проверки вводимых в ячейку значений не приводит к форматированию ячейки.
Задание 3. На листе «2Списки» В столбце D создайте список наименований 4х видов одежды и заполните им с помощью функции автозаполнение 8 ячеек.
Рядом - в столбце E - организуйте ввод данных из раскрывающегося списка в ячейку: источник данных заполните в столбце Н1-Н3 категориями одежды: дет, жен, муж.
(4) Режим Прогрессия
MS Excel уже может автоматически продолжать заполнение прогрессии числами, комбинациями чисел и текста, датами и временем, основываясь на установленном образце.
Для заполнения с помощью прогрессии необходимо
1. ввести начальное значение диапазона.
2. 
Можно выделить диапазон ячеек, начиная с начального значения,
3. Выбрать на вкладке ГЛАВНАЯ/Редактирование / кнопку Заполнить / команду Прогрессия.
4. В диалоговом окне указать параметры прогрессии:
- Расположение (по строкам или по столбцам)
- Шаг прогрессии (указывается целым числом)
- Тип прогрессии (арифметическая, геометрическая, даты, автозаполнение)
- Для типа Даты также указываются единицы измерения (день, рабочий день, месяц, год)
- Если не выделен диапазон ячеек (см.п.2), определите предельное значение, до которого следует продолжать заполнять ячейки с помощью функции Прогрессия.
Задание 4.
3.1. На листе «2Списки» Введите в ячейку A10 любое число (не обязательно целое). Методом перетаскивания заполните несколько следующих ячеек этого столбца числовыми значениями. Обратите внимание на то, что все ячейки будут заполнены копией числа из активной ячейки A10.
3.2. В ячейку B10 введите то же самое число. Удерживая клавишу <Ctrl>, заполните аналогичное количество строк методом перетаскивания. При этом столбец B заполнится возрастающей последовательностью чисел с шагом 1 (это значение установлено для режима Прогрессия по умолчанию).
В Ячейку С10 введите то же самое число. Заполните ячейки С11-С13 в режиме Прогрессия геометрическая с шагом 2.
Иначе обстоит дело с текстом, содержащим числовые значения. Общее правило в этом случае таково: независимо от использования клавиши < Ctrl> интервал ячеек заполняется копиями текста из активной ячейки. Однако если текст начинается цифрами, при буксировке удерживания < Ctrl> происходит заполнение последовательностью с изменением значения числа.
3.3. В ячейку D10 введите текст Цех 1 и скопируйте содержимое ячейки на некоторый диапазон ячеек этого столбца. В этом случае столбец D примет вид, как на рисунке слева. Снова сделайте активной ячейку D10 и повторите процесс автозаполнения, удерживая клавишу <Ctrl>. Столбец будет теперь заполнен повторяющимся текстом «Цех 1» (см. рис.-правый столбец).
3.4. Введите в ячейку F10 любую дату или любое время. Обратите внимание на то, что без удерживания клавиши <Ctrl> интервал заполняется последовательными датами или последовательностью моментов времени.
 |
При удержании клавиши <Ctrl> интервал заполняется копиями даты или времени.
Т.о. если текст заканчивается цифрами, заполнение последовательностью с изменением значения числа происходит без удерживания <Ctrl>.
Задание 5. На листе «1Счет» в созданной таблице:
Заполните столбец Дата с помощью команды Заполнить — Прогрессия – с 01.10.17 по 31.10.17 рабочими днями. Для этого введите в ячейку A2 дату 01.10.17, затем выделите столбец A, начиная с первой даты. Выполните команду Заполнить ¾ Прогрессия. В диалоговом окне Прогрессия установите параметры как предложено на рисунке и нажмите кнопку OK.
Должно получиться 23 записи.
Заполните столбец Счет последовательными числами начиная со 101, используя способ перетаскивания с удерживанием <Ctrl>.
Заполните Суммы для всех записей путем ввода с клавиатуры.
Заполните столбец Тип из списка допустимых значений следующими значениями: депозит; текущий; срочный; на предъявителя.
Заполнитt столбец Отделение из пользовательского списка в Ячейку (см.задание 3) следующими значениями: центральное; северное; западное.
Примечание. Меню: ДАННЫЕ / Работа с данными/Проверка данных используют, если требуется вводить данные из раскрывающегося списка в произвольном порядке.
Работа с таблицами как с базой данных
Сортировка данных в таблице Excel представляет собой упорядочение данных по заданному признаку. Для сортировки необходимо выделить область исходных данных (всю таблицу*), выбрать вкладку Данные/ Сортировка. В диалоговом окне указать параметры сортировки – поля для сортировки, порядок сортировки (по возрастанию или по убыванию).
Фильтрация данных представляет собой выборку из всего массива данных только тех записей, которые удовлетворяют заданному условию. Для установления фильтров необходимо выделить заголовки таблицы, выбрать в главном меню на вкладке Данные/ Фильтр. Справа от каждого заголовка появится значок раскрывающегося списка для ввода условий фильтрации. 
Рассмотрите в раскрывающемся списке условия фильтрации и сортировки. Вот некоторые команды:
- ВСЕ – показать все строки таблицы,
- Числовые фильтры/ПЕРВЫЕ 10 – показать только первые 10 строк таблицы,
- УСЛОВИЯ: Больше, Меньше, и др. – позволяют создать пользовательский фильтр, в котором задать одно или несколько сложных условий для выборки данных.
- А также содержатся команды сортировки данных.
Если в условии пользовательского фильтра указать «больше», а значение оставить таким же то, в таблице останутся только те строки, в которых дата больше 01.01.07, а остальные строки будут скрыты.
Промежуточные итоги
Функция Промежуточные итоги предполагает автоматическое вычисление промежуточных итогов. Для этого необходимо
1) отсортировать данные по тому полю, которое будет являться группировочным признаком.
2) Выделить всю таблицу.
3) На вкладке ДАННЫЕ выбрать команду Промежуточный итог.
В диалоговом окне представлено несколько операций:
- ПРИ КАЖДОМ ИЗМЕНЕНИИ В – выбрать поле, по которому сортировались данные;
- ОПЕРАЦИЯ – из набора функций выбрать Сумма;
- 
ДОБАВИТЬ ИТОГИ ПО – проставить флажок в том поле, по которому должны быть посчитаны итоги.
Обратите внимание! До выполнения команды Промежуточный итог данные необходимо отсортировать по тому полю, по которому затем должна быть проведена группировка.
После выполнения этой операции будет изменена структура исходной таблицы. Итоги отображаются с иерархической детализацией: только Общий итог ® Общий + Промежуточные итоги® Общий+Промежуточный+Исходные данные.
Если в дальнейшем использование этой команды не требуется, то итоги нужно снять, выполнив команду вкладка Данные/ Промежуточный итог/ кнопка <Убрать все>.
Задание 6. Отсортировать данные таблицы, созданной в задании 1,5 следующим образом:
а) по полю Дата – по убыванию
б) по полю Сумма – по возрастанию
в) по двум полям одновременно: сначала по полю Тип – по убыванию, затем Дата – по возрастанию
Отфильтровать данные следующим образом (при переходе к очередному пункту задания снимайте предыдущий фильтр):
а) по полю Отделение – значение «Центральное »;
б) по полю Тип – значение «Депозит »;
в) по полю Дата – условие «больше 20.05.11 »;
г) по полю Счет – условие «меньше 110 »;
д) по полю Сумма – условие «меньше или равно 500 »;
е) Снять все фильтры.
Вычислить промежуточные итоги, которые отражают суммы средств, содержащихся на счетах каждого типа.
Примечание1: функция «промежуточные итоги» выполняется на базовой таблице, отсортированной только по группировочному признаку (здесь – это поле ТИП). алгоритм формирования этой итоговой таблицы дан выше задания.
Примечание2:В случае, если функция «промежуточные итоги» не доступна, проверьте, есть ли для вашей выделенной таблицы в меню лента Конструктора.
И в ней измените диапазон таблицы на обычный: Работа с таблицами/Конструктор/Сервис/Преобразовать в диапазон.
Сводные таблицы
Сводные таблицы представляют собой динамически создаваемые таблицы итоговых обобщенных данных, с изменяемой структурой таблиц. Создание сводных таблиц реализуется с помощью М астера сводных таблиц и диаграмм, который вызывается через вкладку Вставка/ Сводная таблица.
На первом шагеМастера указывается источник исходных данных. Если при запуске Мастера курсор находился на любом элементе таблицы, то программа автоматически определит диапазон исходных данных и куда поместить сводную таблицу.
На втором шаге формируется макет будущей сводной таблицы, определяются её параметры. Из списка полей таблицы выбираются те, которые нужно поместить в столбцы, строки и по которым провести расчеты.
Макет имеет четыре области:
1) Страница – значения поля, помещенного в эту область, используются в качестве заголовков страниц в сводной таблице.
2) Строка – значения поля, помещенного в эту область, используются в качестве заголовков строк сводной таблицы.
3) Данные – поле, для которого подводятся итоги в сводной таблице.
4) Столбец – значения поля, помещенного в эту область, используются в качестве заголовков столбцов сводной таблицы.
Полученную таблицу можно изменять, используя вновь появившийся раздел меню Работа со сводными таблицами. Или в контекстном меню выбрать раздел «Показать список полей».
Задание 7. На новом листе «3Свод» создать сводную таблицу (на основе формата таблицы, созданной в заданиях 1,5), которая отражает сумму на счетах различного типа в разрезе отделений банка. (Результат приведён в Таблице 1).
Указание: Сводная таблица может быть сформирована правильно только на основе простой таблицы (без функции промежуточные итоги). Поэтому скопируйте таблицу с листа «1Счет» на лист «3Свод» и там снимите все функции с таблицы.
Таблица 1
| Сумма по полю сумма | отделение | |||
| тип | западное | северное | центральное | Общий итог |
| депозит | ||||
| на предъявителя | ||||
| срочный | ||||
| текущий | ||||
| Общий итог |
Создать сводную таблицу, которая позволяет ответить на вопрос, – в каком отделении открыто большее количество счетов? (см. Таблица 2)
Указание: по полю счет Количество возможно будет запросить, если некоторые ячейки столбца «Счёт» будут содержать либо буквы (А101) либо доли (107,2)
Чтобы сформировать «Количество по полю..» в макете сводной таблицы на поле Значения
нажмите стрелочку и выберите в «Параметры поля значений» операцию Количество.
Таблица 2
| Количество по полю счет | отделение | |||
| тип | западное | северное | центральное | Общий итог |
| депозит | ||||
| на предъявителя | ||||
| срочный | ||||
| текущий | ||||
| Общий итог |
Если Вы вносите изменения в таблицу, то для отображения изменений в сводной таблице необходимо её обновить: правой мышкой – по верхней левой ячейке св.таблицы, в меню выбрать «обновить».
Задание 8.
Создайте на Листе «4БытТех» таблицу по образцу.
| Дата | Фирма | Товар | Кол |
| 3.06 | Бытовая автоматика | телевизоры | |
| 4.06 | ТД ″Молодежный″ | телевизоры | |
| 4.06 | Электроника | магнитофоны | |
| 5.06 | Бытовая автоматика | видеоплейеры | |
| 5.06 | Электроника | магнитофоны | |
| 10.06 | Электроника | видеоплейеры | |
| 11.06 | Бытовая автоматика | телевизоры | |
| 12.06 | ТД ″Молодежный″ | телевизоры | |
| 13.06 | Электроника | магнитофоны | |
| 14.06 | Бытовая автоматика | видеоплейеры | |
| 15.06 | Электроника | магнитофоны | |
| 16.06 | Электроника | видеоплейеры | |
| 17.06 | Бытовая автоматика | телевизоры | |
| 18.06 | ТД ″Молодежный″ | телевизоры | |
| 19.06 | Электроника | магнитофоны | |
| 20.06 | Бытовая автоматика | видеоплейеры | |
| 21.06 | Электроника | магнитофоны | |
| 22.06 | Электроника | видеоплейеры | |
| 23.06 | Бытовая автоматика | телевизоры | |
| 24.06 | ТД ″Молодежный″ | телевизоры | |
| 25.06 | Электроника | магнитофоны | |
| 26.06 | Бытовая автоматика | видеоплейеры | |
| 27.06 | Электроника | магнитофоны | |
| 28.06 | Электроника | видеоплейеры | |
| 29.06 | Бытовая автоматика | телевизоры |
При помощи автофильтра отберите сведения о фирме «Бытовая автоматика ». В первой строке таблицы установятся стрелки. Раскройте список Фирма и выберите фирму «Бытовая автоматика ». В таблице будет выбрана вся информация по этой фирме.
Вернемся к исходной таблице. Для этого раскроем список Фирма и выберем строчку Все. Отберите сведения о магнитофонах, проданных фирмой «Электроника ». Для этого выберите сначала фирму «Электроника », затем товар «магнитофоны ».
Откройте все раскрывающиеся списки, выделенные синим треугольником и укажите Все. Мы вернемся к исходной таблице.
Теперь откройте список в ячейке Количество и выберите строчку Числовые фильтры/ Больше или равно. В открывшемся окне Пользовательский автофильтр введите условие как на рисунке справа. < OK>. Скопируйте отсортированные данные на отдельный листок рабочей книги.
Вернитесь к исходной таблице и отмените автофильтр. Построим по этой таблице круговую диаграмму общего объема продаж по фирмам с указанием категории и доли.
Для этого сначала отсортируем данные таблицы и подведем итоги по количеству товара каждого наименования.
Выполним сортировку по столбцам «Фирма » и «Товар ». Выделим все содержимое таблицы и выполним команду Данные — Сортировка. В окне Сортировка диапазона укажите в ячейке «Сортировать по » ¾ «Фирма » и в ячейке «затем по » — «Товар ».
Определим итоговое количество товара каждого наименования, проданного в каждой фирме. Выполните команду Данные — Промежуточные итоги. В окне Промежуточные итоги в ячейке «При каждом изменении в » укажите «Фирма », в ячейке «Операция » — «Сумма », «Добавить итоги по » — «Количество ». < OK>.
В результате выполненных действий таблица отсортировалась по товарам и фирмам. В левом поле закроем все уровни, кроме первого. Остались записи с названием фирм и объемом реализованного товара.