Ref-sgain.ru

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

Как синхронизировать все листы книги с тем же диапазоном, выбранным в Excel?

Выпадающий список уникальных значений. Автоматическое обновление выпадающего списка

Зависимый выпадающий список в Excel и Google таблицах

20 июля 2021 5997 года.

Введение

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

Давайте рассмотрим детали создания раскрывающихся списков на примере:

Исходные данные

  • Список адресов в разных городах

Заданье:

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

Мы идем шаг за шагом и сосредотачиваемся на всех возможностях этого средства.

Рабочие файлы по ссылке ниже

Ниже представлен обзорный видеоролик о работе с выпадающими списками в Excel и Google Spreadsheets. Приятного просмотра!

Как сделать выпадающий список в Excel?

Давайте начнем с основ. Чтобы создать выпадающий список, вам понадобится список данных и инструмент Data Validation.

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

Руллеменилисты он ясный!

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

Как создать зависимый выпадающий список в Excel?

Существует несколько вариантов. Один из них заключается в сочетании именованных интервалов и функции DWSSYL.

Именованный диапазон в Excel — это ячейка (или диапазон ячеек), которая имеет имя.

Функция DWSSYL в Excel конвертирует текст в ссылку.

Способ 1: именованные диапазоны + функция ДВССЫЛ

Начните с создания именованных строк с адресами. Назовите их в соответствии с их городом.

Алгоритм создания именованной области: выберите область, затем «Формулы» — «Установить имя

У нас есть пять названных диапазонов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

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

Поэтому вместо дефиса в названии города Ростов-на-Дону мы указываем приемлемый символ — подчеркивание.

Указанные временные интервалы готовы.

Теперь выберите ячейку для второго выпадающего списка, того, который будет зависимым. Перейдите к инструменту «Проверка данных», тип данных — «Список». В поле «Источник» используется функция: =DVSSRL(D2) , где D2 — адрес ячейки, содержащей первый выпадающий список городов.

Функция DVSSYL использует ячейку D2 в качестве аргумента и содержит текстовое выражение, которое сопоставляет названия городов с названиями соответствующих именованных диапазонов. Затем функция возвращает ссылку на соответствующий именованный диапазон.

Теперь имеется раскрывающийся список зависимых адресов.

Изменение значения в ячейке D2 изменяет списки в ячейке E2. За исключением города Ростова-на-Дону. В выпадающем списке городов (ячейка D2) название пишется через тире, а названная область подчеркивается.

Читайте так же:
Как создать маркированный список в ячейке или нескольких ячейках в Excel?

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

С помощью функции SUBSTITUTE можно заменить определенные тексты в текстовой строке новыми значениями. Вместо: =DVSYSL(D2), мы будем использовать =DVSYSL(SUBSTITUTE(D2;»-«;»_»))

Это означает, что мы предварительно вычисляем значения, чтобы они соответствовали правилам написания имен. Если в названии города есть дефисы, они заменяются подчеркиванием.

Теперь она работает и для города с дефисом в названии — Ростова-на-Дону. Вот список городов, которые вы можете выбрать.

Как автоматически обновить выпадающий список в Excel, при добавлении новых данных?

Сначала создайте умную таблицу Excel из ряда данных. Это можно сделать, нажав Ctrl+T .

Одной из полезных особенностей умного стола является диапазон растягивания. Это означает, что если мы добавляем новые строки, они автоматически добавляются в выпадающий список. Например, добавьте новый город — Санкт-Петербург. И вот он уже появился в нашем первом выпадающем списке.

Как сделать выпадающий список уникальных значений в Excel?

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

И добавьте новый столбец в область умной таблицы. «Дизайнер» — «Размер таблицы». Вместо =$B$1:$C$17 введите: =$A$1:$C$17.

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

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

Чтобы заставить Excel рассматривать нашу формулу как матричную, нажмите Ctrl + Shift + Enter .

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

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

«Данные проверки» — «Список». В источнике данных вместо приведенного выше диапазона с названиями городов =$B$2:$B$18 задайте название — =Unique.

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

Чтобы удалить их, мы уточним диапазон под названием «Single». В менеджере имен вместо диапазона =Table1[Single] используем: =MEMBERSY(Sheet1!$A$2;0;0;0;0;COUNT(Table1[Single])-COUNT(Table1[Single]))

Где: Sheet1!$A$2 — ячейка со значением первого элемента списка уникальных значений

Table1[Unique] — столбец со списком всех элементов в списке

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

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

Как сделать автоматически обновляемый зависимый список? Способ 2: СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ

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

Если ячейка F2 (зависимый выпадающий список) должна быть выпадающим списком, вместо =DVSSSIL(SUBSTITUTE(E2);»-«;»_») вставьте: =MESSAGE($B$2;SEARCHING(E2);$B$2:$B$18;0);1;READ($B$2;$B$18;E2);1)

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

Аргументы функции:

Ссылка — берем первую ячейку в нашем списке, т.е. $B$2

Читайте так же:
Как скрыть сразу несколько пустых строк или столбцов в Excel в Excel?

Функция ПОИСК вычисляет смещение строки, определяя порядковый номер ячейки с выбранным городом (E2) в диапазоне (B2:B18).

Смещение колонки = 1, поскольку мы хотим обращаться к адресам в соседней колонке (C)

Высота над уровнем моря — рассчитывается функцией CALCULATION , которая подсчитывает количество вхождений в диапазон ( $B$2:$B$18 ) искомых значений — названий мест (E2)

Ширина = 1, так как нам нужен один столбец адресов

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

Есть несколько отличий от выпадающих списков в таблицах Google

Как сделать выпадающий список в Google таблицах?

Выпадающие списки можно создавать в Google Spreadsheets с помощью инструмента проверки данных.

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

Настройка валидации данных — «Значение из диапазона» под «Данные»

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

Как создать зависимый выпадающий список в Google таблицах?

Вернемся к двум основным формам, которые мы видели в Excel.

Способ 1: именованные диапазоны + ДВССЫЛ

Создаются именованные диапазоны с адресами. Назовите каждый из них в соответствии с городом.

Выделите ячейки — «Данные» — «Определить именованные диапазоны».

Введите имя и нажмите «Готово». У нас есть 5 названных рангов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

Как и в Excel, в Google Таблицах также есть список требований к именам диапазонов.

Поэтому вместо дефиса в названии города Ростов-на-Дону укажем приемлемый символ: подчеркивание.

В Google Spreadsheets мы не сможем настроить функцию DUTSYL в инструменте проверки данных, как в Excel. Поэтому поместите результат функции DUTSYL в пустые ячейки справа. Не забудьте добавить управление цитируемыми значениями с помощью функции DELETE. Мы уже объяснили, почему это необходимо в примере Excel.

В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;»_»;»-«))

Последний ключ при создании зависимого выпадающего списка, в разделе «Настроить проверку данных», указывает список в столбце F:F в качестве диапазона.

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

Как автоматически обновить выпадающий список в Google таблицах при добавлении новых данных?

В раскрывающемся списке городов просто расширьте диапазон и укажите вместо =$A$2:$A$16: =$A$2:$A . Теперь, когда добавляется новый город, он автоматически появляется в выпадающем списке.

Как автоматически обновить зависимый выпадающий список в Google таблицах при добавлении новых данных?

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

В ячейке G6 введите :

Чтобы этот метод работал правильно, данные должны быть отсортированы от A до Z или от Z до A. Приведенный выше пример Excel показывает, как работает функция СООБЩЕНИЕ в этой ситуации.

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

Читайте так же:
Как скопировать сумму выбранных ячеек только в Excel?

Для удобства вспомогательные колонны могут быть скрыты.

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

Заключение

Теперь вы знаете несколько способов создания выпадающих списков в Excel и Google Spreadsheets. Посмотрите примеры и создайте нужные вам выпадающие списки.

Вы можете научиться работать в Excel на наших онлайн-курсах Excel: Онлайн-курсы Excel

Пройдите бесплатный тест на нашем сайте, чтобы оценить свой уровень знаний инструментов и функций Excel: Пройдите викторину

5 полезных инструментов Excel и Google таблиц

25 мая, 2021 833 MS Excel Видео Google Sheets

Знакомство с инструментами Excel и Google таблиц: сортировка данных, группировка строк, выпадающие списки, флажки, условное форматирование.

Простой дашборд в Excel

14 апреля 2021 1977 MS Excel Видео файл

Приборные панели — одна из самых популярных тем в Excel. В этой статье мы рассмотрим правила работы с приборной панелью и создадим простую приборную панель Excel.

Показатели состава тела. Расшифровка и интерпретация

22 сентября 2021 года. 467 Информация Здоровье

Здесь мы рассмотрим основные измерения состава тела. Что такое ИМТ? Какой процент жира считается нормальным? Почему важно, чтобы в организме было достаточно воды?

ABC-XYZ анализ. Как определить самые ходовые товары

24 октября 2021 1079 Инфо Статистика Аналитика MS Excel

Матричный ABC-XYZ-анализ в Excel. Узнайте, зачем нужно проводить анализ ABC-XYZ и как интерпретировать его результаты

Как отправить один лист Excel как вложение по электронной почте

Как отправить один лист Excel как вложение по электронной почте

Мы часто создаем и работаем с несколькими листами Excel. Это могут быть отдельные листы в одной рабочей книге (например, разные листы для разных лет, продуктов, отделов и т.д.).

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

Но для этого необходимо проделать некоторую предварительную работу.

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

Вот, как это сделать:

  • Щелкните правой кнопкой мыши любую вкладку ленты и выберите «Настроить панель быстрого доступа».
  • В диалоговом окне «Параметры Excel» выберите «Все команды» в раскрывающемся списке «Выбрать команду из».
  • Прокрутите вниз и выберите опцию «Отправить получателю почты».
  • Щелкните по кнопке Добавить. Это добавит параметр «Отправить получателю почты» на правой панели.
  • Щелкните ОК.

Это добавит значок «Отправить получателю почты» на панель быстрого доступа.

Вот что можно сделать с помощью этого параметра:

  • Отправьте часть рабочего листа в теле письма.
  • Отправьте рабочий лист Excel в качестве вложения электронной почты.
  • Отправить всю рабочую тетрадь в качестве вложения в электронную почту.

Примечание: Я тестировал это только в Outlook. Я думаю, что он также должен работать с вашим почтовым клиентом по умолчанию.

Отправить часть рабочего листа как тело сообщения электронной почты

В панели быстрого доступа появилась опция «Отправить получателю», которая позволяет отправить часть рабочего листа (диапазон ячеек) в качестве тела письма.

Читайте так же:
Как создать / сделать резервную копию файлов / копий книг в Excel?

Как это сделать?

  • Активируйте рабочий лист, из которого вы хотите отправить по электронной почте диапазон ячеек.
  • Щелкните значок «Отправка получателя почты» на панели быстрого доступа.
  • В открывшемся запросе электронной почты выберите «Отправить текущий лист как тело сообщения» и нажмите «ОК».
    • Это сделает текущий рабочий лист телом исходящего электронного письма. Он автоматически откроет почтовый клиент по умолчанию в вашей системе, и рабочий лист будет частью тела сообщения электронной почты.

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

    Отправить один лист Excel как вложение по электронной почте

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

    • Щелкните правой кнопкой мыши лист, который вы хотите отправить, и выберите «Переместить» или «Копировать».
    • В диалоговом окне «Переместить или скопировать» выберите (новая книга) в раскрывающемся списке «В книгу». Также отметьте опцию Create a copy в левом нижнем углу.
    • Щелкните ОК. Это создаст новую книгу, в которой будет только скопированный лист.
    • Активировав только что созданную книгу, щелкните значок «Отправить получателю почты» на панели быстрого доступа.
    • В диалоговом окне «Электронная почта» выберите вариант «Отправить всю книгу как вложение».
    • Щелкните ОК. Откроется исходящее электронное письмо с прикрепленной книгой. Поскольку это новая книга, которую мы создали, и в ней только один лист (который мы переместили), вы отправляете только один лист.
    • Щелкните Отправить.

    Отправить всю книгу как вложение по электронной почте

    Отправьте всю книгу, вложив ее в электронное письмо.

    Вот как это должно быть сделано:

    • Нажмите «Отправить получателю почты» на панели быстрого доступа.
    • В диалоговом окне «Электронная почта» выберите вариант «Отправить всю книгу как вложение».
    • Щелкните ОК. Откроется исходящее электронное письмо с прикрепленной к нему книгой.
    • Щелкните Отправить.

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

    Exceltip

    Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

    Именованные диапазоны в Excel — несколько трюков использования

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

    Итак, вот несколько советов о том, как сделать работу с именованными диапазонами в Excel быстрее и продуктивнее.

    Многоразовое создание именованного диапазона в один прием

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

    именованные диапазоны в excel

    Например, у вас есть набор данных (как показано ниже), и вы хотите создать отдельные именованные диапазоны для каждого столбца. Вместо того чтобы создавать их по одному, вы можете воспользоваться комбинацией клавиш CTRL +SHIFT +F3, которая откроет диалоговое окно Создать имена из выбранного диапазона. Это же окно доступно на вкладке Формулы -> Определенные имена -> Создать из выбранного. Теперь вы можете создать более одного диапазона — по строкам, столбцам, по обоим параметрам.

    создание имен из выделенного диапазона

    Когда вы нажмете кнопку OK, Excel создаст четыре именованных диапазона. Название каждого диапазона будет служить именем. При желании вы можете легко изменить все атрибуты диапазона.

    Доступ к управлению именованными диапазонами

    Чтобы открыть диалоговое окно Менеджер имен, перейдите на вкладку Формулы в группе Определенные имена, а затем нажмите кнопку Менеджер имен. В качестве альтернативы нажмите комбинацию клавиш Ctrl + F3.

    диспетчер имен

    Использование формулы СМЕЩ

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

    Функция смещ в именованном диапазоне

    Использование абсолютных ссылок при работе с именованными диапазонами

    Я не знаю, является ли это особенностью дизайна или ошибкой. Если вы используете относительные ссылки (A1 вместо $A$1) при определении именованного диапазона, они не останутся на том же месте, даже если вы этого хотите. Давайте рассмотрим этот случай на примере. Предположим, вы хотите создать диапазон, который идет вниз на 10 строк от ячейки A1. Первое, что приходит в голову, это написать формулу =MEMBERSY(A1;10;0).

    диспетчер имен

    Пока все хорошо. Если вы захотите воспользоваться этим именованным диапазоном, необходимо подобрать для нее ячейку (скажем B1) и ввести что-то типа =мой_имен_диап. Где мой_имен_диап — это имя, которое вы дали диапазону на предыдущем шаге.

    функция смещ относительные ссылки

    Но если вы выделите другую ячейку и снова откроете диспетчер имен, то формула, которую вы ввели перед =MEMBERS(Sheet1!A1;10;0), волшебным образом изменится (например, =MEMBERS(Sheet1!A1048576;10;0)). Это происходит потому, что мы использовали относительные ссылки для создания именованного диапазона, т.е. целевой диапазон будет все время перемещаться в соответствии с адресом текущей выделенной ячейки.

    функция смещ относительные ссылки

    Использование F2 для изменения именованного диапазона

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

    функция смещ относительные ссылки

    Чтобы не запутаться при использовании стрелок, нажмите кнопку F2.

    Возможно, у вас есть свои собственные приемы использования именованных диапазонов! Не хотите поделиться?)

    Вам также могут быть интересны следующие статьи

    2 комментария

    Если я использую именованные диапазоны, увеличится или уменьшится размер файла? Использование именованных диапазонов может повлиять на производительность формул? Спасибо.

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

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector