Таблица, впервые появившаяся в Excel 2007, представляет собой прямоугольный диапазон данных, содержащий строку с текстовым заголовком для описания содержимого каждого столбца. Excel, конечно, всегда поддерживал таблицы, но новая реализация существенно облегчает выполнение общих задач, и результаты теперь выглядят намного лучше. И что более важно, новые возможности таблиц могут помочь устранить некоторые наиболее часто встречающиеся ошибки. Многие пользователи упускают из виду эту новую функцию, особенно после 9 класса колледж, но на самом деле ей стоит уделить внимание.
Понимание того, что такое таблица
Таблица — это простой прямоугольный диапазон структурированных данных. Каждая строка в таблице соответствует одной сущности. Например, строка может содержать информацию о клиенте, банковском переводе, сотруднике или продукте. Каждый столбец хранит определенную часть информации.
Например, если строки включают информацию о сотрудниках, то в столбцах могут содержаться такие данные, как имя, номер сотрудника, дата принятия на работу, заработная плата или отдел. В таблицах есть строка заголовка вверху которая описывает информацию, содержащуюся в каждом столбце.
ЛАЙФХАК EXCEL
До сих пор я не сказал ничего нового. Каждая предыдущая версия Excel могла работать с таким типом таблиц. Все интересное начинается, когда вы указываете Excel преобразовать диапазон данных в «официальную» таблицу. Вы делаете это, выбирая любую ячейку в диапазоне, а затем выполняя команду Вставка ► Таблицы ► Таблица.
Когда вы явно определяете диапазон в качестве таблицы, Excel может более разумно реагировать на действия, которые вы выполняете, используя этот диапазон. Например, если вы создаете диаграмму из таблицы, то диаграмма расширяется автоматически по мере добавления новых строк в таблицу. Создав сводную таблицу, вы сможете обновлять ее, добавляя в исходную таблицу любые новые данные.
Диапазон ячеек или таблица
В чем разница между стандартным диапазоном ячеек и таблицей?
- Активизация любой ячейки в таблице дает доступ к новой контекстной вкладке Работа с таблицами на ленте (рис. 158.1).
- Вы можете быстро применить цвет фона или текста, выбирая его из галереи. Этот тип форматирования не является обязательным.
- Каждый заголовок столбца содержит раскрывающийся список, который можно использовать для сортировки данных или фильтрации таблицы, чтобы скрыть определенные строки.
- Если вы прокрутите лист вниз так, что строка с заголовками исчезнет, то заголовки таблицы заменят буквы столбцов в заголовке листа. Другими словами, вам не нужно закреплять верхнюю строку, чтобы сохранять описания столбцов видимыми.
- Таблицы поддерживают рассчитываемые столбцы. Одна формула в столбце автоматически распространяется на все ячейки в столбце.
- Таблицы поддерживают структурированные ссылки. Вместо ссылок на ячейки формулы могут использовать имена таблиц и заголовки столбцов.
- Когда вы перемещаете указатель мыши в правый нижний угол правой нижней ячейки, можете щелкнуть кнопкой мыши и начать перетаскивание для расширения размера таблицы: либо горизонтально (добавить больше столбцов), либо вертикально (добавить больше строк).
- Выделение строк и столбцов внутри таблицы упрощено.
Как сравнить данные в excel за 1 мин?
Рис. 158.1. Когда таблица выбрана. Excel отображает контекстную вкладку Работа с таблицами
При работе с таблицами нужно помнить о некоторых ограничениях.
- По некоторым причинам, когда книга содержит хотя бы одну таблицу, Excel не позволяет использовать функции настраиваемых представлений (выберите Вид ► Режимы просмотра книги ► Представления).
- Вы не можете вставить автоматические промежуточные итоги в таблицу (выбирая команду Данные ► Структура ► Промежуточный итог).
Создание таблицы
В большинстве случаев вы создаете таблицу из существующего набора данных. Excel также позволяет создавать таблицу из пустого диапазона, а данные вы можете заполнить позже. Следующие инструкции предполагают, что у вас уже есть ряд данных, которые подходят для таблицы.
- Убедитесь, что диапазон не содержит совершенно пустые строки или столбцы.
- Активизируйте любую ячейку в диапазоне.
- Выберите Вставка ► Таблицы ► Таблица (или нажмите Ctrl+T). Откроется диалоговое окно Создание таблицы, показанное на рис. 158.2. Excel пытается определить диапазон и установить, есть ли строка заголовка в таблице. В большинстве случаев ему это удается.
- Если Excel не удается угадать, внесите свои корректировки, прежде чем нажать кнопку ОК.
Рис. 158.2. Используйте окно Создание таблицы, чтобы проверить, верно ли Excel определил размерность таблицы
Диапазон преобразуется в таблицу (он использует стиль таблицы по умолчанию), и появляется контекстная вкладка Работа с таблицами.
Excel может неверно определить размерность таблицы, если таблица не отделена от другой информации по крайней мере одной пустой строкой или столбцом. Если Excel угадывает неверно, просто укажите точный диапазон для таблицы в диалоговом окне Создание таблицы или нажмите кнопку Отмена и измените лист так, чтобы таблица была отделена от других данных хотя бы одной пустой строкой или столбцом.
Источник: excelexpert.ru
ТАБЛИЦЫ В EXCEL
Обработка данных в Excel обычно подразумевает работу с взаимосвязанной группой данных. Практически каждая последующая версия электронных таблиц расширяет возможности управления данными, вводя новые объекты. Поэтому необходимо обратить особое внимание на сходство и различие понятий диапазон, список и таблица, которые можно рассматривать как развитие различных способов структурирования данных от простого к сложному.
ДИАПАЗОН
Под диапазоном во всех версиях Excel понимается группа ячеек (две и более), объединенных обычно некоторым смысловым значением. Для задания диапазона используется двоеточие, например, A1:D2.
Диапазон может быть смежным или несмежным. Для его выделения необходимо щелкнуть мышью по верхней левой ячейке диапазона и, не отпуская кнопку, переместить указатель в нижнюю правую ячейку диапазона. Выделение несмежных диапазонов осуществляется при нажатой клавише Ctrl.
Диапазону ячеек также можно присвоить имя, которое затем используется как ссылка на этот диапазон. Таким образом, на диапазон можно ссылаться по адресу или по имени.
Смежный диапазон данных можно считать первым шагом структурирования данных. Такие инструменты, как сортировка, фильтрация, подведение итогов и другие, применимы только к смежным диапазонам, например к АЗ:С17 (рис. 1.1).
Источник: ozlib.com
Создаем именованные диапазоны в Excel
Именованные диапазоны помогут вам быстрее обрабатывать отдельные элементы таблицы, например я, часто ими пользуюсь.
Что такое именованные диапазоны в Excel?
Итак, если вам необходимо часто обрабатывать какие-либо конкретные элементы таблицы — вы можете просто «назвать» их и выделять уже по названию (имени).
Грубо говоря, мы можем выделить какую-либо ячейку или диапазон ячеек и дать ему имя. В таком случае, выбрав имя, мы сможем быстро указать какие элементы необходимо задействовать.
Допустим, у нас есть такая табличка:
Например, здесь мы можем создать диапазон Дат, имен продавцов и продаж.
Вам может показаться, что это совершенно бесполезно, но я уверяю вас — вы ошибаетесь. Когда речь идет о больших объемах данных — именованные диапазоны одна из лучших функций!
Как я уже сказал, мы можем создать диапазон ячеек из каждого столбика:
А также присвоить ей конкретное имя.
Плюсы использования именованных диапазонов
Давайте рассмотрим плюсы использования этой функции.
Используем имена в функциях
Итак, допустим вы создали именованный диапазон. Теперь вы можете использовать его даже в функциях!
К примеру, так можно посчитать сумму всех значений диапазона:
=СУММ(SALES)
Для нашей таблички, мы можем использовать следующие функции, если создадим диапазон каждого столбика:
- Сумма продаж тех продавцов, которые продали более 500 позиций: =СЧЁТЕСЛИ(Sales,»>500″);
- Сумма продаж продавца Tom: =СУММЕСЛИ(SalesRep, «Tom»,Sales);
- Чистая прибыль продавца Joe:
=СУММЕСЛИ(SalesRep, «Joe»,Sales)*Комиссия
В общем, ничего сложного нет, мы просто заменяем ссылки на ячейки в формулах именем диапазона.
Прописать имя гораздо быстрее чем диапазон ячеек
Но, главный плюс, я считаю — увеличение скорости работы с формулами. В случае если вы не используете именованный диапазон, вам в каждой новой формуле нужно указывать диапазон ячеек. С именованным диапазоном все проще!
Вы начинаете вводить его имя и Excel сам понимает что вы хотите написать, таким образом ошибки исключены:
Функции становятся динамическими
Если вы использовали определенные значения при расчетах, а после поменяете эти значения — результат не изменится.
А если же вы используете именованный диапазон — все будет пересчитываться каждый раз!
Как создать именованный диапазон
Есть 3 метода, давайте рассмотрим каждый.
Функция «Присвоить имя»
- Выделите ячейку (или диапазон);
- Щелкните на «Формулы» -> «Присвоить имя»;
- Итак, укажите имя и проверьте диапазон;
- Подтвердите.
С помощью поля «Имя»
- Выделите ячейку (или диапазон);
- А теперь просто впишите имя в поле «Имя»;
- Диапазон будет создан для всего файла Excel (а не для отдельного листа). Если вам нужно создать диапазон только одного листа, вам нужен рассмотренный ранее вариант.
Функция «Создать из выделенного»
Этим вариантом выгодно пользоваться, когда вам нужно создать сразу несколько именованных диапазонов. Например, как в нашем случае, мы хотим создать 3 диапазона из каждого столбика нашей таблички.
- Выделите ячейку (или диапазон);
- Щелкните на «Формулы» -> «Создать из выделенного»;
- В открывшемся окне, выберите ту опцию, которая соответствует положению заголовков в вашей табличке. Грубо говоря, укажите где Excel должен искать заголовки таблички (в нашем случае — «в строке выше»);
Таким образом, он создал три диапазона с названиями наших столбиков, меняя пробел на «_»;
Важная информация
Есть несколько моментов, которые вам нужно знать о именованных диапазонах:
- Первым символом имени может быть только буква, «_» или «». Во всех остальных случаях будет ошибка;
- Именем не может быть диапазон ячеек;
- А также в именах не может быть пробелов, вы можете использовать «_»;
- Регистр не имеет значения;
- Максимальная длина имени — 255 символов.
Поиск нужного именованного диапазона
Бывает такое, что при работе с табличкой вы создаете очень много диапазонов. А потом забываете как называется тот, который нужен.
Давайте я покажу.
Просмотр списка созданных именованных диапазонов.
- Щелкните на «Формулы» -> «Использовать в формуле»;
- Далее — «Вставить имена…»;
Итак, открылся список всех диапазонов.
Автоподстановка имени диапазона
Если вы хотя бы помните как начиналось имя нужного диапазона — вы можете использовать этот вариант.
Редактируем уже созданный именованный диапазон
- Щелкните на «Формулы» -> «Диспетчер имен»;
- В открывшемся окне будут все созданные диапазоны и их описания’
- Щелкните на «Изменить…»;
- Поменяйте то что нужно;
- Подтвердите.
Горячие клавиши
Какие есть горячие клавиши:
- Список всех диапазонов — F3;
- Открыть «Диспетчер имен» — CTRL + F3;
- Создать диапазон из выделенного — CTRL + SHIFT + F3.
Динамический именованный диапазон
До сих пор мы рассмотрели только те именованные диапазоны, которые не будут менять свою область.
Если мы создали B1:B20 диапазон, то он всегда будет ссылаться на эту область.
Грубо говоря, если у вас появятся новые ячейки в столбике, наш именованный диапазон не будет учитывать и включать в себя их. Это не очень удобно, что же делать?
В Excel можно создать и автоматически изменяемый именованный диапазон. Если я добавлю несколько новых ячеек в столбик, он будет включать в себя и их.
Для этого мы будем вызывать функцию ИНДЕКС.
Допустим у вас есть такая табличка:
- Щелкните на «Формулы» -> «Присвоить имя»;
- В открывшемся окне укажите данные на ваш выбор, а в поле «Диапазон» введите — =$A$2:ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,»<>»<>»»))
ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,»<>» выдаст нам значение последнего элемента столбика.
Но если функция будет выглядеть так: (=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,»<>»https://excelexcel.ru/sozdaem-imenovannye-diapazony-v-excel/» target=»_blank»]excelexcel.ru[/mask_link]