Формула Excel для возврата значения из диапазона, в котором условные операторы верны

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

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

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

  = IF (AND ('Stata Output'! D2: D6 = 'Backcheck schedule and results'! C2, 'Stata Output'! C2: C6 = 1), 1,0  )  

Однако замена 1 в формуле диапазоном ячеек, из которых я хочу извлечь значение, каждый раз дает мне ноль, что, как я знаю, неверно. Для этой последней формулы я использую:

  = IF (AND ('Stata Output'! D2: D6 = 'Backcheck schedule and results'! C2, 'Stata Output'  ! C2: C6 = 1), 'Stata Output'! Q2: Q5,0)  

Есть идеи, где я ошибаюсь? Спасибо!


1

Попробуйте — я уверен, что есть более простой способ, но он, похоже, выполняет свою работу (обязательно введите формулу массива с помощью Control + Shift + Enter ):

  =  ИНДЕКС ('Вывод статистики'! $ D $ 2: $ Q $ 6, ПОИСКПОЗ (1, (- ('Вывод статистики'! $ C $ 2: $ C $ 6 = 1) * - ('Вывод статистики'! $ D $ 2  : $ D $ 6 = "Расписание и результаты обратного контроля")), 0), 14)  

При этом вся ваша таблица берется в качестве диапазона (первый аргумент INDEX , затем с помощью MATCH находит строку, в которой выполняются оба ваших условия (в этом случае столбец C должен быть 1 и соответствующее значение в столбце D должно быть Расписание и результаты обратной проверки ). Затем вы ищете в полученном массиве 1 (что указывает на совпадение) и возвращаете столбец 14 (который представляет столбец Q в нашей таблице). При необходимости вы можете продолжать добавлять фильтры внутри функции MATCH () .

Обратите внимание, что это будет соответствовать только первому ‘попаданию’.

Улучшите этот ответ
отредактировано 10 окт., 14:41
ответил 10 октября ’12 в 14:33
добавить комментарий |

Попробуйте — я уверен, что есть более простой способ, но он, похоже, выполняет свою работу (обязательно введите как формулу массива с Control + Shift + Enter ):

  = INDEX ('Stata Output'! $ D $ 2: $ Q $ 6,  MATCH (1, (- ('Вывод статистики'! $ C $ 2: $ C $ 6 = 1) * - ('Вывод статистики'! $ D $ 2: $ D $ 6 = «Расписание и результаты обратного контроля»)), 0  ), 14)  

При этом вся таблица берется в качестве диапазона (первый аргумент для INDEX , затем используется MATCH , чтобы найти строку, в которой выполняются оба ваших условия (в этом случае столбец C должен быть 1 , а соответствующее значение в столбце D должен быть Расписание и результаты обратной проверки ). Затем вы выполняете поиск в результирующем массиве для 1 (что указывает на совпадение) и столбца возврата 14 (который представляет столбец Q в нашей таблице). Вы можете продолжать добавлять фильтры внутри функции MATCH () по мере необходимости.

Обратите внимание, что это s будет соответствовать только первому ‘попаданию’.


0

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

  = IF (('Stata  Вывод '! D2: D6 =' График и результаты обратного контроля '! C2) * (' Вывод статистики '! C2: C6 = 1) = 1,' Вывод статистики '! Q2: Q5,0)  

Это даст вам новый столбец результатов, который будет заполнен из столбца Q2: Q6 для каждой совпадающей строки.

Чтобы использовать вы должны выбрать весь столбец результатов, ввести формулу и затем использовать CTRL + SHIFT + ENTER .

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

Улучшите этот ответ
отредактировал 10 октября 2012 в 15:46
ответил 10 октября ’12 в 15:14
  • Да, вы правы насчет И, результатом И является единственный результат, а не массив ….. но проблема с вашей исправленной версией заключается в том, что она создает «массив «, например. если строка 3 совпадает, а остальные — нет, тогда вы получите {0; text; 0; 0; 0}, где текст — это значение в Q3 ….. так что вам понадобится другая функция для извлечения этого значения из array (см. мой ответ и RocketDonkey) — если вы введете в одну ячейку, вы обычно увидите только первое значение из этого массива (0), но оно может измениться в зависимости от того, где вы вводите формулу! — Барри Гудини, 10 окт., 12:25
  • @barryhoudini, да, он дает массив результатов, и эту формулу следует применять к диапазону ячеек. Думаю, я понял вопрос иначе, что он хочет получить результаты для всех совпадающих строк, а не только для одной из них. — Майкл 10 окт., 12:44
добавить комментарий |

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

  = IF (('Stata Output'! D2: D6 = 'Backcheck schedule and results'! C2) * ('Stata Output'! C2: C6 = 1) = 1  , 'Stata Output'! Q2: Q5,0)  

Это даст вам новый столбец результатов, который будет заполнен из Q2: Q6 столбец для каждой совпадающей строки.

Чтобы использовать его, вы должны выделить весь столбец результатов, ввести формулу и затем использовать CTRL + SHIFT + ENTER .

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


0

Попробуйте следующую формулу:

  = LOOKUP (2,1/('Stata Output'! D2: D6  = 'Расписание и результаты обратного контроля'! C2)/('Вывод статистики'! C2: C6 = 1), 'Вывод статистики'! Q2: Q5)  

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

Улучшите этот ответ
отредактировано 24 сентября ’17 в 17:25
Jose R
41611 золотых знаков88 серебряных знаков2222 бронзовых знака
ответил 10 окт. в 14:33
добавить комментарий |

Попробуйте следующую формулу:

  = LOOKUP (2,1/('  Stata Output '! D2: D6 =' Backcheck schedule and results '! C2)/(' Stata Output '! C2: C6 = 1),' Stata Output '! Q2: Q5)  

Если существует более одной строки, где оба условия удовлетворяются, эта формула вернет значение col Q из последней из этих.



Условное форматирование на основе другого значения ячейки в Google Таблицах

Уважаемый сэр,

Я хочу отметить формулу «меньше или больше» в одном столбце данных. Например, я хочу отметить в столбце данные, которые больше 0 и меньше 95.

Посоветуйте, пожалуйста, формулу.


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


Привет,

Я пытаюсь выделить строки (диапазон A3: F102) желтым цветом, если диапазон H1: H102 содержит «Settled».
какую формулу мне использовать?

Спасибо.


Я могу рассказать вам, как выделить ячейку, если другая ячейка в той же строке содержит слово «поселился», но теперь уверен как выделить целый ряд. Напишите мне, если хотите получить объяснение 🙂


= A1> B1


Условное форматирование диапазона ячеек на основе значения в другом диапазоне.
Я застрял в этом, и мне нужна помощь.
У меня есть таблица 10X10 (B2: K11), в которой хранится таблица умножения. Я хочу, чтобы учащийся ввел значение в ячейку, которая должна стать зеленой, если значение правильное. И станет красным, если неверно.
Я сохранил правильные значения в другой таблице (M2: V11) того же размера, чтобы использовать их как ссылку на правильные значения.
Я хочу использовать ‘значение не равно ‘и использовал формулу.
Какую формулу мне использовать?


У меня есть форма Google, в которой пользователи выбирают сегодняшнюю дату. Я хочу выделить те строки, где две даты разные. Как мне сравнить эту запись с меткой времени?


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


привет! Работает ли это, если A — это объединенные ячейки?


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


Вы используете проверку данных для раскрывающихся списков, верно? Используете ли вы параметр условного форматирования «текст точно _____» с текстом из раскрывающегося списка в качестве условия?

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

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


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


ЕСЛИ A1 больше или равно B1, то раскрасьте его

, что будет быть формулой, пожалуйста, помогите….


У меня такая же проблема, может кто-нибудь посоветовать? Я хочу, чтобы ячейка в строке 2 столбца I выделяла красный или зеленый цвет в зависимости от значения ячейки в строке 2 столбца H, и мне нужна эта формула для каждой ячейки в столбцах. Есть идеи?


Как мне создать фиксированный диапазон в условном форматировании? Я хочу, чтобы диапазон всегда был F3: F22, но я всегда буду добавлять строку над строкой 3, которая изменяет диапазон на F3: F23. Я не хочу этого. Я хочу, чтобы он всегда оставался F3: F22. $$ не помогают. Я пробовал использовать OFFSET и INDIRECT в поле диапазона, но ни одно из них не разрешено.

Спасибо.

Дэн


Это очень полезно, спасибо! Но я хотел бы расширить его, если бы мы могли …

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

Пример: поверните ячейки в столбце A в зеленый цвет, если какие-либо ячейки в столбце B содержат слово «ожидающие».

Заранее благодарим за вашу помощь!


В этом случае я бы использовал функцию «ПОИСК». Например, я документировал подробную информацию о группе песен в моей базе данных. При условном форматировании у меня был диапазон A1: I: 500, настраиваемая формула и формула = ПОИСК («LINE DANCE», $ I2)> 0. Это произвело поиск в полях столбца I для текста «танец линии» (ПОИСК не чувствителен к регистру). Всякий раз, когда ячейка в столбце I содержала «танец линии», выделялась вся строка от A до I. Надеюсь, это поможет!


У меня это сработало !! Если в столбце «J» указано «Просрочено», то я хочу, чтобы он выделил столбец «J» и столбец «K», в которых была дата. Я выделил оба столбца, щелкнул «Условное форматирование», «Новое правило», «Использовать формулу, чтобы определить, какие ячейки нужно форматировать» и ввел формулу = ПОИСК («Истекло», $ J1)> 0. После этого я отформатировал оба столбца, чтобы они были выделены зеленым, и это сработало 😀 Спасибо !!


Я тестирую условную формулу, используя соглашение об абсолютных значениях Excel. Но когда я копирую и вставляю условную формулу = Left ($ c5,9) = «Duplicate» в строку 7, формула не меняет 5 на 7, как в Excel.. Он сохраняет его на C5, хотя символ $ предназначен только для столбца. В Excel номер строки по-прежнему будет относиться к строке, в которую она копируется. = Left ($ c7,9) = ”Duplicate”.
Мне не кажется, что это полная копия Excel соглашение.


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


Мое значение в E2 будет меняться еженедельно. Мне нужно применить правила условного форматирования, которые выделяют все в столбце E (начиная с E3 и ниже) на основе диапазона от значения E2 + .001 до .499 — как мне ввести это в условное форматирование?


Я могу разместить правило на том же листе. Но я хочу, чтобы ячейка меняла цвет на листе 2 в зависимости от результата на листе 1. Это простой ответ «да, нет» на первом листе. Ячейка на листе 2 будет отображаться зеленым, если да.
Правило формата условия, которое я могу работать на той же странице, —

= B4 = ”Да”

Я пробовал
= Sheet! 1B4 = ”Yes”
Выдает неверное сообщение. Можно ли это сделать на разных страницах, или мне нужно будет разработать какие-то коды IF или что-то в этом роде


= ($ E $ 9 = ”Y”)

Работал на моем. Чтобы понять это, потребовалась минута. Не уверен, что он у вас есть, но я использовал его. Мне также нужно было отмечать вещи «да» или «нет».


у меня не работает`


Сработало, когда я used = ($ K3 = ”Y”), просто нужно было повторно ввести кавычки (”и“) в моей формуле.


Сработало для меня, когда я использовал = (K $ 3 = ”Y”)
Чтобы применить его только к столбцу.


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


Использовать косвенное. Итак, вместо = ’sheet1 ′! A1 = 1, используйте = косвенный (« ‘sheet1’! A1 ») = 1


Я понял это в Google Таблицах! Используйте условное форматирование для ОДНОЙ ЯЧЕЙКИ (тогда вы собираетесь взять маленький знак +, чтобы скопировать формулу во все остальные после запуска первой). Предположим, что в следующем примере вы хотите, чтобы ячейка B была выделена зеленым цветом, если она БОЛЬШЕ, чем A, и красным, если она меньше.

Пример: AB
1: 5 10
2: 5 1
У вас будет 2 формулы для B1.
Первая : условное форматирование, настраиваемая формула из раскрывающегося списка, затем поле ниже = (B1> A1). Цвет: зеленый
Второй: условное форматирование, настраиваемая формула из раскрывающегося списка, затем поле ниже = (B1 Цвет: красный
Теперь наведите курсор на B1, пока не увидите см. маленький знак + в маленьком правом углу… .. перетащите его полностью вниз ко всем B-ячейкам. Вуаля!


Согласен. Я тоже пытаюсь получить МНОГО данных, отформатированных для финансов. Если ячейка A имеет значение … все, что я хочу, это когда значение в B ниже, чем в A, я хочу, чтобы оно было красным. Когда оно больше числа в A, я хочу, чтобы он был зеленым. Будет очень утомительно, если мне придется перейти к КАЖДОЙ ячейке B и сообщить ей КОНКРЕТНОЕ число из ячейки A, плюс моя ячейка A сильно изменится!
Пример: AB
1: 5 10
2: 5 1
Я хочу, чтобы B1 был зеленым, а B2 был красным.


Как это работает, если вы пытаетесь сравнить значения из 2 ячеек и определить по цвету, одна из которых больше или меньше другой. Например, у вас есть ячейка A1: A9, содержащая целочисленные значения, и ячейка B1: B9, содержащая целочисленные значения, и вы хотите сравнить каждое значение для каждого столбца (A1) и (B1) и цвет A1 желтый, если он меньше B1, и красный A1 меньше B1. Затем сделайте то же самое для каждой строки.


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


HI,

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


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


Вы должны использовать $ для ссылки на конкретную ячейку, то есть
Диапазон A1: C200
Форматировать ячейку зеленым, если
Больше
= $ L $ 2
Ячейка L2 читает 5%
Результат: все ячейки в диапазоне A1: C200 Больше 5% выделяются зеленым цветом


Это полезно, спасибо !!


Я пытаюсь выполнить форматирование на основе того, действительно ли запись совпадает с другой ячейкой. Это для ввода с несколькими вариантами ответов, и есть 100 вопросов. Я знаю, как это сделать в xcel, но не могу разобраться в листах. Мне нужно, чтобы он соответствовал значению в данной ячейке, а не на основе введенного диапазона значений или значения. Например, таким образом я могу повторно использовать лист для любого теста с множественным выбором и просто изменить строку с ключом ответа, и форматирование будет просто вытягиваться из нового значения ячейки.


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

Оцените статью
logicle.ru
Добавить комментарий