Сортировка списков
Сортировка списков предназначена для упорядочивания данных в строках и столбцах таблицы по определенным параметрам. При обработке больших объемов данных это облегчает работу со списками. В MS Excel имеется возможность сортировки данных по одному, двум, трем и четырем столбцам (строкам); по возрастанию и убыванию; по месяцам и дням недели.
Можно создавать и собственные списки для сортировки. При сортировке предварительно выделяется диапазон данных и во вкладке Данные в группе Сортировка и фильтр выбрать Сортировка. Затем после выбора столбца в поле Порядок необходимо выбрать значение Настраиваемый список. После создания пользовательского списка применяем порядок сортировки.
Фильтрация данных
Фильтрация данных предназначена для быстрого поиска подмножества данных и работы с ними в списке. В отфильтрованном списке отображаются только строки, отвечающие условиям отбора, заданным для столбца. В отличие от сортировки списков при фильтрации данных порядок записей в списке не изменяется. При фильтрации временно скрываются строки, которые не требуется отображать.
|
|
Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядка строк и не перемещая их.
В MS Excel фильтрация данных реализуется двумя командами – автофильтром и расширенным фильтром.
Автофильтр применяется для простых условий отбора. При использовании команды Автофильтр предварительно выделяется диапазон данных, на который будут накладываться условия и во вкладке Данные в группе Сортировка и фильтр выбрать Фильтр. В фильтруемом списке появляются стрелки автофильтра, благодаря которым задаются условия отбора по каждому столбцу.
Расширенный фильтр применяют для более сложных условий отбора. Командой Дополнительно во вкладке Данные в группе Сортировка и фильтр в отдельном диапазоне критериев списка вводится условие, в соответствии с которым требуется произвести фильтрацию. С помощью расширенного фильтра, к примеру, можно задавать более двух наборов условий для одного столбца. Как правило, условия отбора задают в отдельных ячейках листа, а затем в диапазоне критериев расширенного фильтра указывают лишь ссылки на адреса этих ячеек.
С подробной информацией о сортировке и фильтрации можно ознакомиться в справке MS Excel.
Задание к работе
1) Построить таблицу по образцу табл. 4.1.
2) Отсортировать табл. 4.1 по графе «Образование», организовав пользовательский список: «неполное высшее», «высшее», «среднее», а затем – по социальной группе и возрасту.
|
|
3) Отсортировать табл. 4.1 по графе «Социальная группа» по алфавиту.
4) Выбрать записи, относящиеся к предпринимателям, которые могут тратить от 4 000 до 8 000 руб.
5) Выбрать записи, относящиеся к данным о пенсионерах, готовых тратить больше 1000 руб., и о студентах, готовых тратить больше 2000 руб.
6) Выбрать записи, относящиеся к мужчинам от 18 до 25 лет и к женщинам от 26 до 35 лет.
7) Выбрать записи с информацией о лицах, имеющих покупательные возможности больше среднего значения.
Таблица 3.1
Информация о потребителях рынка одежды
Пол | Возраст | Социальная группа | Покупательные возможности, руб. | Образование | Средний срок носки выходной одежды | Средний срок носки повсед-невной одежды | Покуп-ка в «секонд- хэде» |
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
М | 18 – 25 | Студент | 1 400 | Неполное высшее | 1 | 1 | Нет |
М | 26 – 35 | Предприниматель | 6 000 | Высшее | 1 | Менее года | Нет |
Ж | 18 – 25 | Студент | 1 600 | Среднее | 1 | 1 | Да |
Ж | 26 – 35 | Безработный | 500 | Среднее | 3 | 2 | Да |
М | 18 – 25 | Предприниматель | 13 000 | Среднее | 1 | 1 | Нет |
М | 46 – 56 | Рабочий | 3 500 | Среднее | 3 | Более трех лет | Да |
М | 36 – 45 | Предприниматель | 13 000 | Непоное высшее | Менее года | Менее года | Да |
Ж | 56 – … | Пенсионер | 1 300 | Среднее | 1 | Более трех лет | Да |
Ж | 26 – 35 | Служащий | 3 500 | Высшее | 2 | 1 | Да |
М | 26 – 35 | Предприниматель | 8 000 | Высшее | 1 | 1 | Нет |
Ж | 36 – 45 | Безработный | 500 | Высшее | 3 | 3 | Да |
М | 36 – 45 | Рабочий | 2 600 | Среднее | 3 | 3 | Да |
М | 18 – 25 | Рабочий | 2 400 | Высшее | 3 | 3 | Да |
Ж | 26 – 35 | Служащий | 3 300 | Высшее | 2 | 2 | Да |
Ж | 26 – 35 | Служащий | 3 600 | Высшее | 1 | 2 | Нет |
М | 36 – 45 | Госслужащий | 2 200 | Высшее | 2 | 2 | Да |
Ж | 26 – 35 | Предприниматель | 4 600 | Неполное высшее | 1 | 3 | Нет |
М | 46 – 56 | Безработный | 200 | Неполное высшее | 3 | Более трех лет | Да |
Ж | 18 – 25 | Студент | 2 100 | Среднее | 1 | 2 | Нет |
М | 26 – 35 | Безработный | 800 | Среднее | 2 | Более трех лет | Да |
Контрольные вопросы
1) Сортировка каких данных возможна в MS Excel?
2) В чем особенность применения автофильтра?
3) В каких случаях целесообразно применение расширенного фильтра?
Лабораторная работа 4
Консолидация данных и Сводные таблицы в MS Excel
Цель работы: получение навыков решения задач анализа средствами консолидации данных, связывания таблиц и сводных таблиц.
Указания к выполнению лабораторной работы
Часто при решении различных экономических задач возникает необходимость дублирования таблиц с идентичными в них данными либо использования одних и тех же столбцов (строк) в структурно отличающихся друг от друга таблицах. Нередки случаи, когда требуется создание итоговой таблицы по определенному временному периоду на основе нескольких таблиц, например, построение таблицы о результатах прибыли предприятия за год на основе расчетных таблиц по месяцам. В MS Excel имеется ряд инструментов для реализации названных целей.
Связывание таблиц
Простейшим способом связывания диапазонов данных в разных таблицах является метод копирования из таблицы с данными нужного диапазона в буфер обмена, а затем вставки в другую таблицу, которую необходимо связать с искомой. В большинстве случаев рекомендуется осуществлять копирование в связываемую таблицу не данных, а лишь ссылки на ячейки той таблицы, где эти данные находятся. Реализуется данная операция в MS Excel следующим образом: на вкладке Главная в группе Буфер обмена необходимо нажать кнопку со стрелкой под надписью Вставить, далее выберите команду Специальная вставка – Вставить связь.
Консолидация данных
При консолидации данных объединяются значения из нескольких диапазонов данных.
В MS Excel существует несколько способов консолидации данных: использование трехмерных формул, по положению и по категории.
|
|
Использование трехмерных формул является наиболее рациональным методом, который заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Понятие «трехмерные формулы» означает создание формул, которые содержат ссылки на несколько листов. Такие трехмерные ссылки можно использовать в формулах для любого типа и расположения данных. Для применения метода консолидации данных достаточно ввести формулу и включить в нее ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация.
Консолидация по положению предполагает нахождение данных всех исходных областей в одном месте и размещение их в одинаковом порядке. Для упрощения работы с данными рекомендуется диапазонам данных, используемых для консолидации, на всех листах присвоить имена. Осуществить консолидацию по положению можно выбрав в меню на вкладке Данные команду Консолидация. Для связывания с исходными данными требуется выбрать соответствующее поле. После выбора из раскрывающегося списка Функция весовую функцию, которую требуется использовать для консолидации данных, щелкните поле Ссылка, откройте лист, содержащий первый диапазон данных для консолидации, введите имя этого диапазона и нажмите кнопку Добавить. Затем повторите этот шаг для всех диапазонов.
Консолидация по категории производится аналогичнометоду Консолидация по положению. Особенность метода консолидации по категории заключается в консолидации данных с одинаковыми заголовками рядов и столбцов (как и при консолидации по положению), находящихся на разных листах, имеющих различную организацию данных. В этом случае необходимо указывать в исходных диапазонах подписи консолидируемых столбцов (строк) таблиц, а применяя метод консолидации по категории,отметить расположение этих подписей в исходных диапазонах данных. Более подробную информацию о практическом применении методов консолидации данных можно получить в справке MS Excel.
Сводные таблицы
|
|
Отчет сводной таблицы используется в случаях, когда требуется проанализировать связанные итоги, особенно для сравнения нескольких фактов по каждому числу из длинного списка обобщаемых чисел. Благодаря интерактивности отчета сводной таблицы можно изменять представление данных для просмотра дополнительных подробностей или для вычисления других итогов, таких как количество или среднее значение.
В отчете сводной таблицы каждый столбец или поле исходных данных становится полем сводной таблицы, в котором подводятся итоги нескольких строк или столбцов исходной таблицы.
Создать отчет сводной таблицы можно с помощью мастера сводных таблиц и диаграмм, для этого на вкладке Вставка в группе Таблицы выберите пункт Сводная таблица. Мастер предлагает выбрать исходные данные на листе или во внешней базе данных, затем он создает на листе область отчета и предлагает список доступных полей. При перетаскивании полей из окна списка в структурированные области выполняются подведение итогов, автоматическое вычисление и построение отчета.
Задание к работе
1) Создать таблицы по образцу табл. 4.1 на двух разных листах одной книги путем простого связывания их данных, а затем получить тот же результат, расположив таблицы в разных рабочих книгах.
2) Создать отчеты о доходах воображаемой фирмы за три подряд идущих месяца, например, по шаблону вида табл. 4.2 на трех подряд идущих листах одной рабочей книги.
3) На четвертом листе этой же книги консолидировать данные всех трех таблиц, просуммировав доходы фирмы за все месяцы по каждой статье, применив трехмерные формулы.
Таблица 4.1
Оборот продажи книг за 2016 г.
Название книги | Цена книги, руб. | Количество проданных книг, шт. | Сумма продажи, руб. |
Архитектура компьютера | 716 | 5 000 | 3 580 000,00р. |
Microsoft Windows Server 2012. Полное руководство | 2789 | 9 000 | 25 101 000,00р. |
HTML5, CSS3 и JavaScript. | 1660 | 4 000 | 6 640 000,00р. |
Современные операционные системы | 864 | 3 000 | 2 592 000,00р. |
PHP. Объекты, шаблоны и методики программирования | 1045 | 11 000 | 11 495 000,00р. |
Таблица 4.2
Доход фирмы «Орион» за месяц _______
Город | Розничная продажа | Оптовая продажа | Продажа в кредит | Суммарный доход |
Москва | ||||
Омск | ||||
Екатеринбург | ||||
Тюмень |
4) На пятом листе книги просуммировать доходы фирмы за все месяцы по каждой статье, но с помощью консолидации по положению.
5) Создать и заполнить базу данных по образцу табл. 5.3. На другом листе создать сводную таблицу со следующими полями:
- поля «год», «продавец» оформить областью Фильтр отчета;
- поле «объем» оформить областью Названия строк;
- поле «район» оформить областью Названия столбцов;
- поле «товар» оформить областью Значения.
Таблица 4.3
Объем продаж фирмы за отчетный период
Месяц | Год | Продавец | Товар | Район | Объем | Сбыт |
1 | 2 | 3 | 4 | 5 | 6 | 7 |
Январь | 2016 | Иванов | Детектив | Северный | 13 | 106 |
Февраль | 2017 | Петров | Детектив | Южный | 14 | 109 |
Март | 2015 | Сидоров | Детектив | Западный | 12 | 203 |
Январь | 2015 | Иванов | Поэзия | Западный | 15 | 125 |
Апрель | 2016 | Иванов | Поэзия | Восточный | 14 | 206 |
Январь | 2017 | Петров | Детектив | Северный | 12 | 188 |
Июнь | 2015 | Сидоров | Классика | Южный | 11 | 153 |
Март | 2017 | Иванов | Поэзия | Южный | 17 | 186 |
Июль | 2017 | Сидоров | Детектив | Западный | 21 | 124 |
Август | 2016 | Иванов | Классика | Южный | 16 | 120 |
Апрель | 2017 | Петров | Поэзия | Южный | 12 | 181 |
Май | 2015 | Петров | Детектив | Западный | 11 | 147 |
Июнь | 2016 | Иванов | Детектив | Северный | 15 | 133 |
Июль | 2016 | Сидоров | Детектив | Северный | 16 | 169 |
Август | 2015 | Иванов | Детектив | Восточный | 19 | 175 |
Сентябрь | 2016 | Сидоров | Классика | Южный | 20 | 153 |
Октябрь | 2015 | Иванов | Классика | Северный | 14 | 160 |
Июнь | 2017 | Петров | Классика | Восточный | 12 | 193 |
6) Создать таблицу по образцу табл. 4.4.
7) Подсчитать количество сотрудников в каждом отделе.
8) Определить количество иждивенцев в каждом отделе.
Таблица 4.4
Информация о сотрудниках фирмы
Номер п/п | Табельный номер | Фамилия | Имя | Отчество | Дата рождения | Отдел | Должность | Дата приема на работу | Дата увольнения | Пол | Кол-во иждивенцев |
1 | 1 | Иванов | Иван | Иванович | 28.10.1966 | Плановый | Экономист | 10.01.2010 | М | 2 | |
2 | 454 | Иваненко | Иван | Петрович | 21.01.1959 | Бухгалтерия | Бухгалтер | 10.04.2006 | М | 1 | |
3 | 1234 | Петров | Петр | Петрович | 26.08.1994 | Плановый | Секретарь | 21.07.2012 | М | 0 | |
4 | 12312 | Петренко | Петр | Иванович | 14.11.1984 | Маркетинга | Менеджер | 10.10.2008 | М | 1 | |
5 | 12345 | Сидоров | Сидор | Сидорович | 02.02.1985 | Снабжения | Менеджер | 10.01.2009 | 10.10.2012 | М | 0 |
6 | 23456 | Седов | Федор | Фомич | 23.04.1985 | Плановый | Экономист | 12.04.2013 | М | 3 | |
7 | 34567 | Фомин | Фома | Фомич | 12.07.1989 | Плановый | Экономист | 26.07.2012 | М | 1 | |
8 | 45454 | Фоменко | Сидор | Кузьмич | 30.09.1995 | Бухгалтерия | Бухгалтер | 10.11.2008 | М | 1 | |
10 | 45678 | Макова | Алина | Игоревна | 08.03.1976 | Снабжения | Менеджер | 10.04.2014 | Ж | 1 | |
11 | 56565 | Сушкина | Алла | Вадимовна | 17.12.1970 | Плановый | Экономист | 10.07.2009 | 12.12.2013 | Ж | 1 |
12 | 56786 | Кротова | Инна | Павловна | 21.01.1996 | Снабжения | Секретарь | 21.10.2013 | Ж | 0 | |
13 | 56789 | Бойцов | Семен | Семенович | 26.08.1984 | Бухгалтерия | Начальник | 10.01.2007 | М | 1 | |
14 | 67890 | Гайдай | Иван | Фомич | 14.11.1984 | Бухгалтерия | Бухгалтер | 30.04.2010 | М | 1 | |
15 | 78787 | Краснов | Павел | Павлович | 02.02.1985 | Плановый | Начальник | 10.07.2010 | М | 3 | |
16 | 78901 | Рябов | Олег | Евгеньевич | 23.05.1984 | Снабжения | Начальник | 13.10.2011 | М | 1 | |
17 | 89012 | Белова | Софья | Петровна | 12.07.1984 | Плановый | Экономист | 10.01.2006 | Ж | 2 | |
18 | 90123 | Чернова | Зоя | Богдановна | 30.07.1984 | Маркетинга | Начальник | 10.04.2008 | Ж | 2 | |
19 | 98989 | Родионов | Иван | Вадимович | 19.12.1984 | Маркетинга | Секретарь | 10.07.2003 | 30.01.2011 | М | 0 |
20 | 99999 | Хрустов | Юрий | Юрьевич | 08.03.1985 | Маркетинга | Менеджер | 10.10.2009 | М | 0 |
Контрольные вопросы
1) Что называется связыванием таблиц?
2) Какие способы консолидации данных вы знаете?
3) В чем особенность применения способа консолидации по положению?
4) Какой способ консолидации данных для вас предпочтителен? Ответ обоснуйте.
5) В каких случаях целесообразно использовать средство построения сводных таблиц?
6) В чем отличие отчетов, построенных с помощью консолидации данных, от отчетов в сводных таблицах?
Лабораторная работа 5
ПОИСК ПОТЕРЯННЫХ КЛИЕНТОВ
Цель работы: Получить навыки применения средств MS Excel для применения в CRM-концепции.