Как сделать перечисление в excel в формуле

Обновлено: 07.07.2024

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

20 июля 2021 г. 4934

Введение

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

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

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

Задача:

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

Мы будем двигаться поэтапно, уделяя внимание всем возможностям данного инструмента.

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

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

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

Выпадающий список готов!

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

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

Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ .

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

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

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

Для начала создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

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

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

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

Именованные диапазоны готовы.

В ячейке D2, которая используется в качестве аргумента функции ДВССЫЛ , находится текстовое выражение, которое совпадает с именем соответствующего именованного диапазона с названиями городов. В результате функция возвращает ссылку на соответствующий именованный диапазон.

Зависимый выпадающий список адресов готов.

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

Чтобы устранить это несоответствие, перед тем как применять функцию ДВССЫЛ , обработаем значения функцией ПОДСТАВИТЬ .

Функция ПОДСТАВИТЬ заменяет определенный текст в текстовой строке на новое значение. Вместо: =ДВССЫЛ(D2) укажем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"-";"_"))

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

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

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

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

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

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

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

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

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

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

Таблица1[Уникальные] – столбец с перечнем всех пунктов списка

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

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

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

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

В ячейку F2 (зависимый выпадающий список адресов) вместо: =ДВССЫЛ(ПОДСТАВИТЬ(E2;"-";"_")) вставляем: =СМЕЩ($B$2;ПОИСКПОЗ(E2;$B$2:$B$18;0)-1;1;СЧЁТЕСЛИ($B$2:$B$18;E2);1)

Для корректной работы этого способа, данные в столбце с городом должны быть отсортированы. Функция СМЕЩ будет динамически ссылаться только на ячейки адресов определенного города.

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

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

Смещение по строкам – считает функция ПОИСКПОЗ , которая выдает порядковый номер ячейки с выбранным городом (E2) в заданном диапазоне ( $B$2:$B$18 )

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

Высота – вычисляем с помощью функции СЧЁТЕСЛИ , которая подсчитывает количество встретившихся в диапазоне ( $B$2:$B$18 ) нужных нам значений – названий городов (E2)

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

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

С выпадающими списками в Google таблицах все немного иначе.

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

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

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

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

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

Создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

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

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

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

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

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

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

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

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

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

В ячейке G6 укажем:

Важно: для корректной работы этого способа, данные в столбце с городом должны быть отсортированы от А до Я, или от Я до А. Подробнее о том, как в данном случае работает функция СМЕЩ читайте выше в примере с Excel.

Заключительным этапом поместим результат функции СМЕЩ в диапазон выпадающего списка.

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

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

Заключение

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

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

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

Тип и формат данных в Excel и Google таблицах

21 июня 2021 г. 831 MS Excel Video File Google Sheets

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

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

22 сентября 2021 г. 395 Info Health

В этой статье мы рассмотрим основные показатели состава тела более детально. Что такое ИМТ? Какой % жира считается нормой? Сколько воды должно быть в организме?

Простая CRM-система в Google таблицах

2 мая 2021 г. 2104 Google Sheets Code Video File

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

Процесс анализа данных (Часть I)

6 июня 2021 г. 1043 Info Statistics Analytics

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

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

Поиск перечня доступных функций в Excel

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

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

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

Вставка функции в таблицу

Использование математических операций в Excel

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

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

Растягивание функций и обозначение константы

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

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

В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.

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

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

В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:

$В$2 – при растяжении либо копировании остаются постоянными столбец и строка.

B$2 – неизменна строка.

$B2 – константа касается только столбца.

Построение графиков функций

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

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

Здравствуй уважаемый пользователь!

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

Список уникальных значений возможно создать 6-ю способами:

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

Создать список уникальных значений с помощью специальной функции

Создать список уникальных значений с помощью расширенного фильтра

Создать список уникальных значений с помощью формул

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

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

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"«)

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

=ЕСЛИ(МАКС(A1:A100)

С ростом богатства растут и заботы. Гораций

По окончании этого урока вы сможете:

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

1. Арифметические операторы

Все эти символы вводятся с клавиатуры. Рассмотрим на примере.

Шаг 1. Щелкаем по ячейке А1, куда будет введена формула.

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

Шаг 3. Нажимаем Enter. Активной станет ячейка А2.

Шаг 4. Делаем активной ячейку А1.

Понять и запомнить!

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

Алгоритм ввода формулы при помощи арифметических операторов:

Щелкаем на ячейке, в которой должны появиться результаты формулы.
Вводим сначала знак равенства (=), а потом − первое значение.
Вводим арифметический оператор, а затем − второе значение.
После того, как вся формула введена, нажимаем Enter, чтобы завершить ввод.

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

При расчете плотности вязания мне часто приходится вычислять пропорцию:

Размер	Количество петель
10 см образца	18 петель
50 см размер изделия	Сколько нужно набрать петель?

Шаг 1. Щелкаем на ячейке, в которой должны появиться результаты формулы:

Шаг 5. Завершаем ввод, нажав клавишу Enter.

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

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

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

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

Все время контролируем полученное значение. Уже 90. А заодно внимательно посмотрим на строку формул. Формула записалась с соблюдением синтаксиса Excel.

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

Изменяем значение в любой ячейке, которая участвует в формуле, например в ячейке B2:

Формула вычислила новое значение

4. Формула, записанная вручную

Удалите в ячейке В3 формулу. Записываем формулу заново.

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

Разбираться с этим меню вы будете весь световой день, но ошибку не найдёте. Так что лучше не делать ошибок в синтаксисе формул. Самое лучшее в этом случае щелкнуть ЛМ по строке формул:

5. Суммирование строк и столбцов

Шаг 1. Создаем ряд чисел в столбце и делаем активной ячейку суммирования.

Шаг 2. Повторяем Шаги 1÷2 из пункта 4:

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

Шаг 1. Нажимаем Enter

Есть более простой способ: воспользоваться кнопкой Автосумма (AutoSum) на ленте Главная (Лента Главная → группа команд Редактирование → команда Автосумма). Эта кнопка продублирована на ленте Формулы.

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

Щелкаем ЛМ на первой пустой ячейке, находящейся ниже столбца или справа от строки, содержимое в ячейках которых нужно сложить.

Шаг 1. Нажимаем кнопку Автосумма на ленте Главная:

Шаг 2. Нажимаем Enter

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

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

Шаг 1. Повторяем Шаги 1÷2 из пункта 4:

Шаг 7. Выбираем первый диапазон (1), затем второй диапазон (2) простым перетаскиванием ЛМ:

6. Далеко идущие выводы

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

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

4. Формула с использованием встроенной функции и данных, которые находятся в двух диапазонах

Теперь вы можете:

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

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

Читайте также: