Excel список значений в ячейке

Содержание

Как сделать выборку в Excel из списка с условным форматированием
Выбор уникальных и повторяющихся значений в Excel
Выпадающий список в Excel с помощью инструментов или макросов
Создание раскрывающегося списка
Выпадающий список в Excel с подстановкой данных
Как посчитать количество ячеек/значений в Excel
Выпадающий список в Excel с данными с другого листа/файла
Как сделать зависимые выпадающие списки
Выбор нескольких значений из выпадающего списка Excel
Выпадающий список с поиском
Excellama: Выпадающие списки и логические формулы ⁠ ⁠
Равно ли значение ячейки одному из списка значений? Простая формула Excel
Трюк Excel 25. Список уникальных значений
- Похожие статьи

В ячейке D16 считаем скидку с помощью логической формулы ЕСЛИ. Примем за правило, что если заказ собран на сумму более 5 000 руб., то скидка будет 10%.

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

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

Выбор уникальных и повторяющихся значений в Excel

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

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

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

Выделите первый столбец таблицы A1:A19.
Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

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

Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

Перед тем как выбрать уникальные значения из списка сделайте следующее:

Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

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

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

Выборка ячеек из таблицы по условию в Excel:

Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

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

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

Создать таблицу
Форматирование
Функции Excel
Формулы и диапазоны
Фильтр и сортировка
Диаграммы и графики
Сводные таблицы
Печать документов
Базы данных и XML
Возможности Excel
Настройки параметры
Уроки Excel
Карта сайта
Скачать примеры

Выпадающий список в Excel с помощью инструментов или макросов

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

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

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

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

Вручную через «точку-с-запятой» в поле «Источник».
Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$C$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target End If End If End If End Sub

Сохраняем, установив тип файла «с поддержкой макросов».

Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Как посчитать количество ячеек/значений в Excel

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

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

Делаем активной ячейку, куда хотим поместить раскрывающийся список.
Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

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

Создадим первый выпадающий список, куда войдут названия диапазонов.
Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Выбор нескольких значений из выпадающего списка Excel

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

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & «,» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
Жмем «Свойства» – открывается перечень настроек.
Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

Создать таблицу
Форматирование
Функции Excel
Формулы и диапазоны
Фильтр и сортировка
Диаграммы и графики
Сводные таблицы
Печать документов
Базы данных и XML
Возможности Excel
Настройки параметры
Уроки Excel
Карта сайта
Скачать примеры

Excellama: Выпадающие списки и логические формулы ⁠ ⁠

Как-то давно мне очень сильно полюбились выпадающие списки и то, как они работают в связке с логическими (и не только) формулами.

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

Равно ли значение ячейки одному из списка значений? Простая формула Excel

В этом примере мы разберем несколько инструментов Excel, а именно:

— логическая формула ЕСЛИ;

— формула блока «ссылки и массивы» ВПР.

Шаг 0 – введение.

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

Шаг 1 – работа с прайсом.

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

Для этого в ячейке C2 ставим наценку, допустим 1,47 (увеличение цены по сравнению с ценой закупки – 47%). Ее обязательно вывести в отдельную ячейку, потому что «а вдруг кризис» и придется все цены пересчитывать, не будем же мы всю таблицу заново просчитывать. Да и на будущее — в случае, если все строчки будут производить некоторые действия (умножение, сложение, вычитание, деление) с одной единственной ячейкой, то легче ее вывести отдельно и зафиксировать. Опять же, если мы захотим изменить наценку на все товары, то нам достаточно поменять значение только в одной этой ячейке, и вся наша таблица автоматически пересчитается.

А чтобы каждый раз в формуле вручную не ссылаться на одну и ту же ячейку (C2), то ее необходимо зафиксировать. Для этого необходимо поместить курсор в строку формул после знака умножить на C2 и нажать F4. Если в формуле появилось два (!) знака $, то ячейка зафиксирована.

Первоначальная формула в ячейке D5 будет выглядеть следующим образом =C5*$C$2

С первого взгляда все хорошо. Но это не так. В полученном результате в ячейке D5 больше двух знаков после запятой. Избавляемся от концов с помощью формулы ОКРУГЛ. Это наше первое знакомство с вложенной формулой. Формула в формуле. Самый простой способ «вложить» одно в другое – скопировать полученную формулу и следовать по инструкции ниже.

В ячейке D5 в строке формул пишем =ОКРУГЛ и открываем Аргументы функции (элемент Fx).

В поле «Число» вставляем скопированную формулу без знака «=».

В поле «Число_разрядов» ставим цифру 2, так как нам надо 2 знака после запятой.

Трюк Excel 25. Список уникальных значений

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

Готово. Вы великолепны!

Шаг 2 – заполнение карточки заказа.

Начнем с выпадающего списка.

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

Тип данных – список, источник – список товаров с листа «прайс» (диапазон B5:B16).

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

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

— щелкаем 2 раза за маркер автозаполнения и в появившемся окошке выбираем значение «Заполнить» — подходит при нумерации большого списка.

— тянем за маркер автозаполнения, параллельно зажав клавишу CTRL – может работать даже тогда, когда ячейка начинается не с 1, а с числа 824789, удобно продолжать нумерацию в середине списка.

Ура. Осталось два крупных шага (ВПР и логическая формула ЕСЛИ).

Для разгона давайте на ячейке F8 пропишем простую формулу умножения =D8*E8

Переходим к ВПР. Если по-простому, то формула «вертикальный поиск результата/вертикальный просмотр» берет ячейку со значением (товар в карточке заказа) и ищет ее в предложенном списке (в нашем случае в прайсе). После того, как формула нашла это значение в списке, она пробегает по этой строчке в прайсе и забирает оттуда нужное нам значение.

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

Искомое значение — что ищем. Мы ищем блокнот (ячейка C8)

Таблица — где ищем. Ищем в прайсе. Указываем диапазон всей таблицы прайса (внимание: именно с ячейки B4 до ячейки D16).

Номер_столбца — из какого столбца указанной выше таблицы надо брать значения. В нашем случае мы хотим «притащить» финальную цену. См.картинку ниже и ставим цифру 3.

Интервальный_просмотр — 0. Ставим 0 для получения точного результата (чтобы формула нашла конкретно «Блокнот А5», а не «блакнот а 5».

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

Пара простых шагов для финального штриха. Ставим автосумму в ячейке D15 и F15. Для упрощения действия можно запомнить следующее сочетание клавиш «ALT» и «=» (горячая клавиша для автосуммы).

В ячейке D16 считаем скидку с помощью логической формулы ЕСЛИ. Примем за правило, что если заказ собран на сумму более 5 000 руб., то скидка будет 10%.

Финальным аккордом в нашей и так уже затянувшейся песни будет простая формула

Все! С официальной частью закончили.

Небольшие советы:

1. Если Вы очистите все заполняемые ячейки в карточке заказа, то у Вас в колонках с ценой и суммой появятся значения #Н/Д (нет данных), так как непонятно какое значение искать (мы же удалили все значения с товарами в колонке C). Для того чтобы эта ошибка нас не смущала, воспользуемся специально написанной для этого командой ЕСЛИОШИБКА. Достаточно просто скопировать полученную формулу, вставить формулу ЕСЛИОШИБКА и туда вложить формулу ВПР.

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

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

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

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