Бесплатный фрагмент - Современный статистический анализ результатов научных исследований в Excel

СОДЕРЖАНИЕ

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

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

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

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

Данное пособие направлено на формирование у читателя не только теоретических знаний, но и практических навыков в использовании пакетов программных продуктов Microsoft Office 2021 и Windows 11, в частности MS Excel и MS Word, для обработки и анализа статистических данных. Главная цель пособия — помочь студентам и дипломникам, которые работают над своими проектами, освоить инструменты статистического анализа данных социально-экономических явлений и процессов.

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

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

Задачами данного пособия являются:

— Представление возможностей и функций Microsoft Office Word, Excel;

— Овладение методами обработки, обобщения и анализа статистической информации с использованием табличного процессора MS Excel и Word 2021, Windows 11 для изучения тенденций и закономерностей социально-экономических процессов и явлений;

— Ознакомление с процедурами сводки и группировки данных;

— Работа со статистическими таблицами;

— Выполнение анализа данных с помощью графиков;

— Проведение анализа скорости и интенсивности развития социально-экономических явлений и процессов;

— Построение моделей тренда и анализ основной тенденции;

— Построение различных линейных регрессионных моделей и анализ их адекватности;

— Ознакомление с процедурами анализа и прогнозирования временных рядов;

— Освоение прогнозирования с использованием различных моделей, учитывающих разнообразные комбинации трендов и сезонности;

— Освоение методики оценивания финансовых потоков и платежей при статистическом анализе деятельности финансовой организации;

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

ГЛАВА 1. Обзор возможностей сводок и группировок в табличном процессоре Excel

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

При обработке больших объемов статистических данных всегда является целесообразным привлечение специальной программы управления сводками и группировками — часто вполне достаточным оказывается набор функций управления списками, предоставляемый программой электронных таблиц Microsoft Excel. В данной главе достаточно подробно рассматриваются эти функции применительно к типичным задачам учета и статистики.

1.1. Оптимизация обработки данных в Microsoft Excel: эффективные приемы и методы

Основные определения

Список представляет собой набор строк таблицы, содержащей данные определенной структуры.

Список можно использовать как базу данных.

Запись — строка в писке.

Поле списка — столбец в списке.

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

На рабочем листе Excel каждая запись располагается в отдельной строке, а для каждого поля отводится: отдельный столбец.

После организации информации в форме списка данные можно:

— сортировать;

— фильтровать;

— подводить итоги.

Перед тем как создавать список в качестве базы данных в Microsoft Excel, необходимо тщательно продумать его структуру, чтобы при обычных операциях с данными, таких как поиск, сортировка или обработка данных, списки автоматически распознавались как базы данных. При этом следует учитывать следующие элементы списков:

• Столбцы списков превращаются в поля базы данных.

• Заголовки столбцов становятся именами полей базы данных.

• Каждая строка списка превращается в запись данных.

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

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

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

Способы ввода исходных данных в список

— Непосредственный ввод данных в ячейки рабочего листа Excel.

— Ввод с помощью формы ввода данных.

Форма данных

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

Создание формы

Откройте новую рабочую книгу. Сохраните ее под новым именем «Исходная таблица Сбор Зерновых (выполните команду «Файл», «Сохранить как»).

На рабочем листе «Лист1» создайте заголовки столбцов списка (имена полей записи). Это будет первая строка таблицы.

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

Открытие диалогового окна формы данных

На рабочем листе «Лист1» выделите любую ячейку в списке.

Выберите в меню «Данные» команду «Форма». Появится диалоговое окно формы данных.

Окно формы

Обратите внимание, что имя листа, на котором создается форма, и имя формы совпадают (Рисунок 1.1.1.).

Окно формы содержит:

— Имена полей, которые совпадают с заголовками столбцов списка.

— Поля ввода для редактирования записей списка.

— Кнопки для добавления, удаления, поиска записей:

«Добавить» — добавляет строки в конец таблицы;

«Удалить» — уничтожает текущую строку в форме;

«Назад» и «Далее» — используются для перемещения вверх и вниз по таблице;

«Критерии» — открывает окно поиска.

— Вертикальную голосу прокрутки. Она выполняет функции аналогичные кнопкам «Назад» и «Далее». Кроме того, при установке бегунка в крайнее нижнее положение в конце таблицы автоматически создается новая пустая строка.

Дополнение и изменение списка с использованием формы

В диалоговом окне формы данных «Лист1» нажмите кнопку «Добавить». Появится форма данных с пустыми полями.

Введите необходимые данные в поля ввода формы. Нажмите клавишу TAB — для того, чтобы перейти к редактированию следующего поля формы. Нажмите комбинацию клавиш SHIFT+TAB — для того, чтобы вернуться к предыдущему полю формы.

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

Чтобы закрыть диалоговое окно формы данных, нажмите кнопку «Закрыть».

Просмотр записей с помощью формы данных

В диалоговом окне формы данных существуют возможности просмотра данных, перечисленные в таблице 1.1.1.

Изменение записей с помощью формы данных

1. Откройте диалоговое окно формы данных.

2. Найдите запись, которую необходимо изменить, используя кнопки «Далее» или «Назад», либо вертикальную полосу прокрутки.

3. Чтобы отредактировать данные в записи, вам нужно аккуратно продвигаться по полям формы. Вы можете перейти к следующему полю формы, используя клавишу TAB или щелкнув левой кнопкой мыши. Если вам нужно вернуться к предыдущему полю формы, примените комбинацию клавиш SHIFT + TAB. Как только вы изменили данные, нажмите клавишу ENTER, чтобы сохранить изменения текущей записи и перейти к следующей.

4. По завершению редактирования всех записей в форме, вы можете нажать кнопку «Закрыть», чтобы обновить текущую запись и выйти из формы.

Для удобства использования формы данных в Microsoft Excel можно придерживаться ряда советов и инструкций. Вот некоторые из них:

Если поле списка содержит формулу, то в форме будет отображаться только ее результат, а само поле нельзя будет изменять.

При изменении записи, которая содержит формулу, результат формулы не будет вычислен автоматически до тех пор, пока вы не нажмете клавишу ENTER или кнопку «Закрыть».

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

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

Чтобы удалить запись с помощью формы данных, необходимо выделить ее в списке и нажать кнопку «Удалить». Если вы случайно удалили запись, можно воспользоваться функцией отмены действия, нажав сочетание клавиш CTRL + Z. Важно помнить, что удаление записи необратимо, поэтому перед нажатием кнопки «Удалить» рекомендуется внимательно проверить выбранную запись.

Удаление записи с помощью формы данных

Каждая запись удаляется отдельно. Удаление записи с помощью формы нельзя отменить. Для удаления откройте диалоговое окно формы данных; найдите запись, которую требуется удалить; нажмите кнопку «Удалить».

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

Нажмите кнопку «Закрыть», чтобы закончить работу с формой данных.

Поиск записей в списке с помощью формы

Рассмотрим пример поиска записи, у которой поле «Наименование региона» имеет значение «Орловская область».

Откройте диалоговое окно формы данных.

Нажмите кнопку «Критерии». Откроется диалоговое окно формы для ввода различных критериев. Это окно изображено на Рисунок 1.1.2. Щелкните по полю «Наименование региона» и введите критерий. В данном случае в качестве критерия выступает набор символов «Орловская область».

Нажмите кнопку «Далее». Excel просматривает весь список, начиная с первой записи, и ищет данные, удовлетворяющие введенному критерию. Если Excel находит запись, удовлетворяющую критерию, то она появляется в диалоговом окне. Искомая запись — это восьмая запись из 30 записей списка (Рисунок 1.1.3.). Нажмите кнопку «Далее», чтобы найти следующую запись, удовлетворяющую заданному критерию. Звуковой сигнал указывает на то, что ниже текущей записи не существует такой, которая удовлетворяла бы указанному критерию.

Нажмите кнопку «Назад», для того, чтобы произвести поиск среди записей, предшествующих текущей записи. Звуковой сигнал указывает на то, что выше текущей записи не существует такой, которая удовлетворяла бы указанному критерию.

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

Кнопка «Очистить» позволяет стереть введенные критерии, а кнопка «Вернуть» позволяет восстановить стертые критерии.

Нажмите кнопку «Закрыть», чтобы закрыть окно формы, либо нажмите кнопку «Правка», чтобы вернуться к правке формы.

В качестве второго примера рассмотрим поиск записей, у которых поле «Наименование экономического района» имеет значение «Центральный регион».

Пример ввода двух критериев поиска

Поиск записей осуществляется по двум критериям, поэтому соответствующие значения введены в три поля ввода. На Рисунок 1.1.4. показан пример ввода трех критериев поиска.

1.2. Организация данных в порядке возрастания или убывания с помощью функции сортировки в MS Excel

Порядок сортировки, используемый по умолчанию

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

Данные можно сортировать:

— по алфавиту;

— по числу;

— по дате.

Порядок сортировки может быть возрастающим (от 1 до 9, от А до Z) или убывающим (от 9 до 1, от Z до А).

При сортировке текста, значения сравниваются посимвольно слева направо. Например, если в ячейке содержится текст «А100», то после сортировки эта ячейка будет находиться после ячейки, содержащей А1 и перед ячейкой, содержащей А11.

При сортировке по возрастанию в Microsoft Excel используется следующий порядок (при сортировке по убыванию этот порядок заменяется на обратный за исключением пустых ячеек, которые всегда помещаются в конец списка): числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

Текст, в том числе содержащий числа, сортируется в следующем порядке: 0 1 2 3 4 5 6 7 8 9 ' — (пробел)! « # $ % & () *,. /:;? @ [\] ^ _ ` {|} ~ + <=> A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

Логическое значение ЛОЖЬ предшествует значению ИСТИНА. Все ошибочные значения равны. Пустые ячейки всегда помещаются в конец списка.

Порядок сортировки списков

При сортировке списка меняется порядок следования записей (строки таблицы). По полям записей (столбцы таблицы) сортировка не производится вообще.

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

Совет. Перед сортировкой сохраните исходный файл под другим именем, используя команду «Файл», «Сохранить как». Это позволит в дальнейшем, если данные в результате сортировки случайно окажутся перемешанными, вернуться к исходному порядку данных.

Отмена сортировки

Если Вас не устраивают результаты сортировки, то сразу после сортировки выполните ее отмену любым из способов:

— нажмите кнопку «Отменить» на панели инструментов «Стандартная»;

— выполните команду «Правка», «Отменить сортировку»;

— закройте файл, в котором выполнялась сортировка, без сохранения.

Сортировка строк по возрастанию данных в одном столбце

Отсортируем список в файле «Сбор Зерновых» в алфавитном порядке по столбцу «Наименование региона».

1. Сохраните исходный файл «Сбор Зерновых» под другим именем, например «Сортировка Сбор Зерновых». Щелкните по любой ячейке в сортируемом списке.

2. В меню «Данные» выполните команду «Сортировка». Excel автоматически выделяет весь список и открывает диалоговое окно «Сортировка диапазона» (Рисунок 1.2.1.).

3. Обратите внимание на переключатель «Идентифицировать поля по…». Обычно автоматически выбирается правильное положение. Переключатель установлен в положении «подписям (первая строка диапазона)», если список содержит заголовки столбцов. Выберите положение «обозначениям столбцов листа», если заголовков в списке нет.

4. В раскрывающемся списке «Сортировать по» выберите первое поле, по которому следует упорядочить данные (первый ключ сортировки) — «Наименование региона».

Выберите переключатель «По возрастанию» (Рисунок 1.2.2.).

Нажмите кнопку ОК.

Сортировка строк по двум или более столбцам

Отсортируем список в файле «Сбор Зерновых» по столбцам: сначала по столбцу «Наименование экономического района», а затем по столбцу «Пшеница 3 кл.». Для этого: сохраните исходный файл «Сбор Зерновых» под другим именем, например «Сортировка Сбор Зерновых», используя команду «Файл», «Сохранить как». Щелкните по любой ячейке в сортируемом списке.

В меню «Данные» выполните команду «Сортировка». Появится диалоговое окно «Сортировка диапазона». Укажите столбцы сортировки в полях:

«Сортировать по» — «Наименование экономического района»;

«Затем по» — «Пшеница 3 кл.».

Установите для первого и второго ключей сортировки положение

переключателя «По возрастанию».

Нажмите ОК.

Сортировка с помощью панели инструментов

Отсортируем исходный список из файла «Сбор Зерновых» по столбцу «Пшеница 3 кл.» в порядке возрастания.

На панели инструментов «Стандартная» имеются две кнопки, предназначенные для сортировки списка в возрастающем или убывающем порядке. Используя их, можно упорядочить данные по одному полю. Необходимо учитывать, что при такой сортировке используются последние значения параметров (порядок и направление сортировки, учет регистра), которые были установлены в диалоговом окне « Параметры сортировки».

Щелкните по любой ячейке в столбце, который следует использовать в качестве ключа сортировки. В данном задании в качестве ключа сортировки используется столбец «Посевная площадь».

Нажмите кнопку «По возрастанию» на панели инструментов «Стандартная».

Возврат к первоначальному порядку сортировки

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

1.3. Поиск данных с помощью фильтров

Фильтрация данных в Excel — это временное скрытие записей списка, не удовлетворяющих заданному критерию. В Excel существуют следующие средства для фильтрации данных: «Автофильтр», «Пользовательский Автофильтр», «Расширенный фильтр».

Использование «Автофильтра»

Для применения автофильтра выполните следующее.

— Щелкните по любой ячейке списка.

— В меню «Данные» выберите команду «Фильтр», «Автофильтр». В каждой ячейке с заголовками поля списка Excel поместит кнопки управления, с изображением черной стрелки. Таким образом, ячейки с названием полей превращаются в раскрывающиеся списки.

— Нажмите кнопку управления в том поле заголовка, которое следует включить в критерий. На экране появится список критериев.

Совет. Если на экране дисплея в раскрывающемся списке критериев вы не видите полный перечень критериев Автофильтра, то вы можете, например, уменьшить высоту строки, которая содержит заголовки.

Выберите желаемый критерий. Список критериев Автофильтра приведен в таблице 1.3.1.

Если пользователь выбирает критерий «Первые 10», либо «Условие», то следует в соответствующем диалоговом окне выбрать необходимую команду. Результаты работы Автофильтра моментально отображаются на экране. Обратите внимание: кнопки управления, которые участвовали в фильтрации данных, окрашены в голубой цвет.

Все записи, не отвечающие критерию фильтрации, окажутся скрытыми.

Если необходимо включить в текущий критерий фильтрации дополнительно другое поле, то повторите выполнение алгоритма.

Записи, находящиеся на экране (номера строк окрашены в голубой цвет) удовлетворяют условиям, которые были наложены на все поля, участвовавшие в фильтрации исходного списка.

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

Чтобы снять критерий целиком, не выходя из режима Автофильтра, выполните команду «Данные», «Фильтр», «Отобразить все».

Чтобы отменить режим Автофильтра, выберите команду «Данные», «Фильтр», «Автофильтр».

Все задания по фильтрации исходного списка, записанного в файле «СборЗерновых», будем выполнять в файле «Фильтр СборЗерновых». Для этого сохраните исходный файл «СборЗерновых» под именем «Фильтр СборЗерновых», используя команду «Файл», «Сохранить как».

Задание 1. Из исходного списка выберите только те записи, которые относятся к Центральному экономическому району.

1. Щелкните по любой ячейке исходного списка.

2. Выполните команду «Данные», «Фильтр», «Автофильтр».

3. Нажмите кнопку управления (черная стрелка) в поле «Наименование экономического района».

4. Из раскрывающегося списка вариантов фильтрации (обратите внимание, что список всех экономических районов, встречающихся в поле «Наименование экономического района», записан в алфавитном порядке) выберите «Центральный».

5. Результаты работы Автофильтра представлены на Рисунок 1.3.1.

Задание 2. Из списка, полученного в Задание 1, выберите только те области Центрального экономического района, у которых цены на пшеницу 3 класса не превышает 6200.

После выполнения пункта 5 Задания 1 нажмите кнопку управления в поле «Пшеница 3 кл.».

Из раскрывающегося списка вариантов фильтрации выберите «6200».

Результаты работы Автофильтра представлены на Рисунок 1.3.2.

Выполните команду «Данные», «Фильтр», «Отобразить все», чтобы вернуться к исходному списку.

Использование команды «Условие» или «Пользовательский Автофильтр»

Применение «Пользовательского Автофильтра» рассмотрим, на примере группировки исходного списка по посевной площади. Первый интервал группировки: менее 6200; второй от 6200 до 6400.

1. Из исходного списка выберите записи, имеющие посевную площадь менее 6200.

2. Убедитесь, что находитесь в режиме фильтрации исходного списка на Листе 1.

3. Нажмите кнопку управления в поле «Пшеница 3 кл.».

4. Из раскрывающегося списка вариантов фильтрации выберите «Условие» (Рисунок 1.3.3.)

5. Открывается диалоговое окно «Пользовательский автофильтр».

6. Используя раскрывающийся список, выберите опцию «меньше» в левом верхнем углу диалогового окна, а в правом верхнем текстовом поле введите число 6200, либо из раскрывающегося списка значений критерия выберите число 6400, если оно является элементом этого списка (Рисунок 1.3.3.).

Результат заполнения диалогового окна «Пользовательский автофильтр» представлен на Рисунок 1.3.4.

Нажмите кнопку ОК.

— Скопируйте результат на новый рабочий лист и переименуйте его в «Первый интервал». Вернитесь на Лист 1.

— Из исходного списка выберите записи, имеющие цены от 6200 до 6400.

— Убедитесь, что находитесь в режиме фильтрации на Листе1.

— Нажмите кнопку управления в поле «Пшеница 3 кл.».

— Из раскрывающего списка вариантов фильтрации выберите «Условие».

— Открывается диалоговое окно «Пользовательский автофильтр».

— Из первого раскрывающегося списка выберите опцию «больше или равно» и границу критерия 6200.

— Установите переключатель «И».

— Из второго раскрывающегося списка выберите оператор отношения «меньше», и границу критерия 6400.

Результат заполнения диалогового окна представлен на Рисунок 1.3.5.

Нажмите кнопку ОК

Шаг 5. Скопируйте результат на новый рабочий лист и переименуйте его во «Второй интервал». Вернитесь на Лист1.

Шаг 6. Выполните команду «Данные», «Фильтр», «Отобразить все».

Задание 3. Из исходного списка выберите только области Уральского и Центрально-Черноземного экономических районов.

Щелкните по любой ячейке исходного списка.

Выполните команду «Данные», «Фильтр», «Автофильтр».

Нажмите кнопку управления (черная стрелка) в поле «Наименование региона».

Из раскрывающегося списка вариантов фильтрации выберите команду «Условие». Открывается диалоговое окно «Пользовательский автофильтр». Результат заполнения этого окна представлен на Рисунок 1.3.6. Обратите внимание, что дна условия связаны с помощью логической связки ИЛИ, а не И. Если бы в данном условии был записан оператор И, то из исходного списка не было бы выбрано ни одной записи.

Использование команды «Первые 10»

Задание 1. Из исходного списка выберите 5 областей, имеющих наибольшую цену на пшеницу.

Раскройте для фильтрации поле «Пшеница 3 кл.».

Выберите команду «Первые 10». Открывается диалоговое окно.

В диалоговом окне «Наложение условия по списку» выберите три параметра:

— Число (счетчик);

— Выбор из наибольших или наименьших элементов (раскрывающийся список);

— Единицы отсчета (количество элементов списка, либо процент от элементов) (раскрывающийся список).

В данном примере: первый параметр — цифра 5; второй — наибольших; третий — элементов списка.

Нажмите ОК.

Задание 2. Из исходного списка выберите 15% областей, имеющих наименьшие цены на пшеницу 4 класса.

Раскройте для фильтрации поле «Пшеницу 4 кл.».

Выберите команду «Первые 10».

В диалоговом окне «Наложение условия по списку» выберите три параметра.

В данном примере: первый параметр — цифра 15; второй — наименьших; третий — % от количества элементов.

Нажмите ОК.

Использование «Расширенного фильтра»

Расширенный фильтр представляет более широкие возможности поиска и фильтрации данных, чем Форма, Автофильтр или Пользовательский Автофильтр.

— Создайте критерий фильтрации.

— Критерий фильтрации создается как таблица и может храниться в любом месте рабочей книги или даже в другом файле.

— Очистите старые критерии, если они были созданы ранее

— Задайте новые критерии. Диапазон критериев может содержать константы, шаблоны, простые сравнения, формулы.

— Перед заголовком исходного списка вставьте три или более чистых строки (диапазон условий отбора).

— В верхнюю строку диапазона условий введите в точности повторяющиеся по написанию имена полей исходного списка (заголовков таблицы), выполняя команды «Правка», «Копировать» и «Правка», «Вставить».

— В следующие строки диапазона условий введите критерии поиска.

Фильтрация исходного списка

Щелкните по любой ячейке писка.

Выполните команду «Данные», «Фильтр», «Расширенный фильтр».

Откроется диалоговое окно «Расширенный фильтр» (Рисунок 1.3.7.).

При использовании функции «Расширенный фильтр» в Excel, вам доступны два варианта обработки данных: фильтрация списка на месте или копирование результата в другое место. При выборе первого варианта, записи, которые не соответствуют заданным критериям, будут скрыты. Если же выбран второй вариант, исходный список не будет изменен, а отфильтрованные записи будут скопированы в указанный диапазон.

Чтобы настроить расширенный фильтр, нужно выбрать соответствующий режим обработки и ввести критерии фильтрации. Для этого в диалоговом окне «Расширенный фильтр» необходимо указать исходный диапазон (по умолчанию Excel автоматически выделяет список и указывает диапазон, в котором он находится), а затем ввести ссылку или имя диапазона критериев в соответствующее поле. Если выбрано копирование результата в другое место, необходимо указать ссылку или имя диапазона, куда будут скопированы отфильтрованные записи.

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

Нажмите кнопку ОК.

Поиск близкого соответствия с использованием образца

Задание 3. Из исходного списка выберите только те регионы, которые начинаются на букву «К». Результаты разместите после исходного списка.

Результат заполнения диалогового окна «Расширенный фильтр» представлен на Рисунок 1.3.8.

Критерий фильтрации и результат фильтрации исходного списка изображены на Рисунок 1.3.9.

Использование множественного сравнения в диапазоне критериев

Используя «Расширенный фильтр» в диапазоне критериев, можно ввести несколько условий как в строке, так и в столбце.

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

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

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

Задание 4. Из исходного списка выберите записи, у которых цены на пшеницу выше 6200 у. е.

Результаты представлены на Рисунке 1.3.10.

Задание 5. Из исходного списка выберите записи, у которых цены на пшеницу 3 кл. меньше 6200 у.е. и цены на пшеницу 4 кл. выше 6200 у.е.

1.4. Промежуточные итоги

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

Чтобы получить промежуточные итоги в списке, выполните следующие операции.

Отсортируйте исходный список по полю, которое будет исходным при образовании группы, например поле «Наименование экономического района». Щелкните по любой ячейке списка.

Выполните команду «Данные», «Итоги». Открывается диалоговое окно «Промежуточные итоги».

В диалоговом окне «Промежуточные итоги» из раскрывающегося списка «При каждом изменении в» выберите группу, для которой определяются промежуточные итоги. Это будет название столбца, по которому выполнялась сортировка исходного списка (см. п.1.1.).

Из раскрывающегося списка «Операция» выберите функцию, необходимую для подведения итогов. Excel предлагает следующие функции, указанные в таблице 1.4.1.

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

Три флажка, которые находятся внизу окна «Промежуточные итоги», предназначены:

— Установка первого флажка «Заменить текущие итоги» обновление итоговых данных;

— Установка второго флажка «Конец страницы между группами» — приводит к созданию разделителей (разрывов) страниц в области таблицы. После этого каждая группа с промежуточными итогами будет распечатываться на отдельной странице. Для удаления разделителя страницы нужно выделить какую-нибудь ячейку под разделителем и выполнить команду «Вставка, Убрать разрыв страницы».

— Установка третьего флажка «Итоги под данными» — обеспечивает размещение итоговых данных ниже исходных. Если флажок снять, то промежуточные итоги будут предшествовать данным, для которых они подсчитаны, а строка «Общий итог», будет размещена сразу под строкой заголовков столбцов таблицы.

Нажмите кнопку ОК.

Рабочий лист с промежуточными итогами изображен на Рисунке 1.4.1.