Работа с большими массивами данных (списками) в Excel

Основные задачи:

1. Организация ввода

2. Просмотр данных

3. Подведение итогов

4. Сортировка и фильтрация данных в таблицах

Список Excel содержит:

• 1-ую строчку (шапка таблица, строка заголовка) – заголовок полей.

• Строки – записи

• Столбцы – поля записи (в столбце находятся данные одного типа)

Закрепление «шапки» и «боковика» таблицы

Закрепление областей – закрепление на экране части листа при прокрутке остальной его части

Закрепление определенных строк и столбцов:

1. Выделить ячейку, выше и левее которой необходимо присутствие данных

2. Вид ОкноЗакрепить области

Сортировка:

Сортировка – это упорядочивание записей в таблице

1. Выделите любую ячейку столбца, в котором необходимо привести сортировку.

2. ГлавнаяРедактирование Сортировка и фильтр или Данные Сортировка и фильтр Сортировка

3. Действия:

   1. Сортировка по возрастанию – сортировка от А до Я

   2. Сортировка по убывани – сортировка от Я до А

   3. Настраиваемая сортировка…

Настраиваемая сортировка позволяет сортировать данные по нескольким элементам

Фильтры:

Фильтр позволяет легко и быстро найти для работы данные в диапазоне ячеек или столбце таблицы

Используют:

1. Автофильтр – назначение средств фильтрации на каждое поле данных таблицы

2. Расширенный фильтр использует более сложные критерии, чем автофильтр

Автофильтр позволяет фильтровать данные:

1. По значениям списка

   1. Числовым значениям

   2. Текстовым значения

   3. По дате

2. По формату

3. По условиям

Автофильтр (установка):

1. Поместить курсор в таблицу

2. Главная Редактирование Сортировка и фильтрФильтр или ДанныеСортировка и фильтрФильтр

Расширенный фильтр:

• Диапазон данных – область, где хранятся данные списка

• Диапазон критериев – область на рабочем листе, в котором задаются критерии для поиска информаци.

• Диапазон для извлечения – область, в которую Excelкопирует выбранные данные из списка. Этот диапазон д.б. расположен на том же листе, что и список.

Критерий поиска – таблица, состоящая из:

• Строки заголовков – название полей

• Строки критериев – условий отбора

Если критерии находятся в одной строке, то они работают по принципу «И». Если в разных – по принципу «ИЛИ».

В критерии можно использовать шалоны с ? и *

Расширенный фильтр

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

2. Заполнить строки критериев

3. Выделить любую ячейку исходной таблицы

4. Данные Сортировка и фильтр  Дополнительно

Исходный диапазон – таблица с исходными данными

Диапазон условий – сформированный критери отбора

Определние области размещения результатов

5. ОК

Итоги (промежуточные итоги)

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

Последовательность действий:

1. В окне открытого листа ОТСОРТИРУЙТЕ диапазон данных, по которому будут подводиться итоги

2. «Данные» «Структура»Промежуточные итоги

3. Задать необходимые параметры

4. ОК

Итоги (параметры)

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

Определить вид «итоговой» функции

Определить столбец, в котором должны отобразиться итоги

Удаление итогов из рабочего стола.

Консолидация

Позволяет объединить данные таблиц, находящихся не только в пределах одной таблицы, но и в разных местах (листах, книгах). Пр и этом структура таблиц должна быть ИДЕНТИЧНОЙ.

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

• Ключевое поле – самое главное поле таблицы или ее выделенной части

Консолидация (порядок действий)

1. Выделить ячейку, начиная с которой необходимо разместить консолидированные данные

2. Данные Работа с данными Консолидация

3. Из списка «Функция» выбирать функцию, которую планируется использовать для консолидации данных

4. В поле ссылка ввести диапазон. Нажать кнопку Добавить. Повторить для всех консолидированных диапазонов (если есть)

5. Установить необходимые флажки

   1. Подписи верхней строки

   2. Значение левого столбца

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

6. Нажать ОК

Сводная таблица

Является средством анализа и трансформации данных

Сводные таблицы могут использоваться для:

1. Обобщения большого количества однотипных данных

2. Реорганизации данных (с помощью перетаскивания)

3. Отбора и группировки данных

4. Построении диаграмм

Процедура создани сводной таблицы:

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

2. Поместить необходимые поля в таблицу для проведения анализа: поля, содержащие информацию – в области страниц, столбцов и строк, а поля с числовыми данными – в область данных

Сводная таблица (порядко действий)

1. Вставка Таблица Сводная таблица

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

3. ОК

4. На листе появится заготовка для сводной таблицы и связанные с ней вкладки на Ленте: Параметры и Конструктор

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