Создание сводных таблиц в MS Excel. Макрокоманды.

Сводная таблица - это отчет, позволяющий просмотреть данные в более удобном и понятном виде. Чаще всего сводные таблицы используются для организации отображения данных из БД, но также есть возможность создавать сводные таблицы и с данных находящихся в книге. Важное требование, это структурно-организованная таблица (по типу реестра), где имеются поля и записи (строки). Например, таблица, перечисляющая сотрудников, имеет поля: Фамилия, Имя, Отчество, Должность, Дата рождения, Зарплата и т.д.

Для создания сводной таблицы воспользуемся ранее созданной простой таблицей.

Выберете команду меню Данные – Сводные таблицы. Будет открыт первый диалог мастера создания сводной таблицы.

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

Выберем конкретно для нашего примера: В списке или базе данных, вид создаваемого отчета – сводная таблица.

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

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

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

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

После того как мы проделали все эти операции нажимаем кнопку Готово в заключительном окне диалога мастера.

Макрокоманда, макроопределение или мá крос — программный алгоритм действий, записанный пользователем. Часто макросы применяют для выполнения рутинных действий. А также макрос — это символьное имя в шаблонах, заменяемое при обработке препроцессором на последовательность символов, например: фрагмент html-страницы в веб-шаблонах, или одно слово из словаря синонимов в синонимизаторах.

Для задания макрокоманды нужно:

1. Установить курсор в начальное положение.

2. Установить для макрокоманды клавишу и включить запись (СЕРВИС-МАКРОС-НАЧАТЬ ЗАПИСЬ).

3. Выполнить редактирующие действия над элементом.

4. Выполнить подготовку к работе со следующим элементом.

5. Выключить запись (кнопка в окне Записи).

Действия на ПК

1. Поставить курсор в начало первой строки

2. Затем СЕРВИС ––> МАКРОС ––> НАЧАТЬ ЗАПИСЬ ––> в появившимся диалоговом окне Запись макроса набрать Имя макроса Обмен ––> в окошке Назначить макрос нажать кнопку КЛАВИШАМ ––> в следующем диалоговом окне в окошке Новое сочетание клавиш с клавиатуры нажать клавишу F2 ––> Назначить ––> Закрыть

3. Чтобы выделить первое слово в строке с клавиатуры, надо удерживая клавиши Shift и Ctrl, нажать клавишу

4. Найти на панели инструментов Стандартная кнопку – вырезать

5. Переместить курсор в конец строки клавишей

6. Нажать пробел

7. Вставить из буфера кнопкой на панели инструментов

8. Отправить курсор в начало новой строки клавиша + клавиша

9. Остановить запись на панели инструментов Макрос, нажав соответствующую кнопку

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

и нажать ОК. Затем следует добавить нумерацию в уже отсортированный список по алфавиту.

10. Макрос готов. Сдайте работу учителю для выставления оценки.

Вопрос 24(Фильтрация списка в MS Excel: автофильтр, расширенный фильтр. Расчет промежуточных итогов).

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

Первый – автофильтр, предназначен для наиболее простых операций – выделение записей с конкретным значением (например, только выделение только записей, относящихся к Леброну Джеймсу), данных, лежащих в определенном диапазоне (или выше среднего или первую десятку) или ячеек/шрифтов определенного цвета (кстати, очень удобно). Соответственно, пользоваться им очень просто. Вам достаточно выделить те данные, которые вы хотите видеть отфильтрованными. Потом команда «Данные»/ «Фильтр». На каждой верхней ячейке верхней таблицы появится флажок списка, там уже легко разобраться с каждой командой, освоить просто и объяснять, я надеюсь, дальше не нужно, только нюансы использования автофильтра:

1) Работает только с неразрывным диапазоном. Два разных списка на одном листе отфильтровать уже не получится.

2) Самая верхняя строчка таблица автоматически назначается заголовком и в фильтрации не участвует.

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

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

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

1) Задает столько условий, сколько необходимо.

2) Позволяет выделить ячейки с уникальными (неповторяющимися) данными. Это часто бывает нужно в работе с данными и опция отлично справляется с проблемой.

3) Позволяет копировать результат фильтра в отдельное место, не трогая основной массив.

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

Примеры условий:

1) ‘L*’ – ячейки начинающиеся с L

2) ‘> 5’ - данные больше 5

Нюанс:

Если вы удаляете из отфильтрованной таблицы строки, то они удалятся, не прихватывая с собой соседей. Т.е. если таблица отфильтрована и показывает строчки с 26-29 и 31-25, выделение всех строк и их удаление не повлечет удаление строчки 30. Это удобно, лично я часто пользуюсь этим при написании макросов. Какое преимущество это дает – часто нам достаются таблицы, которые надо привести в рабочий вид, т.е. удалить, например пустые строки. Что мы делаем: применяем фильтр к таблице, показывая только те строки, которые нам не нужны, затем удаляем всю таблицу, включая заголовок. Удаляются ненужные строки и заголовок, при этом таблица не имеет пробелов и составляет единый диапазон. А строку заголовков можно добавить простой операций копирования из загодя заготовленной области. Почему это важно при написании макросов? Неизвестно, с какой строки начинаются нежелательные данные и непонятно, с какой строки их начать удалять, удаление всей таблицы помогает быстро решить эту проблему.