Обработка списков в Microsoft Excel: сортировка, фильтрация, создание итоговых таблиц. Списки и базы данных Microsoft Excel

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

– список целиком помещается на одном рабочем листе, максимальный размер списка – 65 536 строк и 256 столбцов;

– список должен оделяться от других данных рабочего листа хотя бы одним незаполненным столб-цом и/или строкой;

– имена столбцов списка располагаются в первой строке таблицы, имена могут быть многостроч-ными;

– ячейки одного столбца списка содержат однородную информацию.

– ввод и редактирование данных списка с помощью экранной формы;

– сортировка – упорядочивание записей (строк/столбцов таблицы) списка;

– агрегирование информации списка, вычисление статистических оценок (сводные таблицы, проме-жуточные итоги);

– структурирование данных (формирование группировок строк и столбцов);

– консолидация (объединение данных нескольких списков с формированием общих итогов).

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

Для выполнения простой сортировки (сортировки по одному ключу), необходимо:

1. установить курсор в ячейку, содержащую имя ключевого поля, значения которого будут отсорти-рованы;

2. нажать кнопку на Стандартной панели инструментов Сортировка по возрастанию (или

– тип сортировки таблицы – по строкам или по столбцам.

Различают два способа фильтрации (отбора) записей списка в команде меню Данные►Фильтр:

АвтофильтриРасширенный фильтр.

Автофильтр создает для каждого поля список значений, используемый для отбора записей: Все –нет ограничений на значения поля;

Первые 10 –вывод установленного числа или процент записей с наибольшими/наименьшимизначениями поля;

Условие –формирование условий отбора в Пользовательском автофильтре; Конкретное значение –отбор записей с указанным значением поля; Пустые –в поле пусто(нет значений); Непустые –в поле содержится какое-либо значение.

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

условий фильтрации служит команда меню Данные►Фильтр►Показать все. Для отказа от автофильтра повторно выполняется команда меню Данные►Фильтр► Автофильтр.

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

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

Команда меню Данные►Фильтр►Расширенный фильтр выводит окно диалога для указания диапазона ячеек: исходного диапазона – списка Microsoft Excel, диапазона условий, диапазона ячеекдля вывода результата фильтрации.

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

Существуют два способа фильтрации записей списка с использованием расширенного фильтра Microsoft Excel: фильтровать список на месте и копировать рузультат фильтрации в новое место.

Последнее изменение этой страницы: 2017-05-05; Просмотров: 2414; Нарушение авторского права страницы

lektsia.com 2007 — 2024 год. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав! (0.02 с.) Главная | Обратная связь

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

Полезные функции Microsoft Excel – руководство для новичков

1. Между ячейками данных списка и ячейками диапазона условий должна быть хотя бы однапустая строка или столбец.
2. Для формирования диапазона условий необходимо скопировать заголовки столбцов, для которых задаются условия выборки (можно скопировать заголовки всех столбцов, но под заголовками тех столбцов, для которых условия фильтрации не задаются, ничего не записывается, т.е. ячейки остаются пустыми).
3. Под каждым заголовком с помощью знаков операций сравнения (>, =) записывается условие фильтрации по данному полю. В одной ячейке записывается только одно условие.
4. Если условия фильтрации связаны логическим И, ячейки, в которые записываются эти условия, располагают в одной строке, а если условия связаны логическимИЛИ, они должны быть расположены в разных строках.

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

Диапазон условий сформирован:

Поскольку с нашем случае в списке студентов нет групп с номером, большим 12, то диапазон условий может быть записан и следующим образом:

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

Создание автоматически заполняемых списков в Excel — Центр обучения

Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».

Мы будем использовать функцию ЕСЛИ для меня, хорошо ли мне идти? (или как вы это называли), чтобы определить, все ли флажки установлены. Выберите ячейку справа от меня. Хорошо ли мне идти? и введите следующий текст.

Двухуровневый выпадающий список в Excel

• Перейдите на вкладку Главная, нажмите «Найти и выделить» и после раскрытия меню нажмите Перейти → Специальные. Вы также можете сделать это с помощью сочетания клавиш Ctrl + G ;
• Выберите параметр «Проверка данных», и будут выбраны все раскрывающиеся списки, доступные на листе.

Знак & позволяет комбинировать текстовые элементы без использования дополнительных функций. В этом случае в выбранной ячейке введите = → добавьте координаты ячейки → введите знак & → добавьте пробел в формате » » (если он нужен) → введите знак & → затем добавьте координаты другой ячейки, например, =A1&» «&B1 и нажмите Enter . Вы можете объединить сразу несколько ячеек.