- •Табличный процессор ms Excel
- •Создание и редактирование таблиц
- •1 Настройка новой книги
- •2 Ввод данных и формул
- •3 Форматирование ячеек
- •Создание и редактирование диаграмм и графиков
- •1 Построение двумерных и трехмерных диаграмм
- •2 Редактирование диаграмм
- •3 Построение других типов диаграмм
- •Встроенные функции ms Excel. Статистический анализ
- •1 Использование функций ms Excel
- •2 Анализ статистических данных
- •3 Инструменты пакета анализа
- •Прогнозирование в Excel с помощью регрессионного анализа
- •1 Линейный регрессионный анализ
- •Линейная регрессия
- •2 Экспоненциальный регрессионный анализ.
- •Правила ввода функций:
- •Линия тренда
- •Добавление данных
- •3 Линейный многомерный регрессионный анализ
- •Работа с финансовыми функциями. Анализ «что-если»
- •1 Финансовые функции при экономических расчётах
- •1 Функция плт. Расчёт величины ежемесячной выплаты кредита
- •2 Прогнозирование с помощью анализа «что-если».
- •Создание макросов в ms Excel 2007
- •Общие сведения
- •1 Запуск макроса с клавиатуры сочетанием клавиш
- •Абсолютная адресация
- •Назначение макросу сочетания клавиш:
- •2 Запуск макроса из окна Макрос
- •Контрольные вопросы
- •4 Запись макроса, запускаемого из окна Макрос и сочетанием клавиш с клавиатуры.
- •Задание
- •3 Открыть новую книгу и выполнить в ней оба макроса.
- •Поиск решения в ms Excel
- •1 Создание формы
- •2 Ввод данных в окно Поиск решения
- •3 Задание параметров поиска и решение задачи
- •Работа с окнами в ms Excel
- •Общие сведения
- •1 Вывод разных частей одного документа
- •2 Вывод на экран разных документов
- •3 Операции в окнах
- •4 Закрепление областей листа
- •Контрольные вопросы
- •Задание
- •Списки в ms Excel. Сортировка и фильтрация данных
- •1 Создание списка – базы данных
- •2 Сортировка данных в списке
- •3 Фильтрация данных в списке
- •3.1 Фильтр
- •3.2 Расширенный фильтр
- •3.2.1 Формирование критерия сравнения
- •3.2.2 Формирование вычисляемого критерия
- •1 Вариант
- •3 Вариант
- •3.3 Фильтрация с помощью формы
- •4 Контрольные вопросы
- •5 Задание
- •1 Вариант.
- •Структурирование таблиц в ms Excel
- •1 Ручной способ структурирования
- •2 Автоструктурирование
- •3 Структурирование с подведением итогов
- •4 Контрольные вопросы
- •5 Задание
- •Сводные таблицы
- •2 Построение макета сводной таблицы.
- •Консолидация данных
- •1 Консолидация данных по расположению
- •2 Консолидация данных по категориям
- •Контрольные вопросы
- •Задание
- •1 Консолидация по областям
- •2 Консолидация по категориям
2 Сортировка данных в списке
Сортировка данных в таблице БД MS Excel является основной операцией, т.к. многие операции группировки, которые выполняются из меню Данные, можно использовать только после сортировки.
Цель сортировки – упорядочивание данных в порядке возрастания или убывания, она производится в том же списке. В среде MS Excel сортировка выполняется по трём полям, причём сортировка по второму полю производится для одинаковых значений отсортированного первого поля, а третьего – для одинаковых значений второго поля.
Для сортировки данных нужно выполнить команду Данные\ Сортировка, откроется окно Сортировка диапазона (рис. 9.4), в котором выбираются столбцы и вид упорядочивания – по возрастанию или по убыванию.
При сортировке по возрастанию упорядочивание идёт от меньшего к большему – по алфавиту, по хронологии дат, но согласно приоритету: числа, текст, логические значения, значения ошибок и пустые ячейки.
При сортировке по убыванию используется обратный порядок, только пустые ячейки всегда собираются в конце списка.
Проведём сортировку списка "Экзаменационная ведомость" по полям Код преподавателя, Оценка и Фамилия И.О.
Рисунок 9.4 Окна Сортировка диапазона и Параметры сортировки
Результат сортировки показан на рис. 9.4, где столбец Е отсортирован по возрастанию, в столбце D по возрастанию отсортированы одинаковые записи столбца Е, а в столбце В – по алфавиту одинаковые значения в столбце D.
Рисунок 9.4 Результат сортировки данных
Дополнительные установки сортировки задаются в окне Параметры сортировки, вызываемой кнопкой Параметры (рис. 9.4). Здесь можно задать сортировку с учётом или без учёта регистра, по столбцам или построкам, обычный или выбранный из списка. Этот список можно создать самостоятельно с помощью команды Сортировка \ Настраиваемая сортировка (выделив таблицу, нажать правой клавишей мыши и выбрать Сортировка\Настраиваемая сортировка).
3 Фильтрация данных в списке
Фильтрация данных – это выбор данных, соответствующих какому-либо условию, критерию. Фильтрация в MS Excel выполняется из вкладки Данные \ раздел Сортировка и фильтр двумя способами:
с помощью команды Фильтр и
с помощью команды Дополнительно (Расширенный фильтр).
3.1 Фильтр
Фильтрация позволяет выбрать критерий отбора из предлагаемых в приложении для каждого столбца отдельно. Для этого нужно раскрыть список [▼] в названия каждого столбца и поставить галочку для критерия. В результате в таблице остаются только строки, удовлетворяющие критериям. Например, фильтрация поля "Оценка" по условию "4" оставляет от исходного списка только записи с оценкой "4" (рис. 9.5)
Рисунок 9.5 Список студентов, получивших отметку 4
Дальнейшая фильтрация по другому полю выполняется для уже отфильтрованных данных, например, фильтр по коду преподавателя 1 приведёт к следующему результату (рис. 9.6):
Рисунок 9.6 Список студентов, получивших отметку 4
у преподавателя с кодом 1
Список критериев для каждого столбца содержит следующие варианты критериев:
все – выбираются все записи поля;
первые 10 – появляется окно Наложение условия по списку, в котором выбираются количество выводимых записей (наибольших или наименьших) в элементах списка или процентах от количества элементов;
значения – отфильтровываются только записи, содержащие в данном столбце указанное значение;
условие – нажать кнопку списка, выбрать Текстовые фильтры \ Настраиваемый фильтр - пользователь формирует условие отбора в окне Пользовательский автофильтр (равно, больше, …И, ИЛИ…) и записи фильтруются по этому условию.
Отмена результата фильтрации (для поля) выполняется либо повторной фильтрацией по критерию Все, либо – для всего списка – повторным вводом команды Данные \ Фильтр.