Программа Microsoft Excel удобна для составления таблиц и расчетов. Рабочая область — это набор ячеек, которые можно заполнить данными. В последующем форматировать, использовать для создания графиков, таблиц, сводных отчетов.
Работа с таблицами в Excel для начинающих на первый взгляд может показаться сложной. Он существенно отличается от принципов построения таблиц в Word. Но начнем понемногу: создание и форматирование таблицы. А к концу статьи вы уже поймете, что лучшего инструмента для создания таблиц, чем Excel, не придумаешь.
КАК СОЗДАТЬ ТАБЛИЦУ В EXCEL ДЛЯ ЧАЙНИКОВ. Пошагово
Работа с таблицами в Excel для начинающих не спешит. Существует множество способов создания таблицы, и для конкретных целей каждый метод имеет свои преимущества. Поэтому сначала визуально оцените ситуацию.
Посмотрите внимательно на рабочий лист электронной таблицы:
Это набор ячеек в столбцах и строках. В основном стол. Столбцы отмечены латинскими буквами. Ряды — это числа. Если мы распечатаем этот лист, мы получим пустую страницу. Без ограничений.
Во-первых, давайте научимся работать с ячейками, строками и столбцами.
Видео на тему: Excel для начинающих
Как выделить столбец и строку
Чтобы выделить столбец целиком, нажмите на его название (латинскую букву) левой кнопкой мыши.
Для выбора строки — по названию линии (по номеру).
Чтобы выбрать несколько столбцов или строк, щелкните левой кнопкой мыши по заголовку, удерживайте и перетащите.
Чтобы выбрать столбец с помощью горячих клавиш, поместите курсор в любую ячейку нужного столбца; нажмите Ctrl + пробел. Для выделения строки — Shift+Пробел.
Видео на тему: ТОП 15 лучших трюков Excel
Как изменить границы ячеек
Если при заполнении таблицы информация не помещается, необходимо изменить границы ячеек:
- Переместите вручную, зацепив границу ячейки левой кнопкой мыши.
- При вводе длинного слова в ячейку дважды щелкните границу столбца/строки. Программа автоматически расширит лимиты.
- Если вы хотите сохранить ширину столбца, но увеличить высоту строки, используйте кнопку «Перенос текста» на панели инструментов.
Чтобы изменить ширину столбцов и высоту строк сразу в определенном диапазоне, выберите область, увеличьте 1 столбец/строку (переместите вручную) — размер всех выбранных столбцов и строк изменится автоматически.
Примечание. Чтобы вернуться к прежнему размеру, вы можете нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но это работает, когда вы делаете это сразу. Позже не поможет.
Чтобы вернуть линиям исходные края, откройте меню инструментов: «Главная» — «Формат» и выберите «Автоматическая настройка высоты линии»
Для столбцов этот метод не актуален. Нажмите «Формат» — «Ширина по умолчанию». Давайте запомним это число. Выделяем любую ячейку в столбце, границы которой нужно «вернуть». Опять же, «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило, это 8,43 — количество символов в шрифте Calibri размером 11 пунктов) — нормально.
Как вставить столбец или строку
Выберите столбец/строку справа/ниже, где вы хотите вставить новый диапазон. То есть столбец появится слева от выбранной ячейки. И очередь встала.
Щелкните правой кнопкой мыши — выберите «Вставить» из выпадающего меню (или нажмите комбинацию горячих клавиш CTRL + SHIFT+»=»).
Выберите столбец и нажмите ОК.
Наконечник. Чтобы быстро вставить столбец, выберите столбец в нужном месте и нажмите CTRL+SHIFT+»=».
Все эти навыки пригодятся при создании электронной таблицы в Excel. Придется расширять границы, добавляя в процессе строки/столбцы.
Пошаговое создание таблицы с формулами
- Заполняем шапку вручную — названия столбцов. Вводим данные: заполняем строки. Полученные знания сразу применяем на практике: расширяем границы столбцов, «подбираем» высоту строк.
- Чтобы заполнить столбец «Стоимость», поместите курсор в первую ячейку. Пишем «=». Итак, говорим программе Excel: будет формула. Выберите ячейку B2 (с первой ценой). Введите знак умножения (*). Выберите ячейку C2 (с суммой). Нажмите Ввод.
- При наведении курсора на ячейку с формулой в правом нижнем углу образуется крестик. Наведите курсор на маркер автозаполнения. Цепляемся за него левой кнопкой мыши и доводим до конца столбца. Формула будет скопирована во все ячейки.
- Обозначим границы нашей таблицы. Выберите диапазон с данными. Нажимаем кнопку: «Главная» — «Грая» (на главной странице в меню «Шрифт»). И выберите «Все ребра».
Теперь при печати будут видны границы столбцов и строк.
Используя меню «Шрифт», вы можете форматировать данные электронной таблицы Excel так же, как в Word.
Измените, например, размер шрифта, сделайте заголовок «жирным». Вы можете установить текст по центру, расставить переносы и т.д.
КАК СОЗДАТЬ ТАБЛИЦУ В EXCEL: ПОШАГОВАЯ ИНСТРУКЦИЯ
Самый простой способ создания таблиц уже известен. Но в Excel есть более удобный вариант (с точки зрения последующего форматирования, работы с данными).
Сделаем «умную» таблицу (динамическую:
- Перейдите на вкладку «Вставка» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
- В открывшемся диалоговом окне укажите диапазон данных. Отметим, что таблица с подписями. Нажмите «ОК». Ничего страшного, если вы не угадаете диапазон сразу. Мобильный «Умный стол», динамичный.
Примечание. Можно пойти и другим путем: сначала выделить диапазон ячеек, а затем нажать кнопку «Таблица».
Теперь введите необходимые данные в готовую рамку. Если вам нужен дополнительный столбец, поставьте курсор в ячейку, предназначенную для заголовка. Введите имя и нажмите ENTER. Диапазон расширится автоматически.
Если вам нужно увеличить количество строк, зацепите правый нижний угол маркера автозаполнения и перетащите его вниз.
КАК РАБОТАТЬ С ТАБЛИЦЕЙ В EXCEL
С выходом новых версий программы работа в Excel с таблицами стала более интересной и динамичной. При формировании смарт-таблицы на листе доступен инструмент «Работа с таблицами» — «Конструктор».
Здесь мы можем назвать таблицу, изменить размер.
Доступны различные стили, возможность преобразования таблицы в обычный диапазон или сводный отчет.
Возможности динамических электронных таблиц в MS Excel огромны. Начнем с базовых навыков ввода данных и автозаполнения:
- Выберите ячейку, щелкнув по ней левой кнопкой мыши. Введите текстовое/числовое значение. Нажмите Ввод. Если вам нужно изменить значение, поставьте курсор обратно в ту же ячейку и введите новые данные.
- Если вы введете повторяющиеся значения, Excel их распознает. Просто введите несколько символов на клавиатуре и нажмите Enter.
- Чтобы применить формулу ко всему столбцу в смарт-таблице, просто введите ее в первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
- Для подсчета итогов выделите столбец со значениями плюс пустую ячейку для будущего итога и нажмите кнопку «Сумма» (группа инструментов «Правка» на вкладке «Главная» или нажмите комбинацию горячих клавиш ALT+»=»).
Нажав на стрелку справа от каждого подзаголовка заголовка, вы получите доступ к дополнительным инструментам для работы с табличными данными.
Иногда пользователю приходится работать с огромными таблицами. Чтобы увидеть результаты, необходимо прокрутить более тысячи строк. Удаление строк невозможно (данные понадобятся позже). Но ты можешь спрятаться.
Для этого используйте числовые фильтры (изображение выше). Снимите галочки рядом со значениями, которые должны быть скрыты.
Уважаемый читатель! Добавьте этот сайт в закладки своего браузера и поделитесь ссылкой на этот сайт с друзьями! Мы стараемся показать здесь всевозможные хитрости и секреты. Подойдет точно.
Вот еще статьи о том, как работать в Excel:
Как закрепить строки и столбцы в Excel при прокрутке.
Как создаются формулы и таблицы в Excel. Шаг за шагом.
Как посчитать сумму столбца в Excel.
Основное направление программы Microsoft Excel – работа с таблицами. Пользователю предлагают расширенный набор функциональности для удобного заполнения полей с данными, формирования сводных и различных отчетов. Можно ограничиться и обычной таблицей, вписав туда необходимые значения.
Всего существует три доступных метода построения данных объектов, о чем я и расскажу далее.
Способ 1: Использование встроенных шаблонов таблиц
Для начинающих и тех, кто проверяет возможности программы, разработчики подготовили несколько полностью рабочих таблиц на разные темы. Ничто не помешает открыть их для редактирования и изменить под себя. Взаимодействие с присутствующими шаблонами осуществляется так:
-
В приветственном окне программы перейдите на вкладку «Создать».
-
Отыщите среди всех предложенных вариантов подходящую для вас таблицу, например, домашний бюджет на месяц или отчет компании. Дважды щелкните по плитке для открытия шаблона.
-
Проект создается сразу с несколькими листами, где обычно присутствуют таблицы и сводка с отдельными данными. Их названия и связи автоматически настроены, поэтому ничего лишнего изменять не придется.
-
В «Сводке» показаны графики или другие таблицы, отражающие общую суть содержимого на других листах. Пока этот лист вообще можно не изменять, поскольку значения подстроятся автоматически после их изменения в других таблицах.
-
Перейдите к следующему листу с данными и ознакомьтесь с присутствующими строками. Смело изменяйте их названия и значения под себя, отслеживая, как это сказывается на листе «Сводка».
Преимущество этого метода работы с таблицами – получение полностью готового проекта, в который можно внести часть изменений и получить приемлемый результат. Недостаток – для более гибкого редактирования придется удалить часть содержимого. В сложных связях легко запутаться, что только замедлит процесс заполнения данных.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Способ 2: Ручное создание таблицы
Ручное создание таблицы в Excel – сложный, но гибкий метод работы с полями и значениями. Далее идет общая пошаговая инструкция по этой теме, частично раскрывающая весь процесс и объясняющая самые важные аспекты взаимодействия с элементами программы. При более профессиональном подходе к созданию таблицы заранее учитывайте ее формат, наличие нескольких листов и правила оформления различных чисел, если речь идет о регламенте компании, для которой и создается проект.
-
Создайте пустой проект и введите названия столбцов, где далее будут размещены значения.
-
Заполните данные каждого столбца в соответствии с имеющейся на руках информацией.
-
Для удобства добавьте заливку к ячейкам разного типа, первоочередно выделив их все при помощи зажатой левой кнопки мыши.
-
Таблица смотрится плохо без границ и кажется одним целым, поэтому вызовите список с доступными вариантами оформления.
-
Найдите там подходящий тип границы. Чаще всего используется вариант «Все границы».
-
На следующем скриншоте вы видите подготовленную простую таблицу с банальными значениями. Примерно так же создаются и другие объекты, однако нужно учитывать подачу и другие детали, добавляя фоновые изображения, растягивая содержимое, работая со шрифтами и их размером.
-
В завершение рассмотрю применение формул в таблице. Для этого создам еще один столбец с названием «Итоги», куда должна выводиться сумма продаж всех наименований товара.
-
В таблице есть цена и количество, а значит, эти значения нужно перемножить, чтобы получить итог. Данная формула записывается как =B2*C2 (названия ячеек меняются в соответствии с требованиями).
-
Используйте растягивание, зажав правый нижний угол ячейки с формулой и растянув ее на всю длину. Значения автоматически подставляются на нужные, и вам не придется заполнять каждое поле вручную.
-
В итоге получилась простая таблица с автоматической формулой для расчетов, значения в ячейках которой будут меняться в зависимости от установленных в клетках чисел.
После добавления знака = при написании формул можно увидеть доступные варианты. Ознакомьтесь с описанием от разработчиков, если пока не знаете, как производить похожие расчеты в Microsoft Excel.
Способ 3: Вставка таблицы
Третий способ создания таблицы в Excel – использование функции вставки. Для этого придется изначально заполнить ячейки с данными примерно по такому же принципу, как это было показано выше. Обычно этот метод используется для добавления сводки, но может пригодиться и в других ситуациях.
-
Перейдите на вкладку «Вставка» и разверните меню «Таблицы».
-
Выберите один из трех доступных вариантов, подходящих для вашего проекта.
-
Я выбрал «Рекомендуемые сводные таблицы» и в качестве диапазона указал созданную ранее таблицу.
-
Ознакомьтесь с предупреждениями от разработчиков, если такие появились на экране.
-
В итоге автоматически создается новый лист со сводной таблицей, которая подхватила значения в указанных данных и вывела общие итоги. Ничего не помешает редактировать эту таблицу точно так же, как это было показано ранее.
В этой статье вы узнали о том, как сделать таблицу в Excel разными способами. Конечно, рассказать абсолютно обо всех нюансах и фишках не получится, поэтому я ограничился только общей информацией, полезной для тех, кто только осваивает это программное обеспечение.
В MS Excel есть много потрясающих инструментов, о которых большинство пользователей не подозревают или сильно недооценивает. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы.
Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы».
В наличии имеется обычный диапазон данных о продажах.
Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица
Есть горячая клавиша Ctrl+T.
Появится маленькое диалоговое окно, где можно поправить диапазон и указать, что в первой строке находятся заголовки столбцов.
Как правило, ничего не меняем. После нажатия Ок исходный диапазон превратится в Таблицу Excel.
Перед тем, как перейти к свойствам Таблицы, посмотрим вначале, как ее видит сам Excel. Многое сразу прояснится.
Структура и ссылки на Таблицу Excel
Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.
Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.
А также при наборе формулы вручную.
Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.
=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»
Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).
Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.
Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»
=СУММ(D2:D8)
то она автоматически переделается в
=Отчет[Продажи]
Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.
Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи.
А теперь о том, как Таблицы облегчают жизнь и работу.
Свойства Таблиц Excel
1. Каждая Таблица имеет заголовки, которые обычно берутся из первой строки исходного диапазона.
2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.
Очень удобно, не нужно специально закреплять области.
3. В таблицу по умолчанию добавляется автофильтр, который можно отключить в настройках. Об этом чуть ниже.
4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.
Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.
5. Новые столбцы также автоматически включатся в Таблицу.
6. При внесении формулы в одну ячейку, она сразу копируется на весь столбец. Не нужно вручную протягивать.
Помимо указанных свойств есть возможность сделать дополнительные настройки.
Настройки Таблицы
В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.
С помощью галочек в группе Параметры стилей таблиц
можно внести следующие изменения.
— Удалить или добавить строку заголовков
— Добавить или удалить строку с итогами
— Сделать формат строк чередующимися
— Выделить жирным первый столбец
— Выделить жирным последний столбец
— Сделать чередующуюся заливку строк
— Убрать автофильтр, установленный по умолчанию
В видеоуроке ниже показано, как это работает в действии.
В группе Стили таблиц можно выбрать другой формат. По умолчанию он такой как на картинках выше, но это легко изменить, если надо.
В группе Инструменты можно создать сводную таблицу, удалить дубликаты, а также преобразовать в обычный диапазон.
Однако самое интересное – это создание срезов.
Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,
и срез готов. В нем показаны все уникальные значения выбранного столбца.
Для фильтрации Таблицы следует выбрать интересующую категорию.
Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.
Попробуйте сами, как здорово фильтровать срезами (кликается мышью).
Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.
Ограничения Таблиц Excel
Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.
1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).
2. Текущую книгу нельзя выложить для совместного использования.
3. Невозможно вставить промежуточные итоги.
4. Не работают формулы массивов.
5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.
Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.
Множество других секретов Excel вы найдете в онлайн курсе.
Поделиться в социальных сетях:
Урок 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 мы подробно рассмотрели функцию автосуммирования, поэтому сейчас вы полностью готовы к тому, чтобы сделать еще один шаг и усложнить задачу.
Бывает так, что в огромном массиве данных нужно суммировать не все данные, а лишь часть из них, соответствующих определенному условию. Например, узнать, на какую сумму были сделаны продажи товаров дороже 1000 рублей.
Функция SUMIF выполняет суммирование значений, если они соответствуют одному условию. Посмотрим, как выглядит функция SUMIF:
=SUMIF(диапазон_условий, условие, [диапазон_суммирования])
Аргументы функции:
- диапазон_условий – диапазон, который будет проверяться на соответствие условию;
- условие – критерий, по которому производится отбор (например, число, текст или выражение);
- диапазон_суммирования (необязательный аргумент) – диапазон, значения из которого будут суммироваться. Если не указан, суммируются значения из диапазона_условий.
В свою очередь, функция SUMIFS выполняет суммирование значений, если они соответствуют нескольким условиям. Это расширенная версия SUMIF, которая позволяет задать несколько критериев для суммирования. Посмотрим, как выглядит функция SUMIFS:
=SUMIFS(диапазон_суммирования, диапазон_условий1, условие1, [диапазон_условий2, условие2], …)
Аргументы функции:
- диапазон_суммирования – диапазон, значения из которого будут суммироваться;
- диапазон_условий1 – первый диапазон, который проверяется на соответствие условию1;
- условие1 – критерий для первого диапазона;
- диапазон_условий2 (необязательно) – второй диапазон, который проверяется на соответствие условию2, и т.д.
Зачем нужны функции SUMIF и SUMIFS? Эти функции помогают анализировать и обрабатывать данные, когда необходимо суммировать только определенные данные, соответствующие условиям.
Использование функций SUMIF/SUMIFS:
- Финансовый анализ – подсчет прибыли, расходов по конкретным категориям.
- Работы с базами данных – суммирование данных по определенным критериям (например, продажи за определенный период или для конкретного продукта).
- Автоматизации отчетности – создание динамических отчетов с использованием условий.
Пример использования функции SUMIF:
Допустим, нужно посчитать сумму продаж, если сумма заказа больше 1000. Если в столбце А указаны клиенты, а в столбце В указаны суммы продаж клиентам, вводим следующую формулу:
=SUMIF(B2:B10, «>1000»)
Здесь B2:B10 – это диапазон, где находятся суммы заказов, а «>1000» – это условие, которое означает, что нужно суммировать только те заказы, которые больше 1000.
Пример использования функции SUMIFS:
Допустим, нужно подсчитать сумму продаж для заказов больше 1000 и для клиентов из конкретного города.
Предположим, что, кроме суммы заказов в столбце В, у вас есть список городов в столбце С, и вам нужно найти сумму всех заказов больше 1000 для клиентов из Москвы. Вводим следующую формулу:
=SUMIFS(B2:B10, B2:B10, «>1000», C2:C10, «Москва»)
Функции SUMIF и SUMIFS являются мощными инструментами для работы с данными в Excel, позволяющими легко суммировать значения на основе одного или нескольких условий. Эти функции особенно полезны при анализе больших объемов данных и создании отчетов с динамическими условиями.
Это лишь некоторые основные приемы работы с большими массивами данных, которые позволяют упростить управление таблицами эксель. Забегая вперед, скажем, что для управления таблицами и анализа больших объемов данных еще могут быть полезны такие инструменты, как условное форматирование и сводные таблицы.
Их мы будем рассматривать на уроке 8 в рамках темы «Простые инструменты анализа данных». А сейчас вас ждет проверочный тест, после чего мы перейдем к следующей теме.
Проверьте свои знания
Если вы хотите проверить свои знания по теме данного урока, можете пройти небольшой тест, состоящий из нескольких вопросов. В каждом вопросе правильным может быть только один вариант. После выбора вами одного из вариантов система автоматически переходит к следующему вопросу. На получаемые вами баллы влияет правильность ваших ответов и затраченное на прохождение время. Обратите внимание, что вопросы каждый раз разные, а варианты перемешиваются.
Далее нас ждет работа с диаграммами и графиками.
← 4 Функции и формулы6 Диаграммы и графики →
Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизиро…
Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизировать этот процесс, ответит наша статья.
Советы по структурированию информации
Перед тем, как создать таблицу в Excel, предлагаем изучить несколько общих правил:
- Сведения организуются по колонкам и рядам. Каждая строка отводится под одну запись.
- Первый ряд отводится под так называемую «шапку», где прописываются заголовки столбцов.
- Нужно придерживаться правила: один столбец – один формат данных (числовой, денежный, текстовый и т.д.).
- В таблице должен содержаться идентификатор записи, т.е. пользователь отводит один столбец под нумерацию строк.
- Структурированные записи не должны содержать пустых колонок и рядов. Допускаются нулевые значения.
Как создать таблицу в Excel вручную
Для организации рабочего процесса пользователь должен знать, как создать таблицу в Экселе. Существуют 2 метода: ручной и автоматический. Пошаговая инструкция, как нарисовать таблицу в Excel вручную:
- Открыть книгу и активировать нужный лист.
- Выделить необходимые ячейки.
- На панели инструментов найти пиктограмму «Границы» и пункт «Все границы».
- Указать в таблице имеющиеся сведения.
II способ заключается в ручном рисовании сетки таблицы. В этом случае:
- Выбрать инструмент «Сетка по границе рисунка» при нажатии на пиктограмму «Границы».
- При зажатой левой кнопке мыши (ЛКМ) перетащить указатель по обозначенным линиям, в результате чего появляется сетка. Таблица создается, пока нажата ЛКМ.
Как создать таблицу в Excel автоматически
Опытные пользователи рекомендуют прибегнуть к изучению вопроса, как создать таблицу в Excel
автоматически. С использованием встроенного инструментария процесс создания табличной формы происходит в разы быстрее.
Область таблицы
Перед тем, как составить таблицу в Excel, пользователю нужно определить, какой интервал ячеек ему понадобится:
- Выделить требуемый диапазон.
- В MS Excel 2013-2019 на вкладке «Главная» кликнуть на пиктограмму «Форматировать как таблицу».
- При раскрытии выпадающего меню выбрать понравившийся стиль.
Кнопка «Таблица» на панели быстрого доступа
На панели инструментов находится пиктограмма для создания табличного объекта. Чтобы воспользоваться функционалом табличного процессора, пользователь использует следующий алгоритм:
- Активировать интервал ячеек, необходимых для работы.
- Перейти в меню «Вставка».
- Найти пиктограмму «Таблицы»:
- В MS Excel 2007 кликнуть на пиктограмму. В появившемся диалоговом окне отметить или убрать переключатель пункта «Таблица с заголовками». Нажать ОК.
- В MS Excel 2016 нажать пиктограмму и выбрать пункт «Таблица». Указать диапазон ячеек через выделение мышкой или ручное прописывание адресов ячеек. Нажать ОК.
Примечание: для создания объекта используют сочетание клавиш CTRL + T.
4. Для изменения названия столбца перейти на строку формул или дважды кликнуть на объекте с заголовком.
Диапазон ячеек
Работа с числовой информацией подразумевает применение функций, в которых указывается интервал (диапазон ячеек). Под диапазоном справочная литература определяет множество клеток электронной таблицы, в совокупности образующих единый прямоугольник (А1:С9).
Активированная вкладка «Конструктор» раскрывает инструментарий, облегчающий процесс редактирования объекта. Для автоматизации работы пользователь устанавливает переключатели на инструментах «Строка заголовка» и «Строка итогов». Последняя опция позволяет провести расчеты по выбранной формуле. Для этого пользователь раскрывает список посредством нажатия на стрелку в итоговой ячейке и выбирает функцию.
Заполнение данными
Работа со структурированной информацией возможна, если ячейки заполнены текстовой, численной и иной информацией.
- Для заполнения необходимо активировать ячейку и начать вписывать информацию.
- Для редактирования ячейки дважды кликнуть на ней или активировать редактируемую ячейку и нажать F2.
- При раскрытии стрелок в строке заголовка структурированной информации MS Excel можно отфильтровать имеющуюся информацию.
- При выборе стиля форматирования объекта MS Excel автоматически выбрать опцию черезстрочного выделения.
- Вкладка «Конструктор» (блок «Свойства») позволяет изменить имя таблицы.
- Для увеличения диапазона рядов и колонок с последующим наполнением информацией: активировать кнопку «Изменить размер таблицы» на вкладке «Конструктор», новые ячейки автоматически приобретают заданный формат объекта, или выделить последнюю ячейку таблицы со значением перед итоговой строкой и протягивает ее вниз. Итоговая строка останется неизменной. Расчет проводится по мере заполнения объекта.
- В заголовках должны отсутствовать числовые форматы, поскольку при создании таблицы они преобразуются в текст. Если формула содержит в качестве аргумента ссылку на ячейку заголовка, где предполагалось число, то функция может не сработать.
Сводная таблица
Сводка используется для обобщения информации и проведения анализа, не вызывает трудностей при создании и оформлении. Для создания сводной таблицы:
- Структурировать объект и указать сведения.
- Перейти в меню «Вставка» и выбрать пиктограмму: в MS Excel 2007 – «Сводная таблица»; в MS Excel 2013-2019 – «Таблицы – Сводная таблица».
- При появлении окна «Создание сводной таблицы» активировать строку ввода диапазона, устанавливая курсор.
- Выбрать диапазон и нажать ОК.
Примечание: Если сводка должна находиться после создания на этом же листе, пользователь устанавливает переключатель на нужную опцию.
5. При появлении боковой панели для настройки объекта перенести категории в нужные области или включить переключатели («галочки»).
Созданная сводка автоматически подсчитывает итоги по каждому столбцу.
Рекомендуемые сводные таблицы
Поздние версии MS Excel предлагают воспользоваться опцией «Рекомендуемые сводные таблицы». Подобная вариация анализа информации применяется в случаях невозможности правильного подбора полей для строк и столбцов.
Для применения рекомендуемых сводных таблиц:
- Выделить ячейки с введенной информацией.
- При клике на пиктограмму «Таблицы» выбрать пункт «Рекомендуемые сводные таблицы».
- Табличный процессор автоматически анализирует информацию и предлагает оптимальные варианты решения задачи.
- В случае выбора подходящего пункта таблицы и подтверждения через ОК получить сводную таблицу.
Готовые шаблоны в Excel 2016
Табличный процессор MS Excel 2016 при запуске предлагает выбрать оптимальный шаблон для создания таблицы. В офисном пакете представлено ограниченное количество шаблонов. В Интернете пользователь может скачать дополнительные образцы.
Чтобы воспользоваться шаблонами:
- Выбирать понравившийся образец.
- Нажать «Создать».
- Заполнить созданный объект в соответствии с продуманной структурой.
Оформление
Экстерьер объекта – важный параметр. Поэтому пользователь изучает не только, как построить таблицу в Excel, но и как акцентировать внимание на конкретном элементе.
Создание заголовка
Дана таблица, нарисованная посредством инструмента «Границы». Для создания заголовка:
Выделить первую строку, кликнув ЛКМ по численному обозначению строки.
На вкладке «Главная» найти инструмент «Вставить».
Активировать пункт «Вставить строки на лист».
После появления пустой строки выделить интервал клеток по ширине таблицы.
Нажать на пиктограмму «Объединить» и выбрать первый пункт.
Задать название в ячейке.
Изменение высоты строки
Обычно высота строки заголовка больше первоначально заданной. Корректировка высоты строки:
- Нажать правой кнопкой мыши (ПКМ) по численному обозначению строки и активировать «Высота строки». В появившемся окне указать величину строки заголовка и нажать ОК.
- Или перевести курсор на границу между первыми двумя строками. При зажатой ЛКМ оттянуть нижнюю границу ряда вниз до определенного уровня.
Выравнивание текста
Если пользователь предполагает расположение текста в ячейке иное, нежели по умолчанию, то пользуется пиктограммами «Выравнивание» относительно горизонтали и вертикали, а также кнопкой «Ориентация». Выбор пунктов выпадающего списка позволит расположить текст по вертикали или диагонали.
Изменение стиля
Изменение размера шрифта, начертания и стиля написания осуществляется вручную. Для этого пользователь пользуется инструментами блока «Шрифт» на вкладке «Главная» или вызывает диалоговое окно «Формат ячеек» через ПКМ.
Пользователь может воспользоваться пиктограммой «Стили». Для этого выбирает диапазон ячеек и применяет понравившийся стиль.
Как вставить новую строку или столбец
Для добавления строк, столбцов и ячеек:
- выделить строку или столбец, перед которым вставляется объект;
- активировать пиктограмму «Вставить» на панели инструментов;
- выбрать конкретную опцию.
Удаление элементов
Для удаления объектов на листе MS Excel пользователь активирует аналогичную кнопку на панели инструментов, предварительно выделив строку, столбец, ячейку.
Заливка ячеек
Для задания фона ячейки, строки или столбца:
- выделить диапазон;
- найти на панели инструментов пиктограмму «Цвет заливки»;
- выбрать понравившийся цвет.
II способ
- вызвать «Формат ячеек» через ПКМ;
- перейти на вкладку «Заливка»;
- выбрать цвет, способы заливки, узор и цвет узора.
III способ
- щелкнуть на стрелочку в блоке «Шрифт»;
- перейти на вкладку «Заливка»;
- выбрать понравившийся стиль.
Формат элементов
На панели инструментов находится пиктограмма «Формат». Опция помогает задать размер ячеек, видимость, упорядочить листы и защитить лист.
Формат содержимого
Последний пункт из выпадающего списка «Формат» на панели быстрого доступа позволяет назначить тип данных или числовые форматы, задать параметры внешнего вида и границы объекта, установить фон и защитить лист.
Использование формул в таблицах
Табличный процессор пользуется успехом благодаря возможности применения математических, статистических, логических и т.п. функций.
Ознакомиться с полным списком и вписываемыми аргументами пользователь может, нажав на ссылку «Справка по этой функции».
Для задания формулы:
- активировать ячейку, где будет рассчитываться формула;
- открыть «Мастер формул»;
или
- написать формулу самостоятельно в строке формул и нажимает Enter;
или
- применить и активирует плавающие подсказки.
На панели инструментов находится пиктограмма «Автосумма», которая автоматически подсчитывает сумму столбца. Чтобы воспользоваться инструментом:
- выделить диапазон;
- активировать пиктограмму.
Использование графики
Для вставки изображения в ячейку:
- Выделить конкретную ячейку.
- Перейти в меню «Вставка – Иллюстрации – Рисунки» или «Вставка – Рисунок».
- Указать путь к изображению.
- Подтвердить выбор через нажатие на «Вставить».
Инструментарий MS Excel поможет пользователю создать и отформатировать таблицу вручную и автоматически.
