Список в excel способы его обработки
Excel – одна из лучших программ для аналитика данных. А почти каждому человеку на том или ином этапе жизни приходилось иметь дело с цифрами и текстовыми данными и обрабатывать их в условиях жестких дедлайнов. Если вам и сейчас нужно это делать, то мы опишем техники, которые помогут существенно улучшить вам жизнь. А чтобы было более наглядно, покажем, как их воплощать, с помощью анимаций.
Анализ данных через сводные таблицы Excel
Сводные таблицы – один из самых простых способов автоматизировать обработку информации. Он позволяет свести в кучу огромный массив данных, которые абсолютно не структурированы. Если его использовать, можно почти навсегда забыть о том, что такое фильтр и ручная сортировка. А чтобы их создать, достаточно нажать буквально пару кнопок и внести несколько несложных параметров в зависимости от того, какой способ представления результатов нужен конкретно вам в определенной ситуации.
Существует множество способов автоматизации анализа данных в Excel. Это как встроенные инструменты, так и дополнения, которые можно скачать на просторах интернета. Также есть дополнение «Пакет анализа», которое было разработано компанией Майкрософт. Она имеет все необходимые возможности, чтобы вы могли получать все необходимые результаты в одном файле Excel.
Пакет анализа данных, разработанный Майкрософт, можно использовать исключительно на едином листе в одну единицу времени. Если он будет обрабатывать информацию, расположенную на нескольких, то итоговая информация будет отображаться исключительно на одном. В других же будут показываться диапазоны без какой-либо значений, в которых есть исключительно форматы. Чтобы осуществить проанализировать информацию на нескольких листах, нужно использовать этот инструмент по отдельности. Это очень большой модуль, который поддерживает огромное количество возможностей, в частности, позволяет выполнять следующие типы обработки:
- Дисперсионный анализ.
- Корреляционный анализ.
- Ковариация.
- Вычисление скользящего среднего. Очень популярный метод в статистике и в трейдинге.
- Получать случайные числа.
- Выполнять операции с выборкой.
Эта надстройка не активирована по умолчанию, но входит в стандартный пакет. Чтобы ею воспользоваться, необходимо ее включить. Для этого сделайте следующие шаги:
- Перейдите в меню «Файл», и там найдите кнопку «Параметры». После этого перейдите в «Надстройки». Если же вы установили 2007 версию Эксель, то нужно нажать на кнопку «Параметры Excel», которая находится в меню Office.
- Далее появляется всплывающее меню, озаглавленное словом «Управление». Там находим пункт «Надстройки Excel», нажимаем на него, а потом – на кнопку «Перейти». Если же вы используете компьютер Apple, то достаточно открыть вкладку «Средства» в меню, а потом в раскрывающемся перечне найти пункт «Надстройки для Excel».
- В том диалоге, который появился после этого, нужно поставить галочку возле пункта «Пакет анализа», после чего подтвердить свои действия, нажав кнопку «ОК».
В некоторых ситуациях может оказаться так, что этого дополнения найти не удалось. В этом случае его не будет в перечне аддонов. Для этого надо нажать на кнопку «Обзор». Может также появиться информация о том, что пакет полностью отсутствует на этом компьютере. В этом случае необходимо его установить. Для этого нужно нажать на кнопку «Да».
Перед тем, как включить пакет анализа, необходимо сначала активировать VBA. Для этого его нужно загрузить таким же способом, как и саму надстройку.
Как работать со сводными таблицами
Первоначальная информация может быть какой-угодно. Это могут быть сведения о продажах, доставке, отгрузках продукции и так далее. Независимо от этого, последовательность шагов будет всегда одинаковой:
- Откройте файл, в котором содержится таблица.
- Выделите диапазон ячеек, которые мы будем анализировать с помощью сводной таблицы.
- Откройте вкладку «Вставка, и там надо найти группу «Таблицы», где есть кнопка «Сводная таблица». Если же используется компьютер под операционной системой Mac OS, то нужно открыть вкладку «Данные», и эта кнопка будет находиться во вкладке «Анализ».
- После этого откроется диалог с заголовком «Создание сводной таблицы».
- Затем выставите такое отображение данных, которое соответствует выделенному диапазону.
Мы открыли таблицу, информация в которой никоим образом не структурирована. Чтобы это сделать, можно воспользоваться настройками полей сводной таблицы в правой стороне экрана. Например, отправим в поле «Значения» «Сумму заказов», а информацию про продавцов и дату продажи – в строки таблицы. Исходя из данных, которые содержатся в этой таблице, автоматически определились суммы. Если есть необходимость, можно открыть информацию по каждому году, кварталу или месяцу. Это позволит получить детальную информацию, которая надо в конкретный момент.
От того, сколько колонок есть, будет отличаться и набор имеющихся параметров. Например, общее число столбцов – 5. И нам надо просто разместить и выбрать их верным образом, а показать сумму. В таком случае выполняем действия, показанные на этой анимации.
Можно сводную таблицу конкретизировать, указав, например, страну. Для этого мы включаем пункт «Страна».
Можно также посмотреть информацию про продавцов. Для этого мы заменяем колонку «Страна» на «Продавец». Результат получится следующий.
Анализ данных с помощью 3D-карт
Данный метод визуального представления с географической привязкой дает возможность искать закономерности, привязанные к регионам, а также анализировать информацию этого типа.
Преимущество этого способа в том, что нет необходимости отдельно прописывать координаты. Необходимо просто правильно написать географическое положение в таблице.
Как работать с 3D-картами в Excel
Последовательность действий, которую вам необходимо выполнить, чтобы работать с 3Д-картами, следующая:
- Откройте файл, в котором есть интересующий диапазон данных. Например, таблица, где есть колонка «Страна» или «Город».
- Информацию, которая будет показываться на карте, нужно сначала отформатировать, как таблицу. Для этого надо найти соответствующий пункт на вкладке «Главная».
- Выделите те ячейки, которые будут анализироваться.
- После этого переходим на вкладку «Вставка», и там находим кнопку «3Д-карта».
Затем показывается наша карта, где города в таблице представлены в виде точек. Но нам не особо нужно просто наличие информации о населенных пунктах на карте. Нам гораздо важнее видеть ту информацию, которая привязана к ним. Например, те суммы, которые можно показать, как высоту столбика. После того, как мы выполним действия, указанные на этой анимации, при наведении курсора на соответствующий столбик будут отображаться привязанные к нему данные.
Также можно воспользоваться круговой диаграммой, которая является намного более информативной в некоторых случаях. От того, какая общая сумма по величине, зависит размер круга.
Лист прогноза в Excel
Нередко бизнес-процессы зависят от сезонных особенностей. И такие факторы надо обязательно принимать в учет на этапе планирования. Для этого существует специальный инструмент Excel, который понравится вам своей высокой точностью. Он значительно более функциональный, чем все описанные выше методы, какими бы отличными они ни были. Точно так же, очень широкой является сфера его использования – коммерческие, финансовые, маркетинговые и даже государственные структуры.
Важно: чтобы рассчитать прогноз, необходимо получить информацию за предыдущее время. От того, насколько долгосрочные данные, зависит качество прогнозирования. Рекомендуется иметь данные, которые разбиты по одинаковым интервалам (например, поквартально или помесячно).
Как работать с листом прогноза
Чтобы работать с листом прогноза, необходимо выполнять следующие действия:
- Откройте файл, в котором содержится большой объем информации по тем показателям, которые нам надо проанализировать. Например, в течение прошлого года (хотя чем больше, тем лучше).
- Выделите две строки с информацией.
- Перейдите в меню «Данные», и там кликните по кнопке «Лист прогноза».
- После этого откроется диалог, в котором можно выбрать тип визуального представления прогноза: график или гистограмма. Выберите тот, который подходит под вашу ситуацию.
- Установите дату, когда прогноз должен закончиться.
В приводимом нами ниже примере даются сведения за три года – 2011-2013. При этом рекомендуется указывать временные промежутки, а не конкретные числа. То есть, лучше писать март 2013, а не конкретное число типа 7 марта 2013 года. Чтобы исходя из этих данных получить прогноз на 2014 год необходимо получить данных, расположенные в рядах с датой и показателями, которые были на этот момент. Выделяем эти строки.
Затем переходим на вкладку «Данные» и ищем группу «Прогноз». После этого переходим в меню «Лист прогноза». После этого появится окно, в котором снова выбираем способ представления прогноза, а затем устанавливаем дату, к которой прогноз должен быть закончен. После этого нажимаем на «Создать», после чего получаем три варианта прогноза (показываются оранжевой линией).
Быстрый анализ в Excel
Предыдущий способ действительно хорош, потому что позволяет составлять реальные прогнозы, основываясь на статистических показателях. Но этот метод позволяет фактически проводить полноценную бизнес-аналитику. Очень классно, что эта возможность создана максимально эргономичной, поскольку для достижения желаемого результата необходимо совершить буквально несколько действий. Никаких ручных подсчетов, записи каких-либо формул. Достаточно просто выбрать диапазон, который будет анализироваться и задать конечную цель.
Есть возможность прямо в ячейке создавать самые разные диаграммы и микрографики.
Как работать
Итак, чтобы работать, нам надо надо открыть файл, в котором содержится тот набор данных, который надо анализировать и выделить соответствующий диапазон. После того, как мы его выделим, у нас автоматически появится кнопка, дающая возможность составить итоги или же выполнить набор других действий. Называется она быстрым анализом. Также мы можем определить суммы, которые автоматически будут проставлены внизу. Более наглядно посмотреть, как это работает, можете на этой анимации.
Функция быстрого анализа позволяет также по-разному форматировать получившиеся данные. А определить, какие значения больше или меньше, можно непосредственно в ячейках гистограммы, которая появляется после того, как мы настроим этот инструмент.
Также пользователь может поставить самые разные маркеры, которые обозначают большие и меньшие значения относительно тех, которые есть в выборке. Так, зеленым цветом будут показываться самые большие значения, а красным – наиболее маленькие.
Очень хочется верить, что эти приемы позволят вам значительно повысить эффективность вашей работы с электронными таблицами и максимально быстро добиться всего, что вы желаете. Как видим, эта программа для работы с электронными таблицами дает очень широкие возможности даже в стандартном функционале. А что уже говорить про дополнения, которых очень много на просторах интернета. Важно только обратить внимание, что все аддоны должны быть тщательно проверены на вирусы, потому что модули, написанные другими людьми, могут содержать вредоносный код. Если же надстройки разработаны компанией Майкрософт, то ее можно использовать смело.
Пакет анализа от Майкрософт – очень функциональная надстройка, которая делает пользователя настоящим профессионалом. Она позволяет выполнить почти любую обработку количественных данных, но она довольно сложная для начинающего пользователя. На официальном сайте справки Майкрософт есть детальная инструкция по тому, как использовать разные виды анализа с помощью этого пакета.
В электронных таблицах доступны такие средства обработки данных, как сортировка, фильтрация и формирование итогов. Использование этих средств требует представления электронной таблицы в виде базы данных или списка. В этом разделе показано, как представить электронную таблицу в виде базы данных и применить к ней аппарат сортировки, фильтрации и формирования итоговых данных, включая средства сводных таблиц.
Список — это один из способов организации данных на рабочем листе. Список создается как помеченный ряд, состоящий из строк с однотипными данными. Данные, организованные в список, в терминологии Excel называются базой данных (БД). При этом строки таблицы — это записи базы данных, а столбцы — поля записей БД. Чтобы превратить таблицу Excel в список или базу данных, необходимо присвоить столбцам однострочные имена, которые будут использоваться как имена полей записей БД. Следует иметь в виду, что однострочные имена столбцов могут состоять из нескольких строк заголовков, размещенных в одной строке таблицы Excel.
При создании списка или базы данных на рабочем листе Excel необходимо выполнять следующие правила:
1. На одном рабочем листе не следует помещать более одного списка, поскольку некоторые операции, например Фильтрация, работают в определенный момент только с одним списком.
2. Следует отделять список от других данных рабочего листа хотя бы одним незаполненным столбцом или одной незаполненной строкой. Это поможет Excel автоматически выделить список при выполнении фильтрации или при сортировке данных.
3. Список может занимать весь рабочий лист.
4. Имена столбцов должны располагаться в первой строке списка. Excel использует эти имена при создании отчетов, в поиске и сортировке данных.
5. Для имен столбцов следует использовать шрифт, тип данных, выравнивание, формат, рамку или стиль прописных букв, •отличных от тех, которые использовались для данных списка.
6. Чтобы отделить имена столбцов от данных, следует разместить рамку по нижнему краю ячеек строки с именами столбцов. Не рекомендуется использовать пустые строки или пунктирные линии.
Сортировка данных. Список БД можно отсортировать по алфавиту, числам или в хронологическом порядке в соответствии с содержанием определенного столбца или столбцов.
Чтобы отсортировать весь список, достаточно выделить одну ячейку и выбрать в меню Данные команду Сортировка. Excel автоматически выделит весь список. Если в первой строке списка БД находятся имена столбцов, то они не будут включены в сортировку. Необходимо иметь в виду, что в этом случае итоговая строка исходного списка также будет включена в сортировку, поэтому целесообразнее самостоятельно выделять область исходного списка для сортировки.
Команда Сортировка осуществляется через диалоговое окно. В трех окнах ввода окна Сортировка можно задать ключи, по которым она будет выполнена.
В диалоговом окне Сортировка имеется режим Параметры, который позволяет установить порядок сортировки по первому ключу — обычный или определяемый пользователем, задать учет кодировки строчных и прописных букв (учет регистра символов), а также направление сортировки — по возрастанию или по убыванию.
Для быстрой сортировки на панели инструментов Стандартная находятся две кнопки:
Фильтрация данных в списке. С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям.
Excel позволяет быстро и удобно просматривать требуемые данные из списка БД с помощью простого средства — автофильтра. Более сложные запросы к базе данных можно реализовать с помощью команды Расширенный фильтр.
Чтобы использовать автофильтр,надо сначала выделить область БД для поиска, с заголовками столбцов. При этом имена столбцов списка Excel преобразует в имена полей записей БД.
Затем выполнить команду Автофильтр в меню Данные.
По команде Автофильтр Excel помещает раскрывающиеся стрелки непосредственно на имена столбцов списка. Щелкнув по стрелке, можно вывести на экран список всех уникальных элементов соответствующего столбца. Если выделить некоторый элемент столбца, то будут скрыты все строки, кроме тех, которые содержат выделенное значение.
Элемент столбца, который выделен в раскрывающемся окне списка, называется критерием фильтра. Можно продолжить фильтрацию списка с помощью критерия из другого столбца. Чтобы удалить критерии фильтра для отдельного столбца, надо выбрать параметр Все в раскрывающемся списке столбца. Чтобы показать все скрытые в списке строки, надо выбрать в меню Данные команду Фильтр, а затем — команду Показать все.
С помощью автофильтра можно для каждого столбца задать Пользовательские критерии отбора записей базы данных, например, вывести на экран только те записи, значения полей которых находятся в пределах заданного интервала. Чтобы задать пользовательский критерий, надо в раскрывающемся списке столбца выбрать параметр Настройка. затем в диалоговом окне Пользовательский автофильтр ввести нужные критерии.
Возможна фильтрация списков в Excel по сложным критериям с использованием команды Расширенный фильтр.
Для фильтрации списка или базы данных по сложному критерию, который будет определен ниже, а также для получения части исходного списка по нескольким заданным столбцам в Excel ис пользуется команда Расширенный фильтр меню Данные. Отличие этой команды от команды Автофильтр состоит в том, что кроме перечисленных выше возможностей отфильтрованные записи можно вынести в другое место рабочего листа Excel, не испортив исходный список.
Чтобы использовать команду Расширенный фильтр, надо сначала создать таблицу критериев, которую следует разместить на;том же рабочем листе, что и исходный список, но так, чтобы не скрывать его во время фильтрации.
Для формирования таблицы критериев необходимо скопировать имена полей записей БД (имена столбцов списка) в ту часть рабочего листа, где будет располагаться таблица критериев.
При этом число строк в этой таблице определяется только числом критериев поиска. Однако включение пустых строк в таблицу критериев недопустимо, поскольку в этом случае будут найдены все записи базы данных. Задание критериев поиска в виде констант требует точной копии имен тех столбцов исходного списка, которые задают условия фильтрации. Кроме таблицы критериев для команды Расширенный фильтр надо определить вид выходного документа. Это означает, что следует скопировать в свободное место рабочего листа имена только тех столбцов списка, ко-1 торые определяют вид выходного документа. Количество строк в выходном документе Excel определит сам.
Таким образом, для выполнения команды Расширенный фильтр надо выполнить три действия:
сформировать в свободном месте рабочего листа таблицу критериев;
сформировать шапку выходного документа;
выделить область поиска в исходном списке.
Использование вычислительного критерия. Ввод вычисляемых критериев поиска в таблицу требует выполнения следующих правил.
1. Формула должна выводить логическое значение истина или
ложь. После выполнения поиска на экран выводятся только те стро
ки, для которых результатом вычисления формулы будет истина.
2. Формула должна ссылаться хотя бы на один столбец в списке.
Использование вычисляемого критерия накладывает существенное ограничение на таблицу критериев. В этом случае имя столбца в таблице критериев, содержащего значение вычисляемого критерия, должно отличаться от имени подобного столбца в исходном списке.
Формирование итогов в электронной таблице осуществляется командой Итоги из меню Данные. Чтобы команда Итоги работала, список должен быть сначала отсортирован. После сортировки можно подсчитать итоги по группам записей. Команда Итоги позволяет подводить итоги более чем по одному полю из группы записей, а также добавлять обрабатываемые поля и применять для вычисления итогов другие функции.
Сводная таблица — это еще один инструмент обработки больших списков с данными. Поскольку в этом случае сразу подводятся итоги, выполняются сортировка и фильтрация списков, то сводная таблица является более мощным инструментом обработки данных, который называется «Мастер сводных таблиц». Для создания сводной таблицы необходимо выполнить следующие шаги:
выделить область списка;
в меню Данные выбрать команду Сводная таблица, по которой на экран выводится окно Мастера сводных таблиц;
щелкнув по кнопке Далее, перейти к следующему шагу.
С помощью мыши можно перетащить поля списка из правой части окна Мастера сводных таблиц в области Строки, Столбца и Данных в том же окне. Таким образом формируется вид выходного документа.
Последний шаг построения сводной таблицы определяет, где будет размещена сводная таблица. Рекомендуется размещать ее на отдельном рабочем листе. При необходимости можно сводную таблицу озаглавить.
Для печати электронной таблицы ее следует предварительно подготовить. Для этого необходимо:
разбить ЭТ на страницы;
установить параметры страницы;
создать необходимые колонтитулы;
создать заголовки для печати, чтобы шапка таблицы выводи
лась на каждом листе;
просмотреть материал, подготовленный для печати.
Чтобы разбить ЭТ на страницы, необходимо вставить горизонтальные разделители конца страницы в требуемых местах ЭТ. Для этого надо выполнить следующее: выделить строку, с которой надо начать новую страницу; в меню Вставка выполнить команду Разрыв страницы;
В результате Excel вставит широкую пунктирную линию (конец страницы) сверху от выделенной строки.
Аналогично можно разбить на страницы остальную часть ЭТ.
Для установки параметров страницы в меню Файл следует выбрать команду Параметры страницы, определив размеры полей, наличие и расположение колонтитулов, а также наличие заголовков таблицы и другие параметры.
Вопросы и задания
1. Что такое электронная таблица и каковы ее основные функции?
2. Укажите типы данных, используемые в электронных таблицах.
3. Какой класс задач решается с помощью электронных таблиц?
4. Чем отличается абсолютный адрес ячейки от относительного?
5. Что такое рабочий лист и рабочая книга?
6. Какие возможности предоставляет Excel при работе с диаграммами?
7. Какими средствами располагает Excel для сортировки и выборки данных?
8. Какие ограничения имеет сортировка в Excel?
9. Что такое фильтрация данных?
10.В чем отличие автофильтра от расширенного фильтра?
11.Как автоматически просуммировать данные в строках и столбцах?
12.Что такое мастер формул?
13.Чем отличается расширенный фильтр от автофильтра?
14.Что такое сводная таблица и как ее создать?
15.Что такое связывание объектов в Excel? Для чего используются связи?
16.Как установить связь между данными разных рабочих листов в
одной рабочей книге?
17.Как установить связь между данными из разных рабочих книг?
18.Какую проблему позволяет решить установление связей между разными таблицами в Excel?
Microsoft Excel позволяет работать с реляционными структурами, которые носят название списки или базы данных Microsoft Excel. Список – таблица прямоугольной конфигурации, столбцы таблицы счи-таются полями, строки – записями базы данных реляционного типа. Список должен отвечать ряду тре-бований:
– список целиком помещается на одном рабочем листе, максимальный размер списка – 65 536 строк и 256 столбцов;
– список должен оделяться от других данных рабочего листа хотя бы одним незаполненным столб-цом и/или строкой;
– имена столбцов списка располагаются в первой строке таблицы, имена могут быть многостроч-ными;
– ячейки одного столбца списка содержат однородную информацию.
Традиционные операции со списками:
– ввод и редактирование данных списка с помощью экранной формы;
– сортировка – упорядочивание записей (строк/столбцов таблицы) списка;
– фильтрация (отбор) записей списка по условиям;
– агрегирование информации списка, вычисление статистических оценок (сводные таблицы, проме-жуточные итоги);
– структурирование данных (формирование группировок строк и столбцов);
– консолидация (объединение данных нескольких списков с формированием общих итогов).
Сортировка списков
Технология сортировки для всех таблиц прямоугольной конфигурации, включая списки Microsoft Excel, единая. Особенностью списков является то, что они сортируются построчно. Таблицы могут сортиро-ваться как по строкам, так и по столбцам. Для сортировки записей таблицы выделяется сортируемая об-ласть, поэтому допускается сортировка части таблицы, при этом правильность операции сортировки должна контролироваться пользователем.
Примечание.Если списокMicrosoft Excelотделяется от прочей информации хотя бы одной пустойстрокой или пустым столбцом, достаточно установить курсор в любую ячейку списка перед выполне-нием команды сортировки.
Результат сортировки списка всегда остается на том же месте. Для сортировки можно задать один, два или три ключа. Направления сортировки – по возрастанию или убыванию указывается для каждого ключа в отдельности. При сортировке по возрастанию упорядочение идет: от меньшего значения к большему, по алфавиту или в хронологическом порядке дат. Сортировка по убыванию использует об-ратный порядок, исключение – пустые ячейки, которые располагаются в конце списка. Если данные в ячейках имеют смешанный формат, установлены приоритеты:
3. логические значения;
4. значения ошибок;
5. пустые ячейки.
Сортировка по одному ключу.
Для выполнения простой сортировки (сортировки по одному ключу), необходимо:
1. установить курсор в ячейку, содержащую имя ключевого поля, значения которого будут отсорти-рованы;
2. нажать кнопку на Стандартной панели инструментов Сортировка по возрастанию (или
| |
| |
| |
Сортировка по убыванию).
Сложная сортировка.
При выполнении сложной сортировки, т.е. по двум, трем можно и по одному ключу с установкой до-полнительных параметров, нужно выполнить команду меню Данные►Сортировка и ввести парамет-ры сортировки в окно диалога Сортировка диапазона . Блок ячеек списка всегда включает имена полей, поэтому нужно указать Идентифицировать поля по подписям. Выбираются ключи сортировки и на-правление сортировки – по возрастанию или убыванию значений этих ключей. Кнопка обеспечивает дополнительную настройку для выполнения операции сортировки списка:
– выбор способа сортировки по первому ключу;
– различие прописных и строчных букв;
– тип сортировки таблицы – по строкам или по столбцам.
Фильтрация списков
Различают два способа фильтрации (отбора) записей списка в команде меню Данные►Фильтр:
АвтофильтриРасширенный фильтр.
Автофильтр.
Автофильтр создает для каждого поля список значений, используемый для отбора записей: Все –нет ограничений на значения поля;
Первые 10 –вывод установленного числа или процент записей с наибольшими/наименьшимизначениями поля;
Условие –формирование условий отбора в Пользовательском автофильтре; Конкретное значение –отбор записей с указанным значением поля; Пустые –в поле пусто(нет значений); Непустые –в поле содержится какое-либо значение.
Условие для первого поля формирует записи списка Microsoft Excel. Условие для второго поля фильтрует результат предыдущей фильтрации и т.д. Все условия отбора связаны логической связкой И. Если условие задается по одному полю то, с помощью пользовательского автофильтра условия могут быть связаны как связкой И, так и связкой ИЛИ. Для задания условий отбора могут использоваться шаблоны – заменители отдельных символов – ? или всех символов – *.
В списке в результате фильтрации остаются видимыми лишь записи, удовлетворяющие заданным условиям отбора. Отобранные записи можно копировать, удалять или редактировать. Для сброса всех
условий фильтрации служит команда меню Данные►Фильтр►Показать все. Для отказа от автофильтра повторно выполняется команда меню Данные►Фильтр► Автофильтр.
Расширенный фильтр.
Расширенный фильтр предполагает формирование специального диапазона ячеек в произвольном месте. Этот диапазон содержит критерии отбора для расширенного фильтра списка. Диапазон условий состоит из имен полей списка и строк условий.
В области диапазона условий для расширенного фильтра можно сформулировать несколько строк условий, связанных между собой логической связкой ИЛИ. При этом условия фильтрации внутри одной строки условий связаны логической связкой И.
Команда меню Данные►Фильтр►Расширенный фильтр выводит окно диалога для указания диапазона ячеек: исходного диапазона – списка Microsoft Excel, диапазона условий, диапазона ячеекдля вывода результата фильтрации.
Результат расширенного фильтра можно скопировать в новое место, он может включать все отобранные или только уникальные записи. Исходный диапазон – список Microsoft Excel, диапазон условий и копия отфильтрованных записей могут находиться на одном и том же листе, на разных листах одной книги и даже в различных открытых книгах.
Существуют два способа фильтрации записей списка с использованием расширенного фильтра Microsoft Excel: фильтровать список на месте и копировать рузультат фильтрации в новое место.
Часто в повседневной работе приходится следить за большими объемами информации — списками почтовой рассылки, телефонов, торговых операций и т. д. Приложение Excel упрощает решение таких задач с помощью содержащихся в нем средств обработки списков.
Списком называется таблица с данными, разделенная на столбцы-поля и строки-записи. В сущности, список представляет собой базу данных, но поскольку он хранится в книге Excel, а не в файлах специального формата, созданных программами типа Access или FoxPro, то фирма Microsoft употребляет для Excel термин список.
1.Использование Списка ячеек в качестве базы данных
Список — это набор строк (подчиняющийся несколько более строгим ограничениям, чем обычный лист с постоянными заголовками столбцов), в которых хранятся данные постоянного формата. Для построения Листа, способного воспринимать все команды Excel по обработке списков, существуют определенные принципы.
Создаваемый Список должен иметь постоянное количество столбцов. Количество строк является переменным, что позволяет позднее добавить, удалить или переставить записи для того, чтобы список содержал оперативную информацию. В каждом столбце должна содержаться однотипная информация, и в списках недопустимы пустые строки или столбцы.
Создание Списка в Excel:
1. Открыть новую Книгу или новый Лист в существующей Книге. Лучше всего размещать списки на отдельных Листах, чтобы Excel смог автоматически выделить данные при выполнении команд обработки списков.
2. Создать Заголовки для каждого Поля в Списке, задать их выравнивание и отформатировать жирным шрифтом.
3. Отформатировать находящиеся под заголовками ячейки в соответствии с данными, которые в них должны храниться. Это подразумевает наложение числовых форматов (денежных или даты), изменение выравнивания и т. д.
4. Ввести новые записи (то есть данные) под Заголовками. (См. Рис.1)При этом следует соблюдать единый стиль заполнения, чтобы позднее взаимосвязанные записи могли быть выделены в группы. Количество строк может быть любым; в списке не должно быть пустых строк (даже между заголовками столбцов и первой записью). Каждый столбец представляет собой Поле, содержащее однородную информацию. Каждая строка соответствует Записи данных
5. После завершения сохранить Книгу. При этом следует продумать меры предосторожности — создание резервной копии, тем более, если Список содержит важную информацию.
Рис. 1. Список на листе Excel, дополненный Заголовками столбцов
Автозавершение значений ячеек помогает при вводе повторяющихся значений в списках — вводимые слова распознаются и автоматически завершаются (См. Рис.1). Чтобы воспользоваться им, необходимо выполнить:
Кнопка Office – кнопка Параметры Excel – соответствующее диалоговое окно –
категория Дополнительно - флажок Автозавершение значений ячеек (См. Рис.2)
Чтобы Excel смог правильно распознать данные для группировки, сортировки и вычислений, необходимо, чтобы повторяющиеся названия и другие данные в точности совпадали в разных записях.
Рис. 2. Диалоговое окно Параметры Excel - флажок Автозавершение значений ячеек
2.Использование Формы для ввода данных
Для облегчения обработки данных в списках в Excel имеется инструмент Форма, предназначенный для добавления, удаления и поиска записей.
Кнопка Форма должна быть установлена на Панель Быстрого Доступа (См. Рис.3):
Рис. 3. Создание кнопки Форма на Панели Быстрого Доступа
Кнопка Office – кнопка Параметры Excel – соответствующее диалоговое окно – категория Настройка – раскрывающийся список Выбрать команды из - опция Все команды – выбор кнопкиФорма,затемкнопка Добавить.
Установленная наПанель Быстрого Доступа кнопкаФормавызывает при выделенном Списке диалоговое окно с полями из Списка и несколькими управляющими кнопками (См. Рис. 4). По умолчанию в окне выводится первая запись, но вертикальная полоса прокрутки позволяет перейти к остальным. Приложение Excel добавляет новые записи в конец списка; чтобы вызвать пустую запись, следует прокрутить список до конца или нажать кнопку Добавить.
Хотя записи достаточно часто вводятся непосредственно на Листе, инструмент Форма предоставляет полезную альтернативу (например, для менее квалифицированных работников, которым поручен ввод данных), и в некоторых случаях работа с ней происходит быстрее.
Рис. 4. Команда Форма предоставляет дополнительные средства для заполнения строк и столбцов списка
3.Проверка данных при вводе
Если с Листом работают сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. Например, можно потребовать, чтобы в столбец Месяц можно было ввести только январские и февральские даты или чтобы сумма в столбце Объем входила в определенный диапазон. В Excel выполнение подобных условий проверяется при помощи инструмента Проверка вода. С его помощью возможно частично или полностью защитить Лист от ошибок ввода, которые приводят к неправильному вычислению формул или к нарушению работы средств обработки списков.
Критерий правильности ввода для определенного диапазона ячеек задается следующим образом:
1. Выделить ячейки столбца, для которого устанавливается проверка ввода. В выделенный диапазон должны войти как ячейки с данными, так и нижние пустые ячейки, куда будут вводиться новые записи.
Вкладка Данные - Группа Инструментов Работа с данными — раскрывающийся список Проверка данных
– кнопка Проверка данных - соответствующее диалоговое окно - вкладка Параметры (См. Рис. 5).
Рис. 5. Диалоговое окно Проверка данных - вкладка Параметры
3. В разделе Условие проверки выбрать формат значений в выделенных ячейках из списка Тип данных:
· Любое значение - используется для отмены проверки ввода,
· Целое число,
· Действительное,
· Список,
· Дата,
· Время,
· Длина текста,
· Другой - формат, для которого можно ввести собственную формулу.
При выборе значения из списка Тип данных появляются дополнительные текстовые поля для ввода дополнительных условий или ограничений — например, минимального и максимального допустимого значения (См. Рис. 5).
С помощью MS Excel можно создавать и обрабатывать базы данных (Список).Список – это совокупность строк листа, содержащих однотипные данные (рис. 1). Список может использоваться в качестве базы данных, в которой записи соответствуют строкам списка, а поля – столбцам. Например, если базой данных считать телефонный справочник, то полями записи будут фамилии, номера телефонов и адреса абонентов.
Рис. 1. База данных (список) MS Excel
Для использования в Microsoft Excel списка в качестве базы данных не требуется каких-либо подготовительных действий. При выполнении характерных для баз данных операций, таких как поиск, сортировка или подведение итогов, список автоматически обрабатывается как база данных.
MS Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям.
1. Список обязательно должен содержать строку заголовков.
2. В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.
3. В списке не должно быть пустых строк.
4. Рекомендуется помещать список на отдельный лист. Но если все же на лист нужно поместить еще и другую информацию, следите, чтобы список от нее отделялся хотя бы одной пустой строкой или одним пустым столбцом. В противном случае можно получить, например, сотрудника с фамилией Итого.
На рабочем листе выделяют следующие области.
1. Диапазон данных – область, где хранятся данные списка. Данные, связанные друг с другом, записываются в отдельные строки, каждому столбцу соответствует свое поле списка с уникальным именем поля.
2. Диапазон критериев – область на рабочем листе, в которой задаются критерии для поиска информации. В диапазоне критериев указываются имена полей и отводится область для записи условий отбора.
3. Диапазон для извлечения – область, в которую MS Excel копирует выбранные данные из списка. Этот диапазон должен быть расположен на том же листе, что и список.
Сортировка и фильтрация в электронных таблицах Excel
Как правило в электронных таблицах Excel хранятся большие объемы данных, причем данные эти однотипны, например есть всего три столбца ФИО, должность, оклад и 1000 строк которые хранят данные о фамилии, окладе и должности струдников организации. Такой способ хранения данных позволят нам быстро сортировать и фильтровать строчки таблицы.
Так что же такое сортировка и фильтрация в экселе? Под сортировкой обычно по- нимаю упорядочивание следования строк в таблице. Сортировка как правило происходит по одному или нескольким столбцам и может производиться как по возрастанию значения так и по убыванию. Фильтрация - это средство при помощи которого можно выбрать строки которые соответствуют некоторому набору условий.
Например, при помощи фильтрации можно быстро выбрать людей с заданым окла- дом, или должностью. В экселе как вы уже догадались присутствует возможность для использования всех этих инструментов и в этой статье я расскажу о том как их использовать. Для начала нам потребуется некоторый набор исходных данных для экспериментов. Придумаем некоторый список состоящий из фамилий людей, их росте, весе, возрасте и стране рождения.
Фамилия | Рост | Вес | Возраст | Страна |
Воробьев | Россия | |||
Воронин | Украина | |||
Галкин | Украина | |||
Синицын | Украина | |||
Сорокин | Россия | |||
Чижов | Россия |
Первым делом попробуем отсортировать табличку, благо это совсем не сложно. Для этого нужно выделить мышкой ту область на листе в которой хранятся наш данные. При этом важно не забыть выделить еще и заголовки столбцов, так как с ними отсортировать строчки будет проще.
В меню "Главная" нужно найти пункт "Сортировка и фильтр" и выбрать в ней "Настраиваемая сортировка". В открывшемся окне мы будем задавать параметры которые будут использоваться при сортировке:
Как я уже писал выше, можно сортировать строки по нескольким столбцам сразу, это означает, что если при сортировке по первому столбцу встретятся одинаковые значения (например одинаковый вес) , то решение о том какую строчку поставить выше а какую ниже будет приниматься исходя из данных во втором столбце который участвует в сортировке.
Для того чтоб выбирать названия столбца а не просто его номер, нужно чтоб галочка "Мои данные содержат заголовки" была установлена. Именно поэтому я попросил выделить не только данные, но еще и заголовки столбцов.
Если посмотреть варианты сортировки, то в Excel 2010 они довольно впечатляют, сортировать можно не только по какому либо значению, но и даже по цвету (!). На мой взгляд полезность такой фичи весьма сомнительна. После нажатия кнопки ОК, эксель отсортировал строки следующим образом:
Сортировка это конечно здорово но как выбрать из громадного объема данных только те которые мы хотим видеть? Конечно, если нам надо посмотреть человека например с минимальным весом, то просто сортируем и смотрим конец (или начало) таблицы. А что если нам нужно выбрать к примеру людей с возрастом от 21 до 30? Или только русских, или только высоких и худых ?
В этом случае нам на помощь приходят фильтры экселя. Это инструмент при помощи которого мы можем сделать всё вышеописанное. Работать с ним не сложнее чем с сортировкой, рассмотрим его на практике. Для этого попробуем выбрать из той же самой таблицы строки с фамилиями людей чей рост больше или равен 180, а вес меньше 100 кг.
Что бы применить такой фильтр нужно нажать "Главная" ->"Сортировка и фильтр" -> "Фильтр". Как и в случае с сортировкой не забываем выделять не только данные но и заголовок. Сразу после выбора этого пункта меню, в заголовке каждого столбца у нас появятся галочки. Нажимая на них можно давать фильтрацию для конкретного столбца. Применим фильтр который отсеет людей ниже 180 сантиметров. Щелкаем по галочке и выбираем там Числовые фильтры -> Больше или равно.
В открывшемся окне вводим что нас интересуют люди от 180 и выше. После нажатия кнопки ОК фильтр начнет действовать и из таблицы исчезнут некоторые строки и остануться только такие:
Фамилия | Рост | Вес | Возраст | Страна |
Галкин | Украина | |||
Сорокин | Россия | |||
Чижов | Россия |
Теперь из оставшихся людей выберем тех кто весит менее 100 кг. Для этого применим второй фильтр, но уже для столбца "Вес". Действовать в этом случае нужно аналогичным образом, только в числовых фильтрах нужно выбирать "Меньше" и вписать число 100 в поле открывшегося окна. После этой манипуляции было бы логично предположить, что останется одна единственная строчка с Сорокиным:
Фамилия | Рост | Вес | Возраст | Страна |
Сорокин | Россия |
Я уверен что в ходе всех этих действий вы получили тот же самый результат и освоили такую полезнейшую вещь как сортировка и фильтрация в Excel.
Сортировка и фильтрация - порядок действий
Откройте в Microsoft Excel электронную таблицу, данные которой требуют сортировки.
Если нужна сортировка по данным лишь одной колонки, то кликните в ней любую ячейку правой кнопкой мыши. В контекстном меню, которое вызовет это действие, раскройте раздел «Сортировка». В него собраны пять способов упорядочивания данных - выберите нужный вариант. Два верхних пункта предназначены для сортировки по возрастанию и убыванию, а три других ставят в начало таблицы те строки, в которых ячейки этой колонки выделены цветом, шрифтом или символом.
Сокращенный вариант этого списка можно вызвать и через меню табличного редактора - для этого используйте кнопку «Сортировка» в группе команд «Редактирование» на вкладке «Главная». В этом случае список будет содержать только команды упорядочивания по возрастанию и убыванию.
Если требуется более сложная сортировка данных сразу по нескольким столбцам, выберите строку «Настраиваемая сортировка» - в обоих описанных выше вариантах она присутствует в меню дополнительным пунктом.
После выбора этого пункта на экране появляется отдельное окно для настройки порядка сортировки. В выпадающем списке «Сортировать по» выберите колонку, данные которой должны быть отсортированы в первую очередь. В аналогичном списке под надписью «Сортировка» выберите объект упорядочивания - значения, цвет, шрифт или значок. В третьем выпадающем списке укажите порядок сортировки - по возрастанию, убыванию или по заданному списку. При выборе третьего пункта откроется дополнительное окно, в котором надо либо ввести свой список, либо выбрать один из уже имеющихся.
Для настройки следующего уровня сортировки нажмите кнопку «Добавить уровень» и в окне появится еще один ряд точно таких же выпадающих списков. Повторите с ними операции предыдущего шага. Если требуется больше уровней, проделайте этот шаг нужное количество раз.
Читайте также:
- Vba excel передача параметров в процедуру
- Итоговое практическое занятие по microsoft word принцип работы с буфером обмена
- Для создания параллельных программ в openmp применяется следующая модель программирования
- Не найдены исполнители для автоподстановки 1с документооборот
- Документ не соответствует штатному расписанию 1с зуп