Урок 5. Создание таблиц

На уроке 1, когда мы только начали знакомство с Excel, мы разобрались, что собой представляет таблица в экселе. Мы узнали, что таблица в экселе состоит из строк и столбцов, которые формируют ячейки для ввода данных. И помним, что каждая строка в экселе имеет свое числовое обозначение (1, 2, 3 и далее), а каждый столбец в экселе имеет буквенное латинское обозначение (А, В, С и далее).

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

Все это поможет нам понять, как упростить управление таблицами эксель и работу с данными.

Содержание:

• Как создать таблицу в Excel?
• Сортировка и фильтрация данных в таблице
• Работа с большими массивами данных
• Проверочный тест

При подготовке этого урока мы опирались на материал «Создание и форматирование таблиц» [Microsoft, 2023] и материал «Фильтрация данных в диапазоне или таблице» [Microsoft, 2023].

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

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

Подготовка данных

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

Выбор диапазона данных

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

Создание таблицы

На вкладке «Вставка» в ленте эксель выберите «Таблицы». Эксель автоматически предложит диапазон для создания таблицы. Убедитесь, что диапазон выбран корректно. Установите флажок «Таблица с заголовками», если ваш диапазон включает заголовки (шапку таблицы). Нажмите OK или Enter для создания таблицы:

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

Фильтрация и сортировка данных

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

Добавление и удаление строк и столбцов

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

Чтобы удалить строку или столбец, щелкните правой кнопкой мыши по заголовку строки или столбца и выберите «Удалить».

Название таблицы

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

Удалите название таблицы по умолчанию и введите свое название таблицы эксель.

Итоговая строка

Вы можете добавить итоговую строку, которая автоматически подсчитает суммы, средние значения и другие данные. Для этого на вкладке «Конструктор» установите флажок «Итоговая строка» или «Строка итогов» в зависимости от версии эксель:

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

Подытожим алгоритм пошагового создания таблицы:

• Введите данные в выбранный диапазон ячеек.
• Перейдите на вкладку «Вставка» и нажмите «Таблица».
• Убедитесь, что диапазон выбран правильно, и нажмите OK либо Enter.
• Перейдите на вкладку «Конструктор» и настройте стиль таблицы, выбрав подходящий вариант.
• Присвойте название таблицы в поле «Имя таблицы».
• При необходимости сделайте «Строку итогов».

Это базовые знания для создания самых простых таблиц, которые помогают организовать данные в эксель и ускорить процессы работы с большими объемами информации, предоставляя мощные инструменты для анализа и структурирования данных. Кроме того, для этого следует освоить сортировку и фильтрацию данных в таблице.

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

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

Сортировка данных в эксель

Для начала рассмотрим сортировку данных.

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

Как в экселе сделать сортировку данных:

• Выделите диапазон данных или щелкните любую ячейку в таблице.
• Перейдите на вкладку «Данные» в верхней ленте эксель.
• В группе «Сортировка и фильтр» выберите «Сортировка по возрастанию» (A–Z или А-Я) либо «Сортировка по убыванию» (Z–A или Я-А).

«Сортировка по возрастанию» сортирует числа по возрастанию или текст по алфавиту, а «Сортировка по убыванию» сортирует числа по убыванию или текст в обратном алфавитном порядке. Если хотите выполнить сортировку по нескольким критериям, нажмите кнопку «Сортировка». Откроется диалоговое окно «Сортировка», где можно выбрать несколько уровней сортировки:

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

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

Фильтрация данных в эксель

Теперь рассмотрим фильтрацию данных.

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

Как в экселе сделать фильтрацию данных:

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

Вы можете выбрать разные типы фильтров. Текстовые фильтры позволяют фильтровать текстовые данные по таким критериям, как «содержит», «начинается с», «не содержит» и прочим. Числовые фильтры позволяют фильтровать числовые данные по критериям «больше», «меньше», «между» и другим. Кроме того, можно выбрать «Настраиваемый фильтр» и настроить «Пользовательский автофильтр».

После того, как вы выбрали критерии фильтрации, эксель отобразит только те строки, которые соответствуют фильтру. Другие строки будут скрыты, но не удалены.

Так, если у вас есть таблица с данными о заказах, вы можете отфильтровать все заказы, сделанные после определенной даты, все заказы на сумму больше 1000 рублей, все заказы, сделанные конкретным покупателем, и т.д.

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

Комбинация сортировки и фильтрации данных в эксель

Комбинировать сортировку и фильтрацию можно для более детализированного анализа данных, когда нужно выяснить какие-то незаметные на первый взгляд нюансы. Например, понять, когда происходит всплеск продаж и какие группы товаров наиболее востребованы на пике спроса. Для этого нужна комбинация сортировки и фильтрации:

Как можно комбинировать сортировку и фильтрацию:

• Сначала отсортировать данные по дате.
• Затем отфильтровать заказы по сумме больше 1000.

И теперь давайте кратко повторим пошаговую настройку фильтров и сортировки.

Применение фильтра:

• Выберите диапазон данных и на вкладке «Данные» нажмите «Фильтр».
• Щелкните по стрелке фильтра в заголовке столбца и выберите критерии фильтрации.

Применение сортировки:

• Щелкните по заголовку столбца, который хотите отсортировать.
• На вкладке «Данные» используйте кнопки A-Z или Z-A для быстрого выполнения сортировки.

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

Работа с большими массивами данных

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

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

Фиксация строк и столбцов (Закрепить области)

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

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

Как зафиксировать строки/столбцы:

• Выберите строку под теми строками, которые хотите зафиксировать (или выберите столбец справа от тех, которые нужно закрепить).
• Перейдите на вкладку «Вид».
• Нажмите «Закрепить области» и выберите нужный вариант.

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

После этого вам будет доступен выбор «Снять закрепление областей», при помощи которого вы можете «вернуть, как было».

Группировка данных

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

Как сгруппировать данные:

• Выделите диапазон строк или столбцов, которые хотите сгруппировать.
• Перейдите на вкладку «Данные».
• Найдите группу инструментов «Структура».
• Нажмите «Группировать».

Диалоговое окно предложит вам сгруппировать столбцы в экселе или строки в экселе, что можно сделать с помощь кнопок «+» и «-»:

В нашем простом примере мы увидим, как исчез столбец А, и мы можем оценить общую успеваемость в группе, не отвлекаясь на фамилии и эмоции, связанные с конкретными людьми. Как мы помним из урока 1, максимальное количество столбцов составляет 16 384, и при большом количестве столбцов такая опция будет весьма кстати.

Работа с макросами

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

Как записать макрос:

• Перейдите на вкладку «Вид».
• Нажмите стрелочку в группе «Макросы».
• Выберите «Записать макрос».
• Выполните необходимые действия.
• Нажмите «Остановить запись», когда закончите.

Вот так:

Обратите внимание, что опция «Остановить запись» становится доступной только после того, как вы запустите команду «Записать макрос».

Функция SUMIF/SUMIFS

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

Функция SUMIF выполняет суммирование значений, если они соответствуют одному условию. Посмотрим, как выглядит функция SUMIF:

=SUMIF(диапазон_условий, условие, [диапазон_суммирования])

Аргументы функции:

• диапазон_условий – диапазон, который будет проверяться на соответствие условию;
• условие – критерий, по которому производится отбор (например, число, текст или выражение);
• диапазон_суммирования (необязательный аргумент) – диапазон, значения из которого будут суммироваться. Если не указан, суммируются значения из диапазона_условий.

В свою очередь, функция SUMIFS выполняет суммирование значений, если они соответствуют нескольким условиям. Это расширенная версия SUMIF, которая позволяет задать несколько критериев для суммирования. Посмотрим, как выглядит функция SUMIFS:

=SUMIFS(диапазон_суммирования, диапазон_условий1, условие1, [диапазон_условий2, условие2], ...)

Аргументы функции:

• диапазон_суммирования – диапазон, значения из которого будут суммироваться;
• диапазон_условий1 – первый диапазон, который проверяется на соответствие условию1;
• условие1 – критерий для первого диапазона;
• диапазон_условий2 (необязательно) – второй диапазон, который проверяется на соответствие условию2, и т.д.

Зачем нужны функции SUMIF и SUMIFS? Эти функции помогают анализировать и обрабатывать данные, когда необходимо суммировать только определенные данные, соответствующие условиям.

Использование функций SUMIF/SUMIFS:

• Финансовый анализ – подсчет прибыли, расходов по конкретным категориям.
• Работы с базами данных – суммирование данных по определенным критериям (например, продажи за определенный период или для конкретного продукта).
• Автоматизации отчетности – создание динамических отчетов с использованием условий.

Здесь B2:B10 – это диапазон, где находятся суммы заказов, а ">1000" – это условие, которое означает, что нужно суммировать только те заказы, которые больше 1000.

Функции SUMIF и SUMIFS являются мощными инструментами для работы с данными в Excel, позволяющими легко суммировать значения на основе одного или нескольких условий. Эти функции особенно полезны при анализе больших объемов данных и создании отчетов с динамическими условиями.

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

Их мы будем рассматривать на уроке 8 в рамках темы «Простые инструменты анализа данных».  А сейчас вас ждет проверочный тест, после чего мы перейдем к следующей теме.

Проверьте свои знания

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

Далее нас ждет работа с диаграммами и графиками.

Ольга Обломова