Умная таблица в excel (эксель): создание и использование

Умная таблица в excel (Эксель): создание и использование        Доброго времени суток друзья!

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

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

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

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

       Соблюдая такие простые и тем не менее эффективные правила, хотя я наверное и не всё перечислил, позволит вам не мучатся в попытках исправить последствия своих неаккуратных действий. Хотя в новых версиях, Excel позволит вам некоторые действия производить в автоматическом режиме или просто упростит их настройку.

  •        Рассмотрим поэтапно работу с умными таблицами:
  • 1. Как создается  умная таблица Excel
  •       Создается умная таблица Excel всего в пару кликов, легко и просто. Ставите курсор на любую ячейку таблицы, а потом процедура создания можно разделить на 3 способа:
  • 1 способ: с помощью горящей комбинации клавиш Ctrl+T;
  • 2 способ: на панели управление выбираем вкладку «Главная» и выбираем пункт «Форматируем как таблицу», после выбираем понравившийся вам дизайн, а их там более полусотни.Умная таблица в excel (Эксель): создание и использование
  • 3 способ: на панели управление нам нужна вкладка «Вставка» и пункт «Таблица».Умная таблица в excel (Эксель): создание и использование

     Следующим этапом вы подтверждаете диапазон вашей таблицы, указываете вариант отображения заголовка таблицы и нажимаем ОК. Умная таблица создана.

2. Как форматируется умная таблица Excel

       Приступаем к самому красивому этапу работы с умными таблицами – это форматирование.

Форматирование имеется в виду не только нацепить шаблонную «шкурку» та таблицу и разукрасить ее в разные цвета в целом так и изменить стили отображаемых ячеек, это возможность находится в панели управления в разделе «Главная» и выбрать пункт «Стили ячеек».

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

       При выборе формата умная таблица Excel много функций включает автоматически:

  • Таблице присваивается имя, которое можно изменить на вкладке «Конструктор»;Умная таблица в excel (Эксель): создание и использование
  • Умная таблица получает возможность автоматически подстраиваться под размер данных и изменять свою высоту и ширину при их добавлении или удалении;
  • В шапке умной таблицы включается «Автофильтр» (выпадающий список) для удобства и быстроты (есть возможность его принудительного отключения на вкладке «Данные»);Умная таблица в excel (Эксель): создание и использование
  • Если вы добавляете новые строки или столбцы производится и автоматическое копирование формул;
  • Появляется возможность на вкладке «Конструктор» включить возможность «Показать итоговою строку» где есть возможность выбора функции отображения данных (сумма, количество, среднее и т.д.);Умная таблица в excel (Эксель): создание и использование
  • При работе с функциями обращаться к данным умной таблицы возможно указывая имена отдельных элементов (к примеру, для суммирование данных таблицы по столбику «Сумма» мы можем использовать формулу такого вида =СУММ(Название таблицы[Сумма]) вместо =СУММ(H2:H54), что позволит не задумываться о количестве строк и корректности указанного диапазона).

3. Работа со стилями таблицы

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

Я думаю, вы согласитесь со мной, что таким образом таблицы становятся лучше и понятней.Умная таблица в excel (Эксель): создание и использование     А применить все стили и подобрать наиболее отвечающий, вашим нуждам можно используя пункт меню «Форматировать как таблицу». В выпадающем списке вы можете выбрать вариант, который вам максимально подходит, это сделать легко так как, просто при наведении указателя мышки на вашу таблицу автоматически применяется указанный стиль.

4. Как создать имя умной таблицы

        Как и многим элементам в Excel, умным таблицам можно присвоить имя в поле «Имя таблицы» и при неоднократном использовании увеличить эффективность ее использования. Также присвоенное имя начнёт отображаться в Диспетчере имен, который находится по пути «Формулы»-«Диспетчер имен».

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

Умная таблица в excel (Эксель): создание и использование

5. Как работает со срезами умной таблицы Excel

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

Умная таблица в excel (Эксель): создание и использование       Использовать эту возможность можно в панели управления «Конструктор», выбрав пункт «Вставить срез» указав в конструкторе какую именно информацию вам нужно. После выбираете стиль среза, и у вас всё работает.Умная таблица в excel (Эксель): создание и использование       Кроме умных таблиц вам может быть интересно: «Как создать сводную таблицу в Excel», «Как получить данные со сводной таблицы», «8 способов как сравнить две таблицы в Excel».

       Спасибо за ваше внимание, надеюсь, было полезно и интересно, буду рад вашим м и лайкам!

Источник: http://topexcel.ru/chto-delaet-umnaya-tablica-excel/

Как сделать таблицу в Excel. Пошаговая инструкция

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

Как работать в Excel с таблицами. Пошаговая инструкция

  • Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;
  • Первая строка таблицы должна содержать короткие, уникальные заголовки;
  • Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;
  • Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;
  • В таблице не должно быть пустых строк и абсолютно пустых столбцов.

1. Выделите область ячеек для создания таблицы

Умная таблица в excel (Эксель): создание и использование

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

2. Нажмите кнопку “Таблица” на панели быстрого доступа

Умная таблица в excel (Эксель): создание и использование

На вкладке “Вставка” нажмите кнопку “Таблица”.

3. Выберите диапазон ячеек

Умная таблица в excel (Эксель): создание и использование

Во всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

4. Таблица готова. Заполняйте данными!

Умная таблица в excel (Эксель): создание и использование

Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

Умная таблица в excel (Эксель): создание и использование

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

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

Умная таблица в excel (Эксель): создание и использование

  • Отображение строки заголовков – включает и отключает заголовки в таблице;
  • Строку итогов – включает и отключает строку с суммой значений в колонках;
  • Чередующиеся строки – подсвечивает цветом чередующиеся строки;
  • Первый столбец – выделяет “жирным” текст в первом столбце с данными;
  • Последний столбец – выделяет “жирным” текст в последнем столбце;
  • Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
  • Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

Как добавить строку или столбец в таблице Excel

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

Читайте также:  Вход в инстаграм с компьютера как войти мою страницу

Умная таблица в excel (Эксель): создание и использование

  • Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.

Умная таблица в excel (Эксель): создание и использование

  • Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

Умная таблица в excel (Эксель): создание и использование

Как отсортировать таблицу в Excel

  1. Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:
  2. Умная таблица в excel (Эксель): создание и использование
  3. В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

Как отфильтровать данные в таблице Excel

Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

  • “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
  • “Фильтр по цвету” так же как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
  • “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
  • Во всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

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

Как посчитать сумму в таблице Excel

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

В списке окна выберите пункт “Таблица” => “Строка итогов”:

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

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

Как в Excel закрепить шапку таблицы

Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.

Для того чтобы закрепить заголовки сделайте следующее:

  • Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:
  • Выберите пункт “Закрепить верхнюю строку”:
  • Теперь, прокручивая таблицу, вы не потеряете заголовки и сможете легко сориентироваться где какие данные находятся:

Как перевернуть таблицу в Excel

  • Представим, что у нас есть готовая таблица с данными продаж по менеджерам:
  • На таблице сверху в строках указаны фамилии продавцов, в колонках месяцы. Для того чтобы перевернуть таблицу и разместить месяцы в строках, а фамилии продавцов нужно:
  • Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):
  • Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:
  • В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:
  • Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!

Источник: https://excelhack.ru/kak-sdelat-tablicu-v-excel/

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

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

Умная таблица в excel (Эксель): создание и использование

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

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

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

  2. В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table]. 

Умная таблица в excel (Эксель): создание и использованиеУмная таблица в excel (Эксель): создание и использование

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

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

Присвоение имени таблице 

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

  1. Выделить ячейку таблицы.
  2. На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы нажать клавишу Enter.

Умная таблица в excel (Эксель): создание и использование

Требования к именам таблиц аналогичны требованиям к именованным диапазонам.

Форматирование таблиц

Умная таблица в excel (Эксель): создание и использование

  1. Выделить ячейку таблицы.
  2. На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].

Вычисления в таблицах

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

  1. На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

Умная таблица в excel (Эксель): создание и использование

  1. В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию. 

Умная таблица в excel (Эксель): создание и использование

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

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

Для проведения расчетов и помещения результатов в новое поле, достаточно ввести формулу в одну ячейку этого поля. Excel автоматически размножит ее по всем ячейкам данного поля. Если правильно установлены параметры Excel, при вводе формулы в нее записываются не адреса ячейки, а имена полей.

Умная таблица в excel (Эксель): создание и использование

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

  1. Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
  2. В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
  3. OK.

Преобразование таблицы в обычный диапазон 

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

  1. На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
  2. Выбрать вкладку Преобразовать в диапазон [Convert to Range].

Умная таблица в excel (Эксель): создание и использование

  1. Нажать на кнопку Да [Yes].

Источник: https://micro-solution.ru/excel/ranges/creating-tables

«Умная таблица» в Excel

Создать «Умную таблицу», которая впервые была представлена в Excel 2007, очень просто. Для ввода данных в нее можно использовать автоформу, которая защитит формулы от случайной перезаписи. В Excel 2010 для «Умной таблицы» был добавлен инструмент «Срезы» для упрощения фильтрации данных.

Общие правила создания и заполнения таблиц

Чтобы ваша таблица, «обычная» или «умная», правильно работала, следует придерживаться при ее создании и заполнении следующих несложных правил:

  • не использовать в таблице объединенные ячейки;
  • присваивать графам (колонкам) уникальные имена;
  • в одной графе использовать один формат данных;
  • не оставлять при заполнении таблицы пустых строк;
  • в «обычной» таблице следует выделить строку заголовков (шапку таблицы) форматированием (цвет, шрифт) и закрепить на начальном экране.

Создание «Умной таблицы»

Для создания «Умной таблицы» необходимо выбрать любую ячейку внутри таблицы без форматирования или выделить произвольный диапазон, в котором планируется создать такую таблицу, и нажать кнопку «Форматировать как таблицу» на вкладке «Главная». Откроется окно выбора формата будущей «Умной таблицы»:

Умная таблица в excel (Эксель): создание и использование

Выбрать можно любой образец форматирования таблицы и нажать на него, а после создания «Умной таблицы» точнее подобрать форматирование с помощью предпросмотра.

После нажатия на образец формата программа Excel предложит проверить диапазон будущей таблицы и выбрать, где будет создана строка заголовков (шапка таблицы) — внутри таблицы, если она уже с заголовками, или над таблицей в новой строке:

Умная таблица в excel (Эксель): создание и использование

В примере заголовки уже присутствуют внутри диапазона с таблицей, поэтому галочку «Таблица с заголовками» оставляем. Нажав «OK», получим следующую «Умную таблицу»:

Читайте также:  Обзор игровых приставок для телевизора — выбираем лучшие модели

Умная таблица в excel (Эксель): создание и использование

Теперь при записи формулы создаются адреса с именами колонок, а при нажатии «Enter» формула автоматически копируется во все ячейки этой графы:

Умная таблица в excel (Эксель): создание и использование

Адреса с именами колонок создаются, если аргументы для формулы берутся из той же строки. Аргументы, взятые из других строк, будут отображены обычными ссылками.

Когда «Умная таблица» уже создана, подобрать для нее подходящее цветовое оформление становится легче.

Для этого нужно выбрать любую ячейку внутри таблицы и снова нажать кнопку «Форматировать как таблицу» на вкладке «Главная».

При наведении курсора на каждый образец форматирования, «Умная таблица» будет менять цветовое оформление в режиме предпросмотра. Остается только выбрать и кликнуть на подходящем варианте.

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

На вкладке «Конструктор» отображены все инструменты для работы с «Умной таблицей» (неполный перечень):

  • редактирование имени таблицы;
  • изменение цветового чередования строк на цветовое чередование столбцов;
  • добавление строки итогов;
  • удаление кнопок автофильтра;
  • изменение стиля таблицы (то же, что и по кнопке «Форматировать как таблицу» на вкладке «Главная»);
  • удаление дубликатов;
  • добавление срезов*, начиная с Excel 2010;
  • создание сводной таблицы;
  • удаление функционала «Умной таблицы» командой «Преобразовать в диапазон».

*Срезы представляют из себя удобные фильтры по графам в отдельных окошках, работающие аналогично кнопкам автофильтра в строке заголовков. Создается срез (или срезы) нажатием кнопки «Вставить срез» и выбором нужной колонки (или колонок). Чтобы удалить срез, его нужно выбрать и нажать на клавиатуре «Delet» или пункт «Удалить (имя среза)» в контекстном меню.

Работа с «Умной таблицей»

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

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

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

Умная таблица в excel (Эксель): создание и использование

В этом меню необходимо выбрать пункт «Другие команды…» для перехода к окну настройки панели быстрого доступа:

Умная таблица в excel (Эксель): создание и использование

Здесь необходимо выбрать в верхнем раскрывающемся списке «Команды не на ленте», в окне со списком команд выделить пункт «Форма…», нажать кнопку «Добавить >>», а затем кнопку «OK». На панели быстрого доступа появится кнопка вызова автоформы.

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

Умная таблица в excel (Эксель): создание и использование

Обратите внимание, что ячейка с формулой не редактируется. Добавление новой записи или сохранение внесенных изменений в существующую строку происходит при нажатии кнопок «Добавить», «Закрыть» или «Enter» на клавиатуре. При нажатии кнопок «Назад» или «Далее» внесенные изменения не сохраняются.

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

Самым простым способом было бы вставить формулу =[предыдущая ячейка]+1, но она не копируется автоматически в новую запись. А в ячейках с этой формулой программа Excel выводит замечание: «Несовместимая формула в вычисляемом столбце».

Для нашего примера подойдет следующая формула: =СТРОКА([@Наименование])-1, которая вычисляет номер текущей строки рабочего листа Excel и уменьшает ее на единицу, так как первая запись в нашей «Умной таблице» начинается со второй строки рабочего листа Excel.

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

Преимущества «Умной таблицы»

К преимуществам «Умной таблицы» можно отнести следующие:

  • автоматическое форматирование по выбранному шаблону;
  • автоматическое расширение «Умной таблицы» при добавлении новых записей или колонок;
  • автоматическое копирование формул в новую запись;
  • возможность добавления строки итогов (критерий расчета итога для каждой отдельной графы можно выбрать из раскрывающегося списка);
  • возможность использования автоформы* для добавления новых записей и редактирования существующих (такая же возможность существует и для обычных таблиц).

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

Источник: https://vremya-ne-zhdet.ru/ofisnaya-rabota/umnaya-tablitsa-v-excel/

Умные таблицы Excel 2007-2013

48220 25.11.2012 Скачать пример

Видео

Постановка задачи

Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера — вот такого вида:

Умная таблица в excel (Эксель): создание и использование

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):

Умная таблица в excel (Эксель): создание и использование

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

Умная таблица в excel (Эксель): создание и использование

В результате после такого преобразования диапазона в «умную» Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

  1. Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
  2. Созданная один раз Таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью: Умная таблица в excel (Эксель): создание и использование  
  3. В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)).
  4. При добавлении новых строк в них автоматически копируются все формулы.
  5. При создании нового столбца с формулой — она будет автоматически скопирована на весь столбец — не надо тянуть формулу черным крестом автозаполнения.
  6. При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр): Умная таблица в excel (Эксель): создание и использование
  7. Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу: Умная таблица в excel (Эксель): создание и использование
  8. К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
  • =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
  • =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
  • =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
  • =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
  • =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы. (В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

P.S

В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list). Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

Источник: https://www.planetaexcel.ru/techniques/2/136/

Умные таблицы

Сегодня поговорим про ТАБЛИЦЫ. Не про таблицы, а именно про ТАБЛИЦЫ. Именно так Microsoft предложил называть те замечательные таблицы, о которых пойдёт речь ниже. В зачаточном состоянии они появились в Excel 2003 и назывались там «списками» («lists»).

В Excel 2007 их довели до ума и переименовали в ТАБЛИЦЫ (TABLES), а то что раньше все нормальные люди называли таблицами, теперь предложено называть ДИАПАЗОНОМ (range).

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

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

Зачем они нужны?

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

Читайте также:  Как скрыть скрытые файлы и папки в windows 10

  1. Всем столбцам давать уникальные названия колонок.
  2. Не допускать пустых столбцов и строк в таблице.
  3. Не допускать разнородных данных в пределах одной колонки. Если уж решили, что, например, в колонке E должен хранится объем продаж в штуках, то не надо туда же вносить объём продаж, скажем, в деньгах у части строк таблицы.
  4. Не объединять ячейки без самой крайней необходимости.
  5. Форматировать таблицу, чтобы она выглядела одинаково во всех своих частях. То есть элементарно рисовать сетку, выделять цветом заголовки столбцов.
  6. Закреплять области, чтобы заголовок был всегда виден на экране.
  7. Ставить фильтр по умолчанию.
  8. Вставлять строку подитогов.
  9. Грамотно использовать абсолютные и относительные ссылки в формулах, чтобы их можно было протягивать без необходимости внесения изменений.
  10. При рабте с таблицей не выделять цветом строки/столбцы за пределами таблицы. Это поветрие, кстати очень сильно распространено, — взять выделить всю строку или весь столбец одним кликом мыши и закрасить. И наплевать, что в таблице 100 строк, а закрасилось помимо них ещё 1 000 000 строк. А потом невинно интересоваться: «Почему мои файлы так много весят?»

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

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

Итак, давайте познакомимся, как создаются умные таблицы и какими полезными свойствами обладают.

1.Создание умной таблицы

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

  1. Способ 1 — на ленте ГЛАВНАЯ выбираем Форматировать как таблицу, выбираем понравишейся дизайн (при этом вам доступны 60 стандартных способа форматирования)
  2. Способ 2 — Нажимаем Ctrl-T
  3. Способ 3 — На ленте ВСТАВКА выбрать Таблица

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

2.Форматирование

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

  1. Выделите таблицу целиком — проще всего 2 раза нажать Ctrl-A (латинская «A»!)
  2. На ленте ГЛАВНАЯ щёлкните Стили ячеек, далее стиль Обычный

При этом все проблемы с форматированием сразу решаются. Однако вам придётся восстанавливать форматы столбцов ячеек: формат даты, времени, нюансы числового формата (типа количества знаков после точки), но это не очень сложно. В любом случае вам решать — сбрасывать форматирование этим способом, либо каким-то другим, менее «разрушительным», но знать о нём надо.

3.Предпросмотр стиля таблицы

Умная таблица в excel (Эксель): создание и использование

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

4.Прочие плюшки и полезности..

5.Упрощенное выделение таблицы, столбцов, строк

6.Умная таблица имеет имя и его можно изменять

Умная таблица в excel (Эксель): создание и использование

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

7.Вставка срезов

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

Умная таблица в excel (Эксель): создание и использование

8.Структурированные формулы

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

Умная таблица в excel (Эксель): создание и использование

«Умный» способ адрессации На что ссылается Формула возвращает Стандартный диапазон
=СУММ(Результаты) По умолчанию умная таблица, которая названа «Результаты» ссылается на область своих данных 87 B3:E7
=СУММ(Результаты[#Данные]) Тот же результат вернёт данная формула, где область данных указана в явном виде. 87 B3:E7
=СУММ(Результаты[Продажи]) Суммируем область данных столбца «Продажи». Если надо создать именованный диапазон, который будет ссылаться на столбец умной таблицы, то надо использовать синтаксис Результаты[Продажи]. 54 D3:D7
=Результаты[@Прибыль] Данную формулу мы вводили в строке 3. @ — означает текущую строку, а Прибыль — столбец, из которого возвращаются данные. 6 E3
=СУММ(Результаты[Продажи]:Результаты[Прибыль]) Ссылка на диапазон столбцов: от колонки «Продажи», до колонки «Прибыль» включительно. Обратите внимание на оператор «:», который создаёт диапазон. 87 D3:E7
=СУММ(Результаты[@]) Формулу вводили в троке 3. Она вернула всю строку таблицы. 11 B3:E3
=СЧЁТЗ(Результаты[#Заголовки]) Подсчёт количества элементов в #Заголовки. 4 B2:E2
=Результаты[[#Итоги];[Продажи]] Формула возвращает итоговую строку для столбца Продажи. Это не одно и тоже, что Результаты[Продажи], так как итоговая функция может быть разной, например, средней величиной. 54 D8

Предупреждение для любителей полазить по иностранным сайтам: товарищи, учитывайте различия в региональных настройках России и западных стран (англоязычных то точно).

В региональных настройках есть такой параметр, как «Разделитель элементов списка». Так вот на Западе это запятая, а у нас точка с запятой.

Поэтому, когда они пишут формулы в Excel, то параметры разделяются запятыми, а когда мы пишем, то — точкой с запятой. Тоже самое и в переводных книгах, везде.

Умная таблица в excel (Эксель): создание и использование

  • Создание выпадающего списка в ячейке

Источник: http://perfect-excel.ru/publ/excel/razlichnye_instrumenty_excel/umnye_tablicy/10-1-0-33

3 приема работы с умными таблицами в Excel

Начиная с Excel 2007 в программе появился еще один объект — таблица.

Умная таблица

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

Давайте рассмотрим три приема работы с умными таблицами.

Прием #1. изменение размера

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

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

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

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

Изменение размера таблицы

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

Изменение размеров таблицы через вкладку Конструктор

Прием #2. строка итогов

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

Строка итогов

Аналогичного эффекта можно добиться, установив табличный курсор под ячейку, итоги для которой нужно вывести, и нажав сочетание Alt + =

Промежуточные итоги с помощью сочетания клавиш

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

Параметры итоговой строки

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

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

Суммирование значений

Прием #3. вставка формул

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

Вставка формулы в столбец

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

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

Параметры автозамены

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

Включить автозамену можно через параметры, которые также доступны в меню автозамены. Либо через меню Файл Параметры Правописание Параметры автозамены Автоформат при вводеСоздавать вычисляемые столбцы.

Включение вычисления формул в столбцах таблицы

Более детально все приемы разобраны в видео:

Видеокурс «Microsoft Excel 2016 Шаг за Шагом»

Источник: https://zen.yandex.ru/media/id/5c4d8a530aaeef00ac928ac3/5c90d7f6a611ae00b42d7a88

Ссылка на основную публикацию