Poldet1.ru

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

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

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

Мне всегда казалось странным, что Excel не поддерживает регулярные выражения (которые в кругах программистов называют регулярными выражениями или регексами). Они используются для выполнения сложной сортировки и фильтрации. Плагин SeoTools для Excel поддерживает регулярные выражения, но, как и большинство надстроек для Excel, он работает только на платформе ПК. Эта статья предназначена в первую очередь для пользователей Mac (мы не рекламируем SeoTools).

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

Немного теории

Мне всегда казалось странным, что Excel не поддерживает регулярные выражения (которые в кругах программистов называются регулярными выражениями или регексами). Они используются для выполнения сложной сортировки и фильтрации. Плагин SeoTools для Excel поддерживает регулярные выражения, но, как и большинство надстроек для Excel, он работает только на платформе ПК. Эта статья предназначена в первую очередь для пользователей Mac (мы не рекламируем SeoTools).

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

Подготовка к работе с расширенным фильтром

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

Усовершенствованный фильтр расположен :

Вы можете сортировать и фильтровать данные на компьютере, выбрав Данные > Сортировка и фильтр > Больше.

Для Mac: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр.

На ПК пользователи Mac могут щелкнуть по таблице и выбрать нужный элемент из контекстного меню. На ПК это невозможно.

Небольшой совет

Базовые операторы

В дополнение к вышеперечисленным символам подстановки, здесь приведены некоторые операторы, необходимые при работе с этим фильтром:

  • = равны, т.е. левая и правая части должны совпадать: =купить билет [включает все ячейки, содержащие билет]
  • <> не равно, левая и правая части не должны пересекаться: <> https://www.aviasales.ru/mag* [исключает этот адрес (блог)]
  • ‘ апостроф превращает формулу в текст, если поставить его первым в ячейке: ‘= купить билет
  • > больше, чем: >500
  • >= больше или равно: B4-C4>=3
  • < меньше чем: B4-C4>=3
  • < меньше чем: C6
Читайте так же:
Как проверить или найти, содержит ли ячейка определенную строку / текст / слово в Excel?

Заголовки

Набор условий

Ниже представлены три основные конструкции для нескольких условий:

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

Фильтры выбирают строки, для которых верно хотя бы одно из условий, т.е. либо Position меньше 11, либо Quantity больше 1000. Один столбец также можно сравнить с помощью оператора OR. На этот раз все критерии перечислены друг над другом в целевом столбце.

N: Если все критерии должны выполняться одновременно, используется AND.

В данном примере будут отобраны строки, в которых в столбце Ключевое слово содержится слово «купить», а не слово «самолет», и где значение параметра Item больше 7, а значение параметра Monthly Average больше 1000. Помните, что звездочка обозначает любое количество символов. В нашем примере будут выбраны строки, содержащие слово «купить» в любой позиции ячейки. Обратите внимание, что результат получен для слова «самолет», «аэроплан» подходит.

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

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

Чтобы получить текст вместо формул, просто поставьте апостроф перед любыми операторами сравнения: ‘=to buy или ‘<>plane. Microsoft на своем сайте советует слишком сложный способ, предлагая заключить все выражение в двойные кавычки и поставить перед ним знак равенства: .

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

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

Эти условия позволят выбрать ключевые слова со среднемесячным показателем от 3,700 до 10,000.

Формулы: это действительно мощный инструмент. Вместо таблицы критериев можно использовать формулы.

В примере делается то же самое, что и в параграфе Оба оператора. Только условия задаются с помощью формул.

При использовании формул следует учитывать несколько моментов:

  • Формулы должны возвращать булево значение FALSE или TRUE;
  • Заголовки формул должны отличаться от заголовков базы данных, можно использовать даже пустую строку;
  • Формулы должны ссылаться на ячейки в первой строке под заголовками базы данных;
  • Ссылки на проверяемые ячейки базы данных должны быть относительными: типа C4, а абсолютные, например, $C$4 — значение формулы будет статичным, т.е. всегда FALSE либо всегда TRUE.
Читайте так же:
Как разорвать ссылки диаграммы на исходные данные в Excel?

Общий алгоритм

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

Шаг 1: Как упоминалось выше, щелкните любую ячейку в подготовленном наборе данных и перейдите к ней:

Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр для ПК. Для Mac: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр.

Откроется меню. Здесь показан снимок экрана Excel 2013, но все пункты такие же, как и на Mac.

Шаг 2: Выберите, где разместить выбранные данные. В большинстве случаев имеет смысл определить «альтернативное местоположение» так, чтобы исходный набор данных остался неизменным. Но выбор остается за вами.

Шаг 3: Для исходного диапазона Excel по умолчанию выбирает всю «таблицу». Это можно изменить, исключив ненужные столбцы. Но Excel позволяет выбрать только прямоугольный диапазон. Чтобы выбрать столбцы с разрывами, введите имена целевых столбцов в свободные ячейки и укажите их адрес для выходного диапазона. В прилагаемых примерах дается подробное объяснение.

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

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

Шаг 6: Если вам нужны только уникальные записи, установите флажок. Большинство людей используют исключительно эту функцию.

Шаг 7: Нажмите OK, посмотрите результат.

Шаг 8: Если список был отфильтрован на месте, вы можете сбросить фильтр, нажав кнопку Очистить, но это относится к ПК. Этот элемент расположен над элементом Advanced. На компьютерах Mac есть аналог — Clear Filter, но он недоступен. Еще один аргумент в пользу сброса фильтра. Чтобы сбросить фильтр, необходимо отменить действие: нажать Command-Z или включить фильтр по умолчанию.

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

Замечания

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

Если аргумент условия относится к пустой ячейке, функция READSLYMN интерпретирует его как значение 0.

В условии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак — это один символ; звездочка — это любая последовательность символов. Если вы хотите найти вопросительный знак или звездочку, перед ними поставьте тильду (

Подсказки и сообщения об ошибках

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

Подсказка для ячейки

Вы можете настроить сообщение на второй вкладке «Контроль данных».

Параметры подсказки в диалоговом окне "Проверка данных"

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

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

Предупреждение о недопустимых данных

Существует три типа сообщений об ошибках.

Гарантирует, что в ячейку не будут введены недопустимые данные.

Сообщение Stop имеет два параметра: Повтор и Отмена.

Он предупреждает пользователей о том, что введенные данные неверны, но не мешает их вводить.

Когда появляется предупреждение, пользователь может нажать Да, чтобы принять недействительную запись, Нет, чтобы изменить введенные данные, или Отмена, чтобы удалить недействительную запись.

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

В информационном сообщении пользователь может нажать OK для принятия недопустимого значения или Cancel для отмены.

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

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

Применить множественную проверку данных в одной ячейке (пример 1)

Чтобы настроить проверку данных для ячеек, я хочу задать условия, например: если вводится число, оно должно быть меньше 100, если вводится текст, он должен быть в диапазоне от D2 до D7, как показано на следующем рисунке:

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

1. выберите ячейки, к которым вы хотите применить многократную проверку данных, затем нажмите на Data > Validate Data > Validate Data, см. снимок экрана :

2. В диалоговом окне Проверка данных на вкладке Конфигурация выполните следующие действия:

(1.) Выберите Custom в раскрывающемся списке Allow (Разрешить);

(2.) Введите эту формулу: =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1) в Формула текстовое окно.

Примечание: В приведенной выше формуле A2 — это ячейка, к которой вы хотите применить проверку фактов, C2 и D2:D7 — критерии, необходимые для применения проверки фактов.

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

Применить множественную проверку данных в одной ячейке (пример 2)

Я допускаю только текст «Kutools for Excel» или дату от 12 до 1 в определенных ячейках, как показано на скриншоте ниже. Чтобы решить эту проблему, сделайте следующее:

1. в диалоговом окне проверки данных и в диалоговом окне выполните следующие действия

(1.) Щелкните Настройки вкладка;

(2.) Выберите Для заказа из файла с выпадающим списком Разрешить;

(3.) Введите эту формулу: =OR(A2=$C$2,AND(A2>=DATE(2017,12,1), A2<=DATE(2017,12,31))) в Формула текстовое окно.

В приведенной выше формуле A2 — это ячейка, к которой вы хотите применить проверку данных, C2 — это ячейка, к которой вы хотите ее применить, 2017,12,1 и 2017,12,31 — это критерии, к которым вы хотите ее применить.

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

Применить множественную проверку данных в одной ячейке (пример 3)

В третьем примере я хочу, чтобы текстовая строка начиналась с «KTE» или «www». Если он начинается с «KTE», допускается только 6 символов; если он начинается с «www», допускается только 10 символов, см. скриншот :

Следующая формула в элементе управления данными поможет вам это сделать.

1. Откройте диалоговое окно Проверка данных и выполните следующие действия в диалоговом окне:

(1.) Щелкните Настройки вкладка;

(2.) Выберите Order (Заказ) в раскрывающемся списке Allow File (Разрешить файл);

(3.) Введите эту формулу: =OR(AND(LEFT(A2,3)=»KTE»,LEN(A2)=6),AND(LEFT(A2,3)=»www»,LEN(A2)=10)) в Формула текстовое окно.

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

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

2. Затем нажмите OK, и теперь можно вводить только те текстовые значения, которые соответствуют заданным критериям, см. скриншот:

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

Как добавить множественное форматирование к вашему тексту в одной ячейке в Excel

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

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

Давайте рассмотрим процесс подробнее.

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

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

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

Как мне поместить несколько элементов в одну ячейку в Excel?

Существует простой способ добавления нескольких элементов в ячейку в Excel. Просто нажмите Alt + Enter после ввода одного элемента и добавьте второй элемент, затем повторяйте, пока не добавите все, что есть в ячейке.

Как использовать несколько цветов шрифта или шрифты в одной ячейке в Excel?

Для этого воспользуйтесь разделом «Шрифт» вкладки Главная меню ленты. Для применения эффектов необходимо выделить определенный текст и воспользоваться специальной кнопкой в разделе «Шрифт».

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