Ref-sgain.ru

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

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

Подстановочные знаки Excel — что это такое и как лучше всего их использовать

Подстановочные знаки Excel - почему вы их не используете?

В Excel есть только 3 знака подстановки (звездочка, вопросительный знак и тильда), но с ними можно сделать многое.

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

Подстановочные знаки Excel — Введение

Подстановочные знаки — это специальные символы, которые могут заменить любой символ (отсюда и название — подстановочный знак).

В Excel есть три типа подстановочных знаков:

  1. * (звездочка) — представляет собой любое количество символов. Например, Ex * может означать Excel, Excel, Пример, Эксперт и т.д. EX. EXCEL * (ЗВЕЗДОЧКА) — ПРЕДСТАВЛЯЕТ СОБОЙ ЛЮБОЕ КОЛИЧЕСТВО СИМВОЛОВ. (вопросительный знак) — представляет собой один символ. Например: Tr? Мп может означать Трамп или Трамп.

(тильда) — Он используется для обозначения подстановочного знака (

, *, ?) в тексте. Например, допустим, вы хотите найти точную фразу Excel * в списке. Если вы используете Excel * в качестве строки поиска, вы получите любое слово, начинающееся с Excel, за которым следует любое количество символов (например, Excel, Excels, Excels, Excellent). Чтобы искать конкретно по excel *, нам нужно использовать

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

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

. Тем не менее, это полезная функция.

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

Подстановочные знаки Excel — примеры

Давайте рассмотрим четыре практических примера, в которых заполнители в Excel могут быть очень полезны:

  1. Фильтрация данных с помощью подстановочных знаков.
  2. Частичный поиск с использованием подстановочных знаков и VPR.
  3. Поиск и замена частичных совпадений.
  4. Подсчитайте непустые ячейки, содержащие текст.

# 1 Фильтрация данных с использованием подстановочных знаков Excel

Подстановочные знаки в Excel удобны, когда у вас большие наборы данных и вы хотите отфильтровать данные на основе какого-либо условия.

Предположим, у вас есть набор данных, подобный приведенному ниже:

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

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

  • Выделите ячейки, которые хотите отфильтровать.
  • Перейдите в Данные -> Сортировка и фильтр -> Фильтр. (Сочетание клавиш — Control + Shift + L).
  • Щелкните значок фильтра в ячейке заголовка.
  • В поле (под параметром «Текстовый фильтр») введите А *
  • Щелкните ОК.

При использовании этого метода результаты будут сразу отфильтрованы, и вы получите три компании на выбор — ABC Ltd., Amazon.com и Apple Store.

Как это работает? Звездочки (*) фильтруют в Excel любое значение, начинающееся с буквы A. Это происходит потому, что звездочка (подстановочный знак Excel) может представлять любое количество символов.

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

Если вы хотите отфильтровать компании, которые начинаются с алфавита A и заканчиваются алфавитом C, используйте строку A * C. Это вернет только два результата: ABC Ltd. и Amazon.com.

Если вы используете A ? C, вы получите в результате только ABC Ltd (так как между «a» и «c» допускается только один символ).

# 2 Частичный поиск с использованием подстановочных знаков и ВПР

Частичный поиск необходим, когда нужно найти значение в списке и нет точного совпадения.

Например, предположим, что у вас есть набор данных ниже, и вы хотите найти в списке компанию ABC, но в списке вместо ABC содержится ABC Ltd.

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

Если вы используете VLOOKUP с приблизительным совпадением, вы получите неправильные результаты.

Однако можно использовать подстановочный знак в функции UPP для получения правильных результатов:

Добавьте приведенную ниже формулу в ячейку D2 и перетащите ее на другие ячейки:

Как работает эта формула?

В приведенной выше формуле вместо того, чтобы использовать значение как есть, вы заключаете обе стороны подстановочного знака Excel в звездочку (*) — «*» & C2 & «*».

Это указывает Excel искать любой текст, содержащий слово в C2. Он может содержать любое количество символов до или после текста в C2.

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

3. Найдите и замените частичные совпадения

Подстановочные знаки в Excel можно использовать разными способами.

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

Вот некоторые данные, которые могут у вас быть:

В приведенных выше данных регион вводится по-разному (например, Северо-Запад, северо-запад, северо-запад).

Это часто происходит с данными о продажах.

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

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

  • Выберите данные, в которых вы хотите найти и заменить текст.
  • На главную -> Найти и выбрать -> Перейти. Откроется диалоговое окно «Найти и заменить». (Вы также можете использовать сочетание клавиш — Control + H).
  • Введите следующий текст в диалоговом окне поиска и замены:
    • Найти то, что: Север * З *
    • Заменить: Северо-Запад

    Это мгновенно изменит все форматы и сделает их совместимыми с Северо-западом.

    Как это происходит?

    В поле «Найти» мы использовали Sever * Z *, который найдет любой текст, в котором есть слово North, за которым следует буква «W».

    Поэтому он охватывает все сценарии (северо-западный, северо-западный и северо-восточный).

    Функция «Найти и заменить» находит все эти экземпляры, изменяет их на северо-западные и делает их последовательными.

    4. Подсчитайте непустые ячейки, содержащие текст.

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

    Вы совершенно правы!

    Это можно сделать с помощью функции SCREENING (Просмотр).

    Но… с этим есть небольшая проблема.

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

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

    Проблема в том, что функция COUNTA не рассматривает ее как пустую ячейку (она рассматривает ее как текст).

    В примере выше я использую функцию COUNTA для поиска непустых ячеек, и она возвращает 11, а не 10 (но вы можете ясно видеть, что только 10 ячеек содержит текст).

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

    Но так работает Excel.

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

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

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

    В ? * комбо :

    • ? (вопросительный знак) указывает на наличие хотя бы одного символа.
    • * (звездочка) позволяет добавить любое количество дополнительных символов.

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

    Аналогичным образом можно использовать подстановочные знаки во многих других функциях Excel, таких как IF(), SUMIF(), AVERAGEIF(), MATCH().

    Кроме того, вы должны знать, что, хотя вы можете использовать подстановочные знаки в функции ПОИСК, они не могут использоваться в функции ПОИСК.

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

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

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

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

    выпадающий список в excel

    Составление исходных списков

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

    • Картофель, свекла, морковь, редис;
    • Петрушка, укроп, щавель, шпинат;
    • Клубника, вишня, сморчки, смородина.

    Первые четыре слова заносим в столбец, начиная с A1, вторые – с B1, третьи – с C1.

    Чтобы перейти непосредственно к вопросу о том, как создать выпадающий список в Excel, давайте назовем эти списки. Для этого выделите значения в первом столбце, выберите пункт меню: «Формулы — Менеджер имен — Присвоить имя». В появившейся форме введите «Овощи» в строке «Название» и нажмите «ОК». Повторите то же самое для других списков, назвав их «Зелень» и «Ягоды». Строка «Площадь» оставлена как «Книга». Это позволяет обращаться к этим спискам с других листов книги.

    создание выпадающего списка в excel

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

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

    Теперь собственно формируем выпадающий список. Выбираем, например, ячейку D1 на другом листе открытой книги, открываем пункт меню: «Данные» – «Проверка данных». В открывшейся форме «Проверка вводимых значений» во вкладке «Параметры» в поле «Тип данных» выбираем «Список». Ставим курсор в поле «Источник», набираем знак равенства и нажимаем клавишу F3. В появившемся меню «Вставка имени» выбираем имя нужного нам списка, например, «овощи», затем нажимаем «ОК». Проверяем, чтобы стояли галочки в позициях «Игнорировать пустые ячейки» и «Список допустимых значений». Это обеспечивает возможность не заполнять это поле данными и вводить только данные из списка с указанным именем. Жмем «ОК».

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

    как сделать выпадающий список в excel с другого листа

    При заполнении поля «Источник» формы «Проверка значений ввода» доступны еще две опции.

    1. Помещение курсора в это поле выделяет в книге область с действительными значениями списка на данном листе. Формула появится в поле после знака равенства. Как создать выпадающий список в Excel из другого рабочего листа в этом случае? Выберите тот же диапазон ячеек в этом рабочем листе, затем, установив курсор в поле после знака равенства, введите имя рабочего листа со списком и поставьте восклицательный знак. Примечание: Имя рабочего листа не должно содержать пробелов!
    2. Введите правильные значения в поле, разделяя их точкой с запятой. Это самый простой способ, который не позволяет ссылаться на списки на другом листе в книге.

    Расширение диапазона допустимых значений

     создание выпадающего списка в excel

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

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

    Таким образом, мы получаем выпадающий список в Excel.

    выпадающий список в excel

    Двойная ссылка

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

    Теперь выберите (вы также можете сделать это на другом листе в этой книге), например, ячейку G1 и введите выпадающий список с именем «Список 1». Следующая ячейка, например, H1, будет заполнена значением, выбранным в ячейке G1. Для этого создайте выпадающий список в ячейке H1, как было указано ранее, выберите ячейку G1 в поле «Источник», но уточните запись в этом поле, добавив DVSSYL после знака равенства и заключив остальную часть формулы в круглые скобки: =DVSSYL($G$1).

    Выбор значения «зеленый» в ячейке G1, например, заполнит ячейку H1 доступными опциями из списка.

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

    Диапазон ячеек Excel

    Начнем с самого простого: дайте ячейке имя. Просто выберите его (1) и введите в поле Имя (2) вместо адреса ячейки имя, которое легко запомнить.

    Название ящика C 7

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

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

    Использование имени ячейки

    Ячейки, строки, столбцы

    Начнем с выбора ячеек, строк и колонок.

      Чтобы выбрать ячейку C3, нажмите на поле на пересечении столбца C и строки 3.

    Примеры диапазона

    Интервал — это набор из двух или более ячеек.

      Чтобы выделить диапазон B2:C4, щелкните в правом нижнем углу ячейки B2 и перетащите указатель мыши в ячейку C4.

    Заполнение диапазона

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

      В ячейку B2 введите значение 2.

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

    Именованный диапазон

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

    Создать доменное имя

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

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

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

    Создание именованной области с использованием панели инструментов

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

    Создание имени через диалоговое окно

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

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

    Управление диапазоном имен

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

    Задача

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

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

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

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

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

    Чтобы создать динамический вентилятор :

    • На вкладке Формулы в группе Определенные имена выберите Назначить имя;
    • В поле Имя введите: Месяц;
    • В поле Домен выберите Лист регистрации;
    • В поле Диапазон введите формулу =MEMBERSY(sheet1!$B$5;;;;1;COUNT(sheet1!$B$5:$I$5)).
    • Нажмите OK.

    Теперь подробнее. Верхняя левая и нижняя правая ячейки определяют каждый диапазон в Excel. Наша динамическая позиция диапазона вычисляется из ячейки B5. Эта ячейка является верхней левой ячейкой диапазона, если аргументы offset_by_rows и offset_by_columns не указаны (как в нашем случае). Задаются высота и ширина правой нижней ячейки диапазона. В нашем случае значение высоты =1, а значение ширины диапазона равно результату вычисления формулы SCHOTZ(sheet1!$B$5:$I$5), который равен 4 (строка 5 содержит 4 месяца с января по апрель). Следовательно, адрес правой нижней ячейки нашего динамического диапазона — E 5.

    При заполнении таблицы данными о продажах за май, июнь и т.д. формула READ(sheet1!$B$5:$I$5) возвращает количество заполненных ячеек (количество названий месяцев) и таким образом определяет новую ширину динамического диапазона, позволяя создать раскрывающийся список.

    ПРИМЕЧАНИЕ: При использовании функции SCRETZ() убедитесь, что нет пустых ячеек. То есть, вы должны заполнить список месяцами без пробелов.

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

    Чтобы создать динамичную область:

    • На вкладке Формулы в группе Определенные имена выберите Назначить имя;
    • В поле Имя введите: Продажи_в_месяц;
    • В поле Диапазон введите формулу = AMOUNT(worksheet1!$A$6;;SCHEDULE(worksheet1!$C$1;worksheet1!$B$5:$I$5;0);12).
    • Нажмите OK.

    Функция ПОИСКПОЗ() ищет в строке 5 (список месяцев) месяц, выбранный пользователем (ячейка C1 с выпадающим списком), и возвращает соответствующий номер элемента из диапазона поиска (названия месяцев должны быть уникальными, т.е. этот пример не подходит для нескольких лет). Верхний левый угол нашего динамического диапазона (начиная с ячейки A6) перемещается на это количество столбцов, но его высота остается фиксированной — 12 (при желании вы можете сделать ее динамической, в зависимости от количества элементов в диапазоне).

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

    Например, в мае месяце.

    Или, к примеру, в апреле.

    Примечание: Вместо формулы SMUM() для расчета количества прошедших месяцев можно использовать формулу INDEX(): = $B$5:INDEX(B5:I5;AMOUNT($B$5:$I$5))

    Формула подсчитывает количество элементов в строке 5 (SCRUTZ() ) и определяет ссылку на последний элемент в строке (INDEX() ) и возвращает ссылку на интервал B5:E5 .

    Визуальное отображение динамического диапазона

    Текущий динамический диапазон можно выделить с помощью условного форматирования. В файле примера правило условного форматирования применяется к ячейкам в диапазоне B6:I14 с помощью формулы: = столбец(B6)= столбец(Продажи_в_месяц)

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

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

    Пример 2. Необходимо ввести количество столбцов в таблице A16.

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

    Определение принадлежности ячейки к диапазону таблицы

    Пример 3: Определяет, принадлежит ли данная ячейка заданному диапазону клеток.

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

      1. Введите часть формулы «=WORLD(()» в любую ячейку и выберите диапазон ячеек для завершения аргументов:
        1. Поместите пробел и выберите любую ячейку в этом диапазоне:
          1. Закройте обе скобки и нажмите Enter. Результат будет следующим:
            1. Если вы выберете ячейку в диапазоне, отличном от заданного, вы получите ошибку #пусто!

            Эта ошибка означает, что ячейка не принадлежит выбранному диапазону.

            Если в диапазоне выбрано более одной ячейки, функция ПЕРЕМЕННАЯ вернет количество выбранных ячеек:

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

            Перемещение и копирование ячеек и их содержимого

            См. также = IF(ANSWER(A2);A2;B2) вы соответствующим образом копируете макрос «Фильтр». Это будет работать только в таблице: Я думаю, что это возможно, если столбец. Т.е. получается, что ВСЕ», затем выполняете и нажимаете Вставить более сложная процедура, нажимаете значок Вставить на следующих шагах. При вставке можно выбрать временное отображение данных, выбранный раздел на Примечание: Вставка значений в качестве выходных значений для таблицы на нужно?

            Затем нажмите Ctrl+Paste, чтобы скопировать диапазон, выделенный в Шаге 1. Чтобы переместить ячейки, щелкните мышью. Вставляйте параметры, которые не требуют другого рабочего листа или Попробуйте как можно чаще

            голоса
            Рейтинг статьи
            Читайте так же:
            Как скопировать лист и автоматически переименовать в Excel?
Ссылка на основную публикацию
Adblock
detector