Elettracompany.com

Компьютерный справочник
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как удалить умную таблицу excel

Создаем и удаляем таблицы в Excel

Лист Excel — это заготовка для создания таблиц (одной или нескольких). Способов создания таблиц несколько, и таблицы, созданные разными способами, обеспечивают разные возможности работы с данными. Об этом вы можете узнать из этой статьи.

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

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

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

С точки зрения разработчиков Excel, то, что вы создали, называется диапазон ячеек. С этим диапазоном вы можете производить различные операции: форматировать, сортировать, фильтровать (если укажете строку заголовков и включите Фильтр на вкладке Данные) и тому подобное. Но обо всем перечисленном вы должны позаботиться сами.

Чтобы создать таблицу, как ее понимают программисты Microsoft, можно выбрать два пути:

  • преобразовать в таблицу уже имеющийся диапазон;
  • вставить таблицу средствами Excel.

Вариант преобразования рассмотрим на примере таблицы, которая показана на рисунке выше. Проделайте следующее:

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

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

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

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

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

Удаление таблиц

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

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

Приемы создания и удаления таблиц, о которых вы узнали из этой статьи, пригодятся вам в Excel 2007, 2010 и старше.

Умные таблицы Excel: секреты эффективной работы и особенности

В последние годы появились новые «умные» технологии: умные телефоны, умные дома, холодильники, чехлы для телефонов, телевизоры и т. д. Не стали исключением и электронные таблицы. Умные таблицы в Excel были внедрены начиная с версии 2010 года.

Понятие об умных таблицах

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

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

При внедрении умных таблиц улучшается восприятие документа электронной таблицы, обработка данных становится проще, и они приводятся к определенной системе.

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

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

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

Далее возникнет диалоговое окно, в котором будет предложено уточнить диапазон ячеек, входящих в данную таблицу, и если таблица содержит заголовки («шапку»), то необходимо поставить галочку около надписи «Таблица с заголовками».

После чего необходимо подтвердить выполненные действия путем нажатия на кнопку «ОК» или нажав Enter.

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

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

Выпадающие списки

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

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

Выделяем ячейку, в которой будет находиться данный список, идем на вкладку «Данные», выбираем группу «Работа с данными» и нажимаем «Проверка данных».

В появившемся окне нажимаем на стрелочку рядом с «Типом данных» и выбираем «Список».

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

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

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

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

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

Представленный способ является одним из секретов эффективной работы с умными таблицами в Excel.

Изменяем стиль умной таблицы

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

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

После этого перемещаемся в таб «Конструктор», группа «Стили таблиц». Если не хватает представленных стилей, кликаем на «Дополнительные параметры» и видим всю палитру стилей.

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

Кстати говоря, если ни один стиль не нравится, внизу можно нажать на ссылку «Создать стиль таблицы».

Параметры стилей

Умные таблицы в Excel 2010 и старше, а также в Excel 2007 можно настраивать под себя в зависимости от предпочтений и необходимости.

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

Использование формул

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

Читать еще:  Командная строка excel

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

Здесь <Название столбца>— конкретное название столбца, в котором осуществляется расчет, например «Сумма, руб.», арифметические действия — это алгебраические выражения, принятые в формулах Excel и, в случае необходимости, числа, @ — показывает, что данные будут взяты из той же строки.

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

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

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

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

Автофильтрация

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

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

Задаем название таблицы

После того как умная таблица в Excel создана, приложение присваивает ей название в соответствии с порядком ее следования (если это первая таблица будет присвоено название «Таблица1» и т. д.).

Для того чтобы узнать название таблицы, ставим курсор в любую ее ячейку, переходим в таб «Конструктор», группа «Свойства» и там находим параметр «Имя таблицы», который можно редактировать, для чего нужно поместить на «Таблица1» курсор, выделить и ввести новое название, после чего нажать Enter.

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

Название таблицы может быть использовано при применении функций ВПР, ГПР. Например, мы хотим найти, кто у нас был 1229-м покупателем. Для этого в какую-то свободную ячейку, например K1, вводим 1229, в ячейку L1 вводим формулу =ВПР(K1;Таблица1 (или ее измененное название);1). Здесь 1 — номер столбца, в котором будет осуществляться поиск. В результате в ячейке L1 появятся Ф. И. О. покупателя под №1229 (если, конечно, он был в первом столбце).

Адресация в умных таблицах

Умные таблицы в Excel позволяют вместо диапазона ячеек с данными применять имена полей. Так, чтобы подсчитать сумму по столбцу C с 1-й по 21-ю ячейку, можно ввести формулу, общепринятую в Excel = СУММ(C1:C21). Предположим, что в столбце С у нас занесена информация по сумме покупки, совершенной каждым конкретным покупателем, и называется этот столбец «Сумма, руб.», а таблица была переименована в «Покупатели». Таким образом, в качестве формулы может быть введено следующее выражение: = СУММ(Покупатели[Сумма, руб.]).

Помимо этого, можно использовать специальную адресацию:

  • «=Покупатели[#Эта строка]» будет ссылаться на текущую строку.
  • «=Покупатели[#Данные]» будет ссылаться на данные, не учитывая строки заголовков.
  • «=Покупатели[#Заголовки]» будет ссылаться на заголовки столбцов таблицы, расположенные в первой строке таблицы.
  • «=Покупатели[#Итоги]» будет ссылаться на итоговую строку (в случае ее наличия в умной таблице).
  • «=Покупатели[#Все]» будет ссылаться на всю таблицу, включая все вышеперечисленные формулы.

Так, например, подсчитать суммы покупок можно вводом формулы =СУММ(Покупатели[#Все]).

Дополнительные «фишки» умных таблиц

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

В Excel 2010 появилась возможность при работе с умными таблицами создавать срезы. Они представляют собой автофильтр отдельного столбца, представленный в виде элемента графики. Переходим в таб «Конструктор», группа команд «Инструменты», нажимаем «Вставить срез», в диалоговом окне выбираем названия столбцов (или одного столбца), из которых будет сформирован срез. При необходимости фильтрации таблицы выбираем необходимую категорию или категории, зажав кнопку Ctrl.

Как удалить умную таблицу Excel

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

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

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

В заключение

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

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

Видео

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

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

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

Решение

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

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

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

  1. Созданная Таблицаполучает имяТаблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design) . Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
  2. Созданная один раз Таблицаавтоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

  • В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data) ).
  • При добавлении новых строк в них автоматически копируются все формулы.
  • При создании нового столбца с формулой — она будет автоматически скопирована на весь столбец — не надо тянуть формулу черным крестом автозаполнения.
  • При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

    Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:

  • К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
    • =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
    • =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
    • =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
    • =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
    • =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы.

    (В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

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

    Очистка данных: набор основных инструментов очистки больших массивов данных Excel

    Сколько времени у вас занимает очистка данных перед тем, как можно будет приступить к анализу? Лишние пробелы, непечатаемые символы, разный регистр текста… Все это неизбежно при импорте данных в Excel или объединении нескольких книг. Тогда возникает вопрос: как быстро очистить большие таблицы, когда поиск, удаление вручную или копирование функций Excel не продуктивно?

    Надстройка «Очистка данных» поможет очистить большие массивы данных в секунды:

    • Удаление лишних пробелов, пробелов в начале и в конце ячейки
    • Удаление переносов строк и непечатаемых знаков
    • Изменение регистра текста во всём диапазоне
    • Преобразование чисел в текстовом формате в полноценные числа
    • Преобразование чисел в текстовые значения

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

    Добавить «Очистку данных» в Excel 2019, 2016, 2013, 2010

    Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).

    Как работать с надстройкой:

    Внимание: отмена последнего действия (UNDO) невозможна. Рекомендуем сохранять резервную копию файлов или использовать «Контроль версий» XLTools для отслеживания изменений.

    Как удалить пробелы в начале и в конце строки или все лишние пробелы

    1. Выберите диапазон > Нажмите кнопку «Очистка данных».
    2. На панели слева выберите одну или обе операции:
      • Удалить пробелы в начале/в конце строки
      • Удалить все лишние пробелы
    3. В нижней части панели нажмите кнопку «Применить» > Готово, все ненужные пробелы в текстовых строках удалены.

    Как удалить переносы строк в ячейках

    1. Выберите диапазон > Нажмите кнопку «Очистка данных».
    2. На панели слева выберите «Удалить переносы строк внутри ячеек».
    3. Нажмите кнопку «Применить» > Готово, все переносы строк (переносы текста на новую строку внутри ячейки) удалены.

    Как удалить непечатаемые знаки в ячейках

    1. Выберите диапазон > Нажмите кнопку «Очистка данных».
    2. На панели слева выберите «Удалить непечатаемые знаки».
    3. Нажмите «Применить» > Готово, все непечатаемые символы удалены.

    Как изменить регистр текста в ячейках

    1. Выберите диапазон > Нажмите кнопку «Очистка данных».
    2. На панели слева выберите «Изменение регистра букв» и выберите нужный вариант:
      • Каждое Слово С Заглавной Буквы
      • Предложение с заглавной буквы
      • все буквы в нижнем регистре
      • ВСЕ БУКВЫ В ВЕРХНЕМ РЕГИСТРЕ
    3. Нажмите кнопку «Применить» > Готово, регистр текста унифицирован во всём диапазоне.

    Как преобразовать числа (сохранённые как текст) в полноценные числа

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

    1. Выберите диапазон > Нажмите кнопку «Очистка данных».
    2. На панели слева выберите «Текст в числа».
    3. Нажмите кнопку «Применить» > Готово. Каждой преобразованной ячейке назначен числовой формат, сигналы об ошибке пропадут.

    Как преобразовать числа в текстовые значения

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

    1. Выберите диапазон > Нажмите кнопку «Очистка данных».
    2. На панели слева выберите «Числа в текст».
    3. Нажмите кнопку «Применить» > Готово. Каждой преобразованной ячейке назначен текстовый формат, также в верхнем левом углу появится обозначение, что число сохранено как текст.

    Появились вопросы или предложения? Оставьте комментарий ниже.

    Умные таблицы Excel: секреты эффективной работы и особенности

    В последние годы появились новые «умные» технологии: умные телефоны, умные дома, холодильники, чехлы для телефонов, телевизоры и т. д. Не стали исключением и электронные таблицы. Умные таблицы в Excel были внедрены начиная с версии 2010 года.

    Понятие об умных таблицах

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

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

    При внедрении умных таблиц улучшается восприятие документа электронной таблицы, обработка данных становится проще, и они приводятся к определенной системе.

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

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

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

    Далее возникнет диалоговое окно, в котором будет предложено уточнить диапазон ячеек, входящих в данную таблицу, и если таблица содержит заголовки («шапку»), то необходимо поставить галочку около надписи «Таблица с заголовками».

    После чего необходимо подтвердить выполненные действия путем нажатия на кнопку «ОК» или нажав Enter.

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

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

    Выпадающие списки

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

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

    Выделяем ячейку, в которой будет находиться данный список, идем на вкладку «Данные», выбираем группу «Работа с данными» и нажимаем «Проверка данных».

    В появившемся окне нажимаем на стрелочку рядом с «Типом данных» и выбираем «Список».

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

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

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

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

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

    Представленный способ является одним из секретов эффективной работы с умными таблицами в Excel.

    Изменяем стиль умной таблицы

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

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

    После этого перемещаемся в таб «Конструктор», группа «Стили таблиц». Если не хватает представленных стилей, кликаем на «Дополнительные параметры» и видим всю палитру стилей.

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

    Кстати говоря, если ни один стиль не нравится, внизу можно нажать на ссылку «Создать стиль таблицы».

    Параметры стилей

    Умные таблицы в Excel 2010 и старше, а также в Excel 2007 можно настраивать под себя в зависимости от предпочтений и необходимости.

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

    Использование формул

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

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

    Здесь <Название столбца>— конкретное название столбца, в котором осуществляется расчет, например «Сумма, руб.», арифметические действия — это алгебраические выражения, принятые в формулах Excel и, в случае необходимости, числа, @ — показывает, что данные будут взяты из той же строки.

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

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

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

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

    Автофильтрация

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

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

    Задаем название таблицы

    После того как умная таблица в Excel создана, приложение присваивает ей название в соответствии с порядком ее следования (если это первая таблица будет присвоено название «Таблица1» и т. д.).

    Для того чтобы узнать название таблицы, ставим курсор в любую ее ячейку, переходим в таб «Конструктор», группа «Свойства» и там находим параметр «Имя таблицы», который можно редактировать, для чего нужно поместить на «Таблица1» курсор, выделить и ввести новое название, после чего нажать Enter.

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

    Название таблицы может быть использовано при применении функций ВПР, ГПР. Например, мы хотим найти, кто у нас был 1229-м покупателем. Для этого в какую-то свободную ячейку, например K1, вводим 1229, в ячейку L1 вводим формулу =ВПР(K1;Таблица1 (или ее измененное название);1). Здесь 1 — номер столбца, в котором будет осуществляться поиск. В результате в ячейке L1 появятся Ф. И. О. покупателя под №1229 (если, конечно, он был в первом столбце).

    Адресация в умных таблицах

    Умные таблицы в Excel позволяют вместо диапазона ячеек с данными применять имена полей. Так, чтобы подсчитать сумму по столбцу C с 1-й по 21-ю ячейку, можно ввести формулу, общепринятую в Excel = СУММ(C1:C21). Предположим, что в столбце С у нас занесена информация по сумме покупки, совершенной каждым конкретным покупателем, и называется этот столбец «Сумма, руб.», а таблица была переименована в «Покупатели». Таким образом, в качестве формулы может быть введено следующее выражение: = СУММ(Покупатели[Сумма, руб.]).

    Помимо этого, можно использовать специальную адресацию:

    • «=Покупатели[#Эта строка]» будет ссылаться на текущую строку.
    • «=Покупатели[#Данные]» будет ссылаться на данные, не учитывая строки заголовков.
    • «=Покупатели[#Заголовки]» будет ссылаться на заголовки столбцов таблицы, расположенные в первой строке таблицы.
    • «=Покупатели[#Итоги]» будет ссылаться на итоговую строку (в случае ее наличия в умной таблице).
    • «=Покупатели[#Все]» будет ссылаться на всю таблицу, включая все вышеперечисленные формулы.

    Так, например, подсчитать суммы покупок можно вводом формулы =СУММ(Покупатели[#Все]).

    Дополнительные «фишки» умных таблиц

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

    В Excel 2010 появилась возможность при работе с умными таблицами создавать срезы. Они представляют собой автофильтр отдельного столбца, представленный в виде элемента графики. Переходим в таб «Конструктор», группа команд «Инструменты», нажимаем «Вставить срез», в диалоговом окне выбираем названия столбцов (или одного столбца), из которых будет сформирован срез. При необходимости фильтрации таблицы выбираем необходимую категорию или категории, зажав кнопку Ctrl.

    Как удалить умную таблицу Excel

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

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

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

    В заключение

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

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