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

Если у меня есть данные в диапазоне ячеек A1: A6 , который:

  AppleBananaCherryGrapeOrangeWatermelon  

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

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

  BananaOrangeWatermelon  

Есть ли способ сделать это?

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

  "" Banana "" "" OrangeWatermelon  

Да.

Вот формула массива (разрыв строки добавлен для удобства чтения):

  = INDEX (A1:  A6, N (IF ({1}, MODE.MULT (IF (ISNUMBER (SEARCH ("n", A1: A6)), (ROW (A1: A6) -ROW (A1) +1) * {1,1  })))))  

Обратите внимание, это формула массива, это означает, что вы должны нажать Ctrl + Shift + Enter после ввода формулы, а не просто Enter .

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

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

  = INDEX (A1: A6,2)  

Это вернет банан .

Чтобы возвращать результаты из списка, основанного на нескольких индексах, вы можете подумать использовать что-то вроде этого:

  = INDEX (A1: A6, {2; 5; 6})  

В идеале это должно вернуть {Banana; Orange; Watermelon} .

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

  = INDEX (A1: A6, N (IF ({1}, {2  ; 5; 6})))  

Это вернет желаемый результат {Banana; Orange; Watermelon} .

Я считаю это частью 1 объяснения.

Часть 2 объяснения заключается в том, как следующее возвращает {2; 5; 6} :

  = MODE.MULT (IF (ISNUMBER (SEARCH ("n", A1: A6)), (ROW (A1: A6) -ROW (A1  ) +1) * {1,1}))  

MODE.MULT – это функция, которая возвращает данные в наборе, который появляется чаще всего. часто. Однако есть несколько предостережений:

1. Данные должны отображаться как минимум дважды, чтобы их возвращал MODE.MULT . Если нет повторяющихся данных, он вернет ошибку. Например, MODE.MULT ({1; 2; 3}) вернет ошибку, поскольку в массиве нет повторяющихся данных {1; 2; 3} код>. Другой пример: MODE.MULT ({1; 1; 2} вернет 1 , потому что 1 чаще всего встречается в данные.

2. Если есть «ничья» в отношении того, какие данные появляются чаще всего, MODE.MULT возвращает массив все завязанные записи. Например, MODE.MULT ({1; 1; 2; 2}) вернет массив из {1; 2} .

3. Самое главное и, вероятно, наиболее своеобразное, но также самое полезное поведение MODE.MULT , MODE.MULT полностью игнорирует логические значения (значения TRUE и FALSE ) при определении режима данных, , даже если они появляются чаще, чем нелогические значения в данных.

Мы можем использовать эти свойства MODE.MULT , чтобы получить желаемый массив.

ISNUMBER (SEARCH ("n", A1: A6)) возвращает массив ИСТИНА/ЛОЖЬ значения, где данные содержат n . Примерно так:

  FALSETRUEFALSEFALSETRUETRUE  

(ROW (A1: A6) -ROW (A1) + 1) возвращает массив, начинающийся с 1 и увеличивающийся на 1 до размера исходного массива:

  123456  

(ROW (A1: A6) -ROW (A1) +1) * {1,1} фактически просто копирует этот столбец:

  1 12 23 34 45 56 6  

Оператор IF используется для возврата числа в массив выше, если TRUE , и FALSE в противном случае. (Поскольку инструкция IF не содержит предложения else, FALSE является значением по умолчанию.)

В этом примере , инструкция IF вернет следующее:

  FALSE FALSE2 2FALSE FALSEFALSE FALSE5 56 6  

Принятие MODE.MULT приведенной выше формулы вернет {2; 5; 6} , потому что, как уже упоминалось, MODE.MULT удобно игнорирует значения FALSE в приведенном выше массиве при рассмотрении режима.

Необходимо учитывать указанный выше массив внутри MODE. MULT вместо простого:

  FALSE2FALSEFALSE56  

Поскольку, как упоминалось ранее, MODE.MULT требует, чтобы как минимум две записи в данных совпадали, чтобы они возвращали значение.

  = INDEX ($ A $ 2: $ A $ 7, MAT  CH (1, (COUNTIF ($ C $ 1: C1, $ A $ 2: $ A $ 7) = 0) * (FIND ("n", $ A $ 2: $ A $ 7)> 0), 0))  

  = ИНДЕКС ($ A $ 2: $ A $ 7, ПОИСКПОЗ (1, (СЧЁТЕСЛИ ($ C $ 1: C1, $ A $ 2: $ A $ 7) = 0)  * (НАЙТИ ("n", $ A $ 2: $ A $ 7)> 0), 0))  

Это формула массива, поэтому ее нужно вводить с помощью Ctrl + Shift + Enter

Ввод и вывод

• INDEX возвращает элемент из диапазона
• MATCH позволяет нам найти позицию строки (строк) для возврата
• COUNTIF предназначен для того, чтобы мы выбирали только уникальные значения в результатах.
• FIND возвращает только строки где в строке присутствует n

2

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

  Открытая функция ContainsN (rng As Range) As String Dim r As Range  СодержитN = "" для каждого r In  rng Если InStr (1, UCase (r.Value), "N")> 0 Then ContainsN = ContainsN & Chr (10) & r.Value Next r ContainsN = Mid (ContainsN, 2) End Function  

Улучшите этот ответ

ответил 20 ноября ’17 в 18:48

добавить комментарий |

Попробуйте следующую пользовательскую функцию:

  Открытая функция ContainsN (rng As Range  ) As String Dim r As Range ContainsN = "" Для каждого r In rng Если InStr (1, UCase (r.Value), "N")> 0 Then ContainsN = ContainsN & Chr (10) & r. Значение Далее r ContainsN = Mid (ContainsN, 2) End Function  

Excel: формулы массива в условном форматировании

Технологии |

Автор Билл Джелен

5 комментариев

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

УСЛОВНОЕ ФОРМАТИРОВАНИЕ

Ячейки B3: E12 на Рисунке 1 показывают квартальные продажи для нескольких производственных линий. Используйте условное форматирование, чтобы привлечь внимание к кварталу в каждой строке, в котором были наибольшие продажи по сравнению с другими кварталами. Вам нужно построить формулу для ячейки верхнего левого угла диапазона. Для ячейки B3 вы хотите, чтобы ячейка выделялась, когда она равна наибольшему значению в B3: E3. В правиле условного форматирования это можно представить как = B3 = MAX ($ B3: $ E3). Обратите внимание, что ссылка внутри функции MAX представляет собой смесь относительных и абсолютных ссылок. Столбцы всегда будут от B до E, но строка может изменяться.

Создав формулу, которая будет работать, добавьте условное форматирование для всех данных:

1. Выберите B3: E12.
2. Выберите “Домашняя страница”, “Условное форматирование”, “Новое правило”.
3. В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу, чтобы определить, какие ячейки следует форматировать».
4. Type = B3 = MAX ($ B3: $ E3) в диалоговом окне.
5. Нажмите кнопку «Форматировать…».
6. В диалоговом окне «Формат ячеек» нажмите вкладку «Заливка» и выберите цвет заливки.
7. Нажмите «ОК», чтобы закройте диалоговое окно “Форматирование ячеек”.
8. Нажмите “ОК”, чтобы закрыть новое правило форматирования.

Вы должны увидеть наибольшее значение в каждой строке изменить цвет. В случае строки 8, где B8 и E8 равны, обе будут отформатированы.

ОСЛОЖНЕНИЯ В РЕАЛЬНОЙ ЖИЗНИ

Аналогичным образом вы можете выделить наименьшее значение в каждой строке, изменив формулу на шаге 4 на = B3 = MIN ($ B3: $ E3). Но после использования этой техники вы понимаете, что есть кварталы, в которых определенные продукты не выставляются на продажу, и показатели продаж за нулевой квартал выделяются (см. Рисунок 2). Ваш менеджер хочет, чтобы вы игнорировали нулевые значения. Ваша цель – выделить самую маленькую ячейку в каждой строке, значение которой больше 0.

Если вы используете Office 365 и загрузили обновление Excel за февраль 2016 г., вы можете легко решить эту проблему с помощью новой функции MINIFS. Формула на шаге 4 будет = B3 = MINIFS ($ B3: $ E3, $ B3: $ E3, «> 0»). Но вы, вероятно, встретите много других пользователей, у которых нет последней версии Excel, и формула перестанет работать.

ИСПОЛЬЗУЙТЕ ФОРМУЛЫ МАССИВОВ

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

Вы можете начать строить эту формулу массива изнутри. Рассмотрим фрагмент формулы IF ($ B3: $ E3> 0, $ B3: $ E3, «Игнорировать»). В нем говорится: «Если B3: E3 больше нуля, используйте число из B3: E3; в противном случае используйте значение Игнорировать ». Вы можете использовать любой текст вместо «Игнорировать» – даже «» подойдет. В строке 5 этот фрагмент формулы даст ответы «Игнорировать», «Игнорировать», 87, 30.

Функции MIN и MAX предназначены для вычисления чисел в диапазон и игнорировать любые текстовые значения. Если вы спросите MIN («Игнорировать», «Игнорировать», 87,30), ответ будет 30. Таким образом, следующий шаг в вашей формуле – взять MIN фрагмента формулы: MIN (IF ($ B3: $ E3> 0, $ B3: $ E3, «»)).

Последний шаг – проверить, равно ли значение, возвращаемое MIN, равно значение в B3. В диалоговом окне условного форматирования введите тестовую формулу = B3 = MIN (IF ($ B3: $ E3> 0, $ B3: $ E3, «»)).

Поскольку эти формулы массива используются так редко, я не ожидал, что условное форматирование будет оценивать формулу массива. Но если вы выполните пронумерованные шаги и воспользуетесь этой формулой на шаге 4, она правильно выделит наименьшее значение в каждой строке, игнорируя нулевые значения. На рисунке 3 формула массива успешно выделяет 30 в строке 5 вместо нулей в B5 и C5.

ПОЛЕЗНЫЕ СОВЕТЫ

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

SF ГОВОРИТ

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

5 + Показать комментарии

Отменить ответ

5 комментариев

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

Дэйв – хорошая идея. Брэд – Я не мог заставить работать ни одну из них. Однако, если у вас есть последние сборки Office 365, новая функция MINIFS () может работать.

спасибо за полезные советы. нельзя сделать то же самое с AND? = и (b3: e3> 0, min (b3: e3)) или, может быть, это будет = (B3: E3> 0 * MIN (B3: E3))

Спасибо, счет, Или можно просто использовать «найти и заменить», чтобы заменить все нули пробелами перед нахождением минимумов

МОЖНО ЗАИНТЕРЕСОВАТЬСЯ ПРОЧИТАТЬ ДАННУЮ СТАТЬЮ 🙂

Вам также может понравиться

Excel: сводная таблица с данными из разных листов

EXCEL: ЗАМЕНА Вложенных заявлений IF на ВПР

Excel: диаграммы водопада денежных потоков в Excel 2016

Excel: история акций и обменных курсов