logo

Блог Александра Томма

О том, как заставить Microsoft Office работать на Вас

Срезы в Excel и другие приемы работы с таблицами

Работа с таблицами Excel Дата: 22 ноября 2015 Категория: Excel
Поделиться, добавить в закладки или распечатать статью

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

Навигация по таблице

Перемещение по ячейкам таблицы почти не отличается от навигации в обычной табличной структуре Эксель. Вы, так же, можете активировать ячейку мышью, или перемещаться с помощью клавиатурных стрелок и кнопок PgUp, PgDn.

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

Перемещение таблицы

Переместить табличку можно двумя способами:

  1. Перетягиванием. Выделите любую ячейку внутри таблицы и ведите мышь к любой из границ таблицы. Как только курсор примет вид четырёхнаправленной стрелки – зажимайте левую кнопку мыши и тяните таблицу в новое место
  2. Вырезанием-вставкой:
    • Выделите всю таблицу любым способом. Например, мышью, или двойным нажатием Ctrl+A
    • Вырежьте таблицу с помощью команды Вырезать контекстного меню, комбинации Ctrl+X, или команды Главная – Буфер обмена – Вырезать
    • Вставьте табличку в новое место. Для этого, установите курсор в ту ячейку, где будет располагаться верхний левый угол таблицы. Выберите Вставить в контекстном меню, нажмите Ctrl+V или выполните Главная – Буфер обмена – Вставить

Благодаря этим простым действиям, вы можете перенести таблицу в любое место на листе.

Выделение элементов таблицы

Чтобы выделить всю строку полностью – наведите мышкой на левую границу первого столбца таблицы. Когда курсор станет чёрной стрелкой – жмите левую кнопку мыши, вся строка таблицы будет выделена. Или же выделите любую ячейку строки и нажмите Shift+Пробел. В принципе, правила выделения не отличаются от общепринятых в Эксель

Для выделения всего столбца – наведите мышкой на верхнюю грань таблицы (в нужном столбце). Как только курсор изменится на стрелку «Вниз» — жмите левой кнопкой мыши. Так же, можно активировать любую ячейку столбца и нажать Ctrl+Пробел. Нажмите эту комбинацию дважды, чтобы программа выделила и шапку.

Выделить всю табличку целиком – выберите любую ячейку таблицы и нажмите Ctrl+A. Для выделения диапазона вместе с шапкой – нажмите Ctrl+A еще раз.  Еще один способ – поместите стрелку мыши в верхний левый угол первого столбца шапки. Когда курсор превратится в диагональную стрелку – сделайте левый клик мышью. Чтобы выделить таблицу с шапкой – повторите такой клик.

Как добавить строку итогов

Обычно, мы вручную добавляем строку «Итого» в конец своей таблички. Если диапазон преобразован в таблицу Excel, программа добавит итоги одним нажатием мыши. Для этого, выделите любую ячейку таблицы и поставьте галочку: Конструктор – Параметры стилей и таблиц – Строка итогов

Строка итогов Excel
Вставка строки итогов

Итоги будут добавлены в конец таблички. Кстати, в строке итогов могут быть различные функции подсчёта:

  • Нет – оставить поле пустым
  • Среднее – считать среднее значение по столбцу
  • Количество – считать количество значений в столбце
  • Количество чисел – подсчитать количество числовых значений
  • Максимум – вывести максимальное значение в столбце
  • Минимум – вывести минимальное значение в столбце
  • Сумма – сумма всех значений
  • Смещённое отклонение – просчитать соответствующий статистический показатель
  • Смещённая дисперсия – считать статистический показатель
  • Другие функции – выбрать другую функцию из полного списка
Функции в строке итогов Эксель
Функции в строке итогов

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

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

Как удалить дубликаты из таблицы

Удаление дубликатов в Excel – будь то обычный диапазон или таблица – задание очень простое. Если в вашей табличке есть лишние, «задвоенные» данные – выполните команду: Конструктор – Сервис – Удалить дубликаты. В появившемся окне выберите столбцы, по которым нужно определять дублированные данные и нажмите ОК. Дальше программа всё сделает за вас.

Удаление дубликатов в Excel
Удаление дубликатов из столбца «Продавец»

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

Сортировку столбцов таблицы можно делать по одному столбцу или сразу по нескольким. Для этого предложу Вам 2 способа:

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

    Например, в таблице с рисунка нужно отсортировать по фамилии продавца, а потом сумму продаж по убыванию, чтобы увидеть в какие дни продавцы делали наибольшие продажи. Для этого, сначала отсортируем продажи по убыванию, а потом фамилии продавцов от «А» до «Я».

  2. С помощью окна сортировки. Выполните команду Главная – Сортировка и фильтр – Настраиваемая сортировка.
Сортировка данных по нескольким параметрам
Сортировка данных по нескольким параметрам

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

Фильтрация данных

По умолчанию, в таблицах всегда включён автофильтр. Если его нет – установите флажок: Конструктор – Параметры стилей таблиц – Кнопка фильтра. После этого в окне автофильтра установите флажки возле тех пунктов, которые нужно оставить на экране. Можно установить фильтры в нескольких столбцах, чтобы получить сложную выборку.

Фильтрация с помощью автофильтра в Эксель
Фильтрация с помощью автофильтра

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

Фильтр по активной ячейке Excel
Фильтр по активной ячейке

Чтобы отменить заданные фильтры и сортировки – выполните команду Главная – Сортировка и фильтр – Очистить.

Срезы в таблицах Эксель

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

Срезы в Эксель
Срезы в Excel

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

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

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

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

Поделиться, добавить в закладки или распечатать статью

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

4 комментариев

  1. Не могли бы Вы помочь разобраться мне с одной ситуацией. Необходимо в сводной таблице в срезе выбирать диапазон. Пример: есть данные по остаткам в днях, это числа и их довольно много, их нужно разбить на группы до 20, от 20-60 и выше 60. Можно было бы использовать условное форматирование, но не могу в сводной таблице фильтровать по цвету. Буду очень признательна, если поможете мне

    Ответить
    1. Анастасия, здравствуйте. В срезе можно выбирать диапазон. Для этого в нужном окне среза выберите минимальное значение диапазона, зажмите Shift и кликните по максимальному значению этого диапазона. Будет выделена и отображена таблица с отбором по диапазону.
      Еще один способ: можно добавить еще один столбец, в котором проставить группы и далее включить их в срез. Группировать можно, например, нестрогим поиском ВПР, как я описывал в этой статье.
      Анастасия, пишите, если не получится, будем разбираться дальше.

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

        Ответить
        1. Анастасия, помогу Вам, но вслепую не получится, нужно посмотреть. Направьте мне Ваш файл (или хотя бы копию экрана) на почту a.tomm [собачка] officelegko.com

          Ответить