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

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

Получение данных другого листа (в excel) с помощью формулы

В этой формуле строки, столбцы и/или имя листа могут быть изменены для отображения данных на текущем листе с любого другого листа в книге. Введите общий номер строки в ячейку A1, общий номер столбца в ячейку B1 и общее имя листа в ячейку C1. Эта формула относится к функциям в категории «Ссылки и массивы».

Если же не убирать галочку в «Сервис» — «Параметры» — вкладка «Диаграмма», то можно этим воспользоваться.
Умышленно скрывая строки мы можем получить из одной диаграммы несколько. В файле примере на диаграмме показывается динамика по «Западу» в то время, как диаграмма содержит данные и по «Востоку». Все дело в том, что строка 4 скрыта. Если же мы отобразим ее, то на диаграмме уже появиться 2 ряда столбцов.
См. файл-пример.

Прикрепления: ne_ubirat.xls (19Kb)

Извлечь число из ячейки смешанного содержания (например 35 шт.; ук467рв) цифры в ячейке должны идти подряд
Доступно только для пользователей =ПСТР(A15;ПОИСКПОЗ(0;(ЕОШИБКА(ПСТР(A15;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A15)));1)*1)*1);0);ДЛСТР(A15)-СУММ((ЕОШИБКА(ПСТР(A15;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A15)));1)*1)*1))) *

Условие ЕСЛИ ячейка содержит определенный текст
Доступно только для пользователей =ЕСЛИ(ЕЧИСЛО(ПОИСК(«текст»;A7;1));»содержит»;»не содержит»)

Подсчет количества по нескольким условиям (Тема обсуждения)
Доступно только для пользователей =СЧЁТ(ЕСЛИ((A2:A11=»Южный»)*(C2:C11=»Мясо»);D2:D11)) *

Подсчет «слов» в ячейке после символа №
Доступно только для пользователей =ДЛСТР(СЖПРОБЕЛЫ(ПСТР(B14;НАЙТИ(«№»;B14;1);1000)))-ДЛСТР(ПОДСТАВИТЬ(ПСТР(B14;НАЙТИ(«№»;B14;1);1000);» «;»»))

Расчет времени между датами. Следующая формула возвращает возраст в годах, месяцах, днях относительно даты в ячейке B2
Доступно только для пользователей =РАЗНДАТ(B2;СЕГОДНЯ();»y»)&» лет «&РАЗНДАТ(B2;СЕГОДНЯ();»ym»)&» мес. «&РАЗНДАТ(B2;СЕГОДНЯ();»md»)&» дн.»

Возвращение последнего значения в столбце.
Если столбец, последнее значение которого нужно найти, не имеет пустых ячеек:
Доступно только для пользователей =СМЕЩ(А1;СЧЕТЗ(А:А)-1;0)
Если столбец может содержать пустые строки:
Доступно только для пользователей =ИНДЕКС(F1:F30;МАКС(СТРОКА(F1:F30)*(F1:F30<>«»))) *
Эта формула возвращает содержимое последней не пустой ячейки в первых тридцати строках столбца F, не стоит злоупотреблять слишком большим количеством строк в столбце, это будет снижать скорость вычислений.

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

Перечень дней без суббот и воскресений
формула позволяет получить набор дат без суббот и воскресений
Доступно только для пользователей =ЕСЛИ(ДЕНЬНЕД(B2;2)=5;B2+3;B2+1)
(в ячейку B2 следует поместить дату от которой будем отталкиваться, а саму формулу поместить в ячейку B3)

Нумерация строк, которая сохраняется при удалении строк из середины списка
Доступно только для пользователей =МАКС($B$1:B1)+1
формулу ставить в В2 и растянуть вниз.

Извлечение уникальных значений.
Доступно только для пользователей =ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);» «);СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183))))) *
G7:g183 массив значений.

Немного улучшена, чтобы не возникало ошибок #Number.

Доступно только для пользователей =ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);» «);СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)))));»»;ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);» «);СТРОКА(ДВССЫЛ(«1:»&ЧСТРОК(G7:G183)))))) *

Зебра
Выделить ячейки таблицы (кроме «шапки»), открыть меню Формат — Условное форматирование (Format — Conditional Formatting), выбрать в раскрывающемся списке вариант Формула вместо Значение и ввести такую формулу:
Доступно только для пользователей =ОСТАТ(СТРОКА(A7);2)=0

Если в ячейке A4 столбца C Листа2 нет совпадения (0 или FALSE), мы возвращаем данные из столбца 10. Если совпадающее значение не найдено, мы возвращаем «отсутствует».

Если ячейка содержит одну из многих вещей

Чтобы проверить ячейку по одной или нескольким строкам и вернуть настраиваемый результат для первого найденного совпадения, можно использовать формулу INDEX / SEARCHPOPE, основанную на функции SEARCH. В показанном примере формула C5 имеет следующий вид:

Где объекты (E5 : E8) и результаты (F5 : F8) — это диапазоны имен.

Это формула массива, которую необходимо ввести с помощью комбинации клавиш Control+Shift+Enter.

Объяснение

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

Формула использует следующий фрагмент:

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

Эта формула основана на другой формуле (подробно описанной здесь), которая проверяет наличие одной подстроки в ячейке. Если ячейка содержит подстроку, формула возвращает TRUE. В противном случае формула возвращает FALSE.

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

Цифры обозначают совпадения в вещах, ошибки — элементы, которые не найдены.

Чтобы упростить массив, мы используем функцию ISNUMBER для преобразования всех элементов массива в TRUE или FALSE. Любое правильное число становится TRUE, а любая ошибка (т.е. что-то не найдено) становится FALSE. В результате получается массив, подобный этому:

Который включается в функцию SEARCH как аргумент lookup_array с lookup_value, равным TRUE:

После этого SEARCHPOZ возвращает позицию первого TRUE-значения, в данном случае 2.

Наконец, мы используем функцию INDEX, чтобы получить результат по имени диапазона результатов в одной и той же позиции:

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

Предотвращение ложных совпадений

I SNUMBER + ПОИСК может привести к ложным совпадениям из-за частичных совпадений внутри длинных слов. Например, если вы пытаетесь найти слово «dr», вы также можете найти «Andrea», «drank», «drip» и т.д. и т.п., потому что «dr» встречается внутри этих слов. В этом случае ПОИСК автоматически выполняет сопоставление содержащего типа.

Чтобы быстро решить проблему, можно заключить ключевые слова в пробелы (например, «dr» или «dr»), чтобы избежать поиска «dr» в другом слове. Однако это не сработает, если слово «dr» окажется первым или последним в ячейке.

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

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