Выборка данных в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Выборка данных в Microsoft Excel

Как сделать выборку в Excel из списка с условным форматированием

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

Выбор уникальных и повторяющихся значений в Excel

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

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

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

  1. Выделите первый столбец таблицы A1:A19.
  2. Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
  3. В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
  4. Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

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

Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

Перед тем как выбрать уникальные значения из списка сделайте следующее:

  1. Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
  2. На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
  3. В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

В результате в ячейке B1 мы создали выпадающих список фамилий клиентов.

Примечание. Если данные для выпадающего списка находятся на другом листе, то лучше для такого диапазона присвоить имя и указать его в поле «Источник:». В данном случае это не обязательно, так как у нас все данные находятся на одном рабочем листе.

Выборка ячеек из таблицы по условию в Excel:

  1. Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
  2. Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

Как работает выборка уникальных значений Excel? При выборе любого значения (фамилии) из выпадающего списка B1, в таблице подсвечиваются цветом все строки, которые содержат это значение (фамилию). Чтобы в этом убедится в выпадающем списке B1 выберите другую фамилию. После чего автоматически будут выделены цветом уже другие строки. Такую таблицу теперь легко читать и анализировать.

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

Выборка данных в Microsoft Excel

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

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

1. Характеристика пакета Excel

Пакет Excel оснащен средствами статистической обработки данных. И хотя Excel существенно уступает специализированным статистическим пакетам обработки данных, тем не менее этот раздел математики представлен в Excel наиболее полно. В него включены основные, наиболее часто используемые статистические проце­дуры: средства описательной статистики, критерии различия, корреляционные и другие методы, позволяющие проводить необходимый статистический анализ эко­номических, психологических, педагогических и медико-биологических типов данных.

Каждая единица информации занимает свою собственную ячей­ку (клетку) в создаваемой рабочей таблице. В каждой рабочей таблице 256 столбцов (из которых в новой рабочей таблице на экране видны, как правило, только первые 10 или 11 (от А до J или К) и 65 536 строк (из которых обычно видны только первые 15-20). Каждая новая рабочая книга содержит три чистых листа рабочих таблиц.

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

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

2 Использование специальных функций

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

Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число1, число2, . — это от 1 до 30 массивов для которых вычисляется среднее.

Функция МЕДИАНА позволяет получать медиану заданной выборки. Медиана – это элемент выборки, число элементов выборки со значениями больше которого и меньше которого равно.

Функция МОДА вычисляет наиболее часто встречающееся значение в выборке.

Функция ДИСП позволяет оценить дисперсию по выборочным данным.

Функция СТАНДОТКЛОН вычисляет стандартное отклонение.

Функция ЭКСЦЕСС вычисляет оценку эксцесса по выборочным данным.

Функция СКОС позволяет оценить асимметрию выборочного распределения.

Функция КВАРТИЛЬ вычисляет квартили распределения. Функция имеет формат КВАРТИЛЬ(массив, значение), где массив – интервал ячеек, содержащих значения СВ; значение определяет какая квартиль должна быть найдена (0 – минимальное значение, 1 – нижняя квартиль, 2 – медиана, 3 – верхняя квартиль, 4 – максимальное значение распределения).

Пример 1. Провести статистический анализ методом описательной статистики доходов населения в регионе 1 и регионе 2.

Задания для самостоятельной работы

1. Наблюдение посещаемости четырех внеклассных мероприятий в экспериментальном (20 человек) и контрольном (30 человек) классах дали значения (соответственно): 18, 20, 20, 18 и 15, 23, 10, 28. Требуется найти среднее значение, стандартное отклонение, медиану и квартили этих данных.

2. Найти среднее значение, медиану, стандартное отклонение и квартили результатов бега на дистанцию 100 м у группы студентов (с): 12,8; 13,2; 13,0; 12,9; 13,5; 13,1.

3. Определите верхнюю и нижнюю квартиль, выборочную асимметрию и эксцесс для данных измерений роста групп студенток: 164, 160, 157, 166, 162, 160, 161, 159, 160, 163, 170, 171.

4. Найти наиболее популярный туристический маршрут из четырех реализуемых фирмой, если за неделю последовательно были реализованы следующие маршруты: 1, 3, 3, 2, 1, 1, 4, 4, 2, 4, 1, 3, 2, 4, 1, 4, 4, 3, 1, 2, 3, 4, 1, 1, 3.

Читайте также:  Метод аппроксимации в Microsoft Excel

3. Использование инструмента Пакет анализа

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

Для установки пакета Анализ данных в Excel сделайте следующее:

– в меню Сервис выберите команду Надстройки;

– в появившемся списке установите флажок Пакет анализа.

Для использования статистического пакета анализа данных необходимо:

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

Задание для самостоятельной работы

1. В рабочей зоне производились замеры концентрации вредного вещества. Получен ряд значений (в мг./м 3 ): 12, 16, 15, 14, 10, 20, 16, 14, 18, 14, 15, 17, 23, 16. Необходимо определить основные выборочные характеристики.

Работа со списками данных в Excel

Добрый день уважаемый читатель!

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

Для примера используем список или базу данных сотрудников и их личных данных: Для создания правильных списков данных, с которыми эффективно можно работать, необходимо следовать нескольким правилам, а именно:

  • За каждым столбиком должна быть закреплена информация только одного типа. Например, в столбик с данными о днях рождениях вводится только такие данные, с именами сотрудников, только имена, и смешение типов данных недопустимы;
  • Информацию лучше всего делить по максимум. Например, ФИО стоить разделить на три разных поля, так как поиск и работа с данными будет легче (по имени можно поздравить в связи с праздником);
  • В обязательном порядке каждое поле должно иметь заголовок, несмотря на то, что с многоуровневыми «шапками» Excel не очень умело умеет работать;
  • В списке должны отсутствовать пустые строки и столбцы, так как это определяется программой как окончание созданного списка и в дальнейшем создаются проблемы и ошибки при отображении данных;
  • Размещение иных данных в стороне от списка не рекомендуются, так как в момент наложения любого из фильтров они будут скрыты.

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

При работе с объёмными таблицами любой пользователь сталкивается с проблемой, когда заголовки полей уходят за пределы окна и в связи с этим появляется неудобства пользования. Для избегания этого необходимо закрепить «шапку» нашей таблицы, что бы полоса прокрутки не влияла на отображения первых строк и столбцов ваших списков. Это можно осуществить в панели управления, вкладка «Вид», блок «Окно» и нажать выпадающий список «Закрепить область».

Вариантов закрепить область прокрутки всего три, это:

  1. Закрепить область – производится закрепление области слева и сверху от текущей ячейки, то есть по горизонтали и вертикали одновременно;
  2. Закрепить верхнюю строку – закрепление по горизонтали сверху установленной ячейки таблицы;
  3. Закрепить первый столбец – произвести вертикальное закрепление списков слева от установленной ячейки.

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

Отбор с помощью фильтра

Excel предоставляет возможность отфильтровать ваши списки по заданным критериям двумя вариантами, с помощью:

  1. Расширенного фильтра;
  2. Автофильтра.

Если вы будете использовать автофильтр, то данные, которые не соответствуют указанным условиям, будут спрятаны. В документе отражаться будут только отобранные по критериям записи в одном столбике.

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

Детально работу с фильтрами, я описал в статье «Автофильтр в Excel» с которой вы можете ознакомиться, перейдя по соответствующей ссылке.

Создаем промежуточные отчёты

Очень часто возникает необходимость группировать данные списков по определенным показателям с расчётами по ним итогов. Так создаются удобные и очень полезные в работе отчёты и анализы, прекрасный инструмент для любого бухгалтера и экономиста. Создать такой детализированный список данных в Excel с выделением групп и подбитием итогов по группам и общий по полю, не очень трудно. Всё это можно произвести в несколько шагов, но обязательным условием применения промежуточных итогов к спискам, это сортировка данных по полю для которого создается итог. Подробно и в деталях об этом можно узнать, прочитав статью «Промежуточные итоги в Excel», перейдя по ссылке.

Сортируем свои списки

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

Работаем со сводными таблицами

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

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

Для создания сводной таблицы необходимо установить курсор на любую ячейку таблицы или базы данных, и на панели управления во вкладке «Вставка» выбрать пункт «Сводная таблица». В диалоговом окне указываем, где размещены данные для анализа (по умолчанию будет указан диапазон таблицы, где стоит курсор) и куда нужно поместить результат. Следующим шагом в «Конструкторе сводной таблицы» вы можете из полей и записей вашей БД создать отчёт в таком виде, который вам нужен. При внесении изменений в базу данных, автоматических изменений в сводной таблице не происходит. Все изменения стают, доступны только при нажатии кнопки «Обновить данные», через контекстное меню или вкладка «Данные» и кнопка «Обновить всё».

Читайте также:  Восстановление поврежденных файлов Microsoft Excel

Группируем элементы таблицы

Иногда для удобства навигации по вашей базе данных или для сворачивания некоторых элементов таблицы можно использовать инструменты «Группировка» и «Разгруппировать». Эта возможность позволит свернуть данные, которые в данный момент вам не интересны и отображать их не стоит. Очень полезный инструмент, когда возникает необходимость создать диапазон печати для некоторых данных.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное о работе со списками данных в Excel вам пригодилось и было понятным. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

MS Excel. Фильтрация (выборка) данных из списка

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

Фильтрация данных может выполняться двумя способами: с помощью автофильтра или расширенного фильтра.

Для использования автофильтра нужно:

o установить курсор внутри таблицы;

o выбрать команду Данные – Фильтр – Автофильтр;

o раскрыть список столбца, по которому будет производиться выборка;

o выбрать значение или условие и задать критерий выборки в диалоговом окне Пользовательский автофильтр.

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

Для отмены режима фильтрации нужно установить курсор внутри таблицы и повторно выбрать команду меню Данные – Фильтр – Автофильтр (снять флажок).

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

Задание.

Создайте таблицу в соответствие с образцом, приведенным на рисунке. Сохраните ее под именем Sort.xls.

Технология выполнения задания:

1. Откройте документ Sort.xls

2. Установите курсор-рамку внутри таблицы данных.

3. Выполните команду меню Данные – Сортировка.

4. Выберите первый ключ сортировки “По возрастанию” (Все отделы в таблице расположатся по алфавиту).

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

5. Установите курсор-рамку внутри таблицы данных.

6. Выполните команду меню Данные – Фильтр

7. Снимите выделение в таблицы.

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

9. Щелкните по кнопке со стрелкой, появившейся в столбце Количество остатка. Раскроется список, по которому будет производиться выборка. Выберите строку Условие. Задайте условие: > 0. Нажмите ОК. Данные в таблице будут отфильтрованы.

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

11. Фильтр можно усилить. Если дополнительно выбрать какой-нибудь отдел, то можно получить список неподанных товаров по отделу.

12. Для того, чтобы снова увидеть перечень всех непроданных товаров по всем отделам, нужно в списке “Отдел” выбрать критерий “Все”.

13. Чтобы не запутаться в своих отчетах, вставьте дату, которая будет автоматически меняться в соответствии с системным временем компьютераФормулы – Вставить функцию – Дата и время – Сегодня.

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

Задание

· откройте файл автомобили.xls с электронной таблицей;

· вычислите длину пробега каждого автомобиля;

· отсортируйте данную таблицу в порядке уменьшения по значениям в столбце «Вид автомобиля»;

· получите общее время пробега, общую длину пробега и среднюю скорость пробега для каждого вида автомобиля. Построить диаграмму, отражающую длину пробега каждого автомобиля.

Контрольные вопросы

1. Что представляет из себя MS Excel?

2. Какие бывают адреса ячеек? Перечислите, охарактеризуйте.

3. Что такое макрос? Его назначение.

4. Какие возможности предоставляет окно Формат ячеек?

5. Опишите ваши действия при создании диаграммы.

Выбор данных в сводной таблице

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

Для выделения ячеек используйте обычный указатель мыши Microsoft Office Excel .

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

Примечание: Если фигура изменить указатель не отображается, на вкладке Параметры в группе действия нажмите кнопку выделитьи убедитесь, что выбран параметр разрешить выделение .

Выделение отдельных ячеек

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

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

Щелкните сводную таблицу.

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

Наведите курсор на верхний край поля, пока указатель не примет вид стрелки вниз, , а затем щелкните.

Например, чтобы выбрать элемент “Восток и Запад”, наведите указатель мыши на верхнюю часть областии щелкните один раз.

Выберите нужные элементы.

На вкладке Параметры в группе действия нажмите кнопку выделитьи выберите нужный вариант.

Метки и значения для выбора обоих параметров.

Значения выделять только значения для элементов.

Метки для выбора только подписей элементов.

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

Например, чтобы выбрать обе строки для гольфа, щелкните левый край элемента гольф.

Наведите курсор на верхний край элемента поля столбца, пока указатель не примет вид стрелки вниз либо наведите указатель мыши на левый край элемента поля строки, пока он не примет вид стрелки вправо .

Щелкните один раз, а затем еще раз щелкните Дополнительные часы, ожидая между нажатиями клавиш, чтобы не использовать двойным щелчком.

Например, чтобы выделить только строку для Qtr1 гольфа, щелкните левый край элемента гольф, а затем щелкните еще раз.

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

Читайте также:  Проблема замены чисел значками решетки в Microsoft Excel

Наведите курсор на верхний край элемента поля столбца, пока указатель не примет вид стрелки вниз либо наведите указатель мыши на левый край элемента поля строки, пока он не примет вид стрелки вправо .

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

Удерживая нажатой клавишу SHIFT, щелкните или, удерживая нажатой клавишу CTRL, выберите дополнительные элементы в том же поле.

Чтобы отменить выделение элемента, щелкните его, удерживая нажатой клавишу CTRL.

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

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Умные Таблицы Excel – секреты эффективной работы

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

Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы».

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Есть горячая клавиша Ctrl+T.

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

Как правило, ничего не меняем. После нажатия Ок исходный диапазон превратится в Таблицу Excel.

Перед тем, как перейти к свойствам Таблицы, посмотрим вначале, как ее видит сам Excel. Многое сразу прояснится.

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

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

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

то она автоматически переделается в

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи.

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

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

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.


Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

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

Помимо указанных свойств есть возможность сделать дополнительные настройки.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

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

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.