Фильтрация списка для поиска нужной информации

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

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

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

Ограничение отображения информации

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

Чтобы создать фильтр, щелкните на ячейке в области, которую вы хотите отфильтровать, и в меню Данные (Data) включите опцию Автофильтр (AutoFilter). Когда вы включите автофильтр, являющийся набором нескольких средств фильтрации, в ячейке, которая распознана программой Excel как заголовок столбца, появится кнопка списка.

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

Выбор опции Первые 10 (Top 10) не только ограничивает отображение данных первыми десятью значениями; также открывается диалоговое окно Первые 10 - автофильтр (Top 10 AutoFilter). В этом окне вы можете выбрать, откуда начинать отображение данных, либо сверху, либо снизу столбца, задать количество значений, которые должны отображаться, а также указать, в каком формате введено количество значений - в числах, либо в процентах. Используя диалоговое окно Первые 10 - автофильтр (Top 10 AutoFilter), можно определить десять самых удачных продавцов вашей компании или определить, какие покупатели входят в пять процентов самых активных.

Выбрав пункт Расширенный фильтр (Custom) в меню Фильтр (AutoFilter), вы можете установить правило, согласно которому Excel будет определять, какие строки отображать после включения фильтра. Например, вы можете установить правило, согласно которому на рабочем листе будут отображаться только те дни, в которые объем продаж составил менее $2.500. Анализируя эти данные, можно выяснить, какие факторы в эти дни плохо сказываются на интенсивности продаж вашей компании.

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

Выбор строк произвольным образом полезен, например, при рассылке покупателям специальных предложений, при выборе дней месяца для проведения аудита или при определении победителя при розыгрыше призов. Для выбора строк можно использовать функцию СЛЧИС (RAND), которая генерирует случайное значение и сопоставляет его со значением условия, введенным в выражении. Выражением, возвращающим значение ИСТИНА в 30% случаев, будет RAND()<=30%; вы можете использовать это выражение для выбора каждой строки в листе с вероятностью 30%.