Сортировка данных. Фильтр. Расширенный фильтр

Данная тема требует введения некоторых определений.

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

Порядок сортировки − это способ изменения относительного положения данных, основанный на значении или типе данных. Данные можно сортировать по алфавиту, по числу или по дате. Порядок сортировки может быть возрастающим (от I до 9, от А до Z, от А до Я) или убывающим (от 9 до 1, от Z до А, от Я до А).

Пользовательский порядок сортировки − это неалфавитный и нечисловой порядок сортировки. Например, «воскресенье, понедельник, вторник» или «водород, кислород, железо». В Microsoft Excel определено несколько списков сортировки. С помощью кнопок, находящихся в группе Сортировка и фильтр вкладки Данные можно определить свой собственный список сортировки.

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

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

 

Фильтр

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

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

 

Задание 1

1.Отсортируйте таблицу из файла "Лр1" по названиям фирм (по возрастанию).

2.Установите фильтр, скрыв фирмы, чья доля в загрузке гостиницы за месяц не превышает 100 чел/дней.

Ход выполнения работы

1. Загрузите файл Лр1.

1.

2. Установите курсор в область заголовка таблицы.

2.1. Нажмите кнопку Фильтр, находящуюсяна вкладке Данные в группе Сортировка и фильтрация.

2.2. Откройте раскрывающийся список в столбце Наименование и выберите пункт меню Сортировка от А до Я.

2.3. Оцените правильность сортировки фирм по наименованию (по алфавиту).

2.4. Отмените сортировку, нажав кнопку Отменить ввод (сортировку) в левом углу окна Excel.

2.

3. Установите курсор в область заголовка таблицы.

3.1. Нажмите кнопку Фильтр, находящуюсяна вкладке Данные в группе Сортировка и фильтрация.

3.2. Откройте раскрывающийся список в столбце Всего и выберите пункт меню Числовые фильтры.

3.3. Войдите в пункт меню б ольше и в окне Пользовательский фильтр введите значение 100. Нажмите кнопку Ok.

3.4. Оцените правильность выполнения задания.

3.5. Отмените сортировку, нажав кнопку Отменить ввод (сортировку) в левом углу окна Excel.

Расширенный фильтр

Обычная фильтрация очень удобна, но не всегда позволяет выполнить необходимый отбор данных. Пусть нужно выполнить фильтрацию по условию, которое является объединением условий фильтрации для двух и более столбцов. Такое объединение условий выполняется, обычно, при помощи логических функций ИЛИ или И. Фильтрацию же по этим условиям можно осуществить, применив расширенный фильтр. Расширенный фильтр находится на вкладке Данные в группе Сортировка и фильтр, кнопка Дополнительно.

Задание 1

Используя расширенный фильтр, найдите свободные номера в гостинице (рис.17) в промежуток времени с 5.01.2015 по 10.01.2015. При этом надо учитывать, что некоторые номера в этот промежуток находятся на ремонте.

Рис.17

Ход выполнения работы

1. Создайте таблицу на рис.17, оставив сверху 5 свободных строчек, куда скопируйте шапку таблицы. В остальные строки впишите условия, по которым должна проводиться фильтрация. Между таблицей и условиями поиска должна быть хотя бы одна строка. Свободные номера будут при выполнении хотя бы одного из условий (логическая функция ИЛИ):

· День приезда постояльца в номер после интересующего нас периода;

· День отъезда постояльца из номера до интересующего нас периода;

· Номер на ремонте.

Запись условий приведена на рис.19

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

2. Выполните фильтрацию, открыв вкладку Данные и нажав кнопку Дополнительно. В открывшемся диалоговом окне Расширенный фильтр, рис.18 автоматически будет введен диапазон с исходными данными.

 

Рис.18

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

4. Установите переключатель Обработка в положение скопировать результат в другое место и нажмите ОК. Результаты поиска отражены на рис. 19, начиная с 24 строки.

Рис.19

Ответы на финансовые задачи

1. Задание 1 = АПЛ (66000; 12500; 6) = 8 916,67р.
2. Задание 2 = ПС (0,08/12; 20*12; -50) = 5 977,71р.
3. Задание 3 = БС (0,06/12; 12; -100; -1000; 1) = 2 301,40р.
4. Задание 4 = ПРПЛТ (0,06/12; 1; 5*12; -600000) = 3 000,00р.
5. Задание 5 = ПРПЛТ (0,06; 5; 5; -600000) = 8063,00р.
6. Задание 6 = АСЧ (70000; 4700; 10; 1) = 11 872,73р.

= АСЧ (70000; 4700; 10; 10) = 1 187,27р.

 

Практическое занятие №8

Создание диаграмм

 

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

В Excel есть два способа создания диаграмм. Во-первых, это "внедренные" диаграммы и, во-вторых, "диаграммы на отдельных листах". Внедренные диаграммы представляют собой графики, наложенные на рабочую страницу и сохраняемые в этом же файле. Их, как правило, используют для отчетов. Д иаграммы на отдельных листах удобны для подготовки слайдов или для вывода на печать.

Для построения диаграммы необходимо:

· выделить данные;

· перейти на вкладку Вставка в группу Диаграммы и выбрать тип диаграммы;

· при активизации диаграммы становятся доступными три контекстные вкладки Конструктор, Макет, Формат. Инструменты, размещенныена этих вкладках, позволяют отформатировать полученную диаграмму, изменить ее тип, стиль, месторасположение и т.д.