Несколько значений для ПОИСК

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Несколько значений для ПОИСК (Формулы/Formulas)

arxon

Дата: Четверг, 18.04.2019, 14:38 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 54

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Господа, добрый день. уже который день не могу решить простую задачу:
мне нужно указать в формуле не одно значение - "клавиш*", а несколько значений. Например "клавиш*", "пианин*", "рояль"
Конечно удобнее всего было бы вставить диапазон D1:D10 например или сохраненный ранее через диспетчер имен список значений - Клавишные_инструменты
но у меня не получилось
вот моя формула

Код

=ЕСЛИ(ЕЧИСЛО(ПОИСК("клавиш*";$A36));C$1;"")

файл приложен

К сообщению приложен файл: 2830241.xlsx (16.3 Kb)

Сообщение отредактировал arxon - Четверг, 18.04.2019, 16:20

Ответить

_Boroda_

Дата: Четверг, 18.04.2019, 14:49 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 16913

Репутация: 6617 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

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

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

arxon

Дата: Четверг, 18.04.2019, 16:22 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 54

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

все исправил!

Ответить

sboy

Дата: Четверг, 18.04.2019, 16:27 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

Добрый день.
Так хотели? формула массива

Код

=ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК('Варианты содержания ячеек кол.A'!$B$2:$B$4;$A2)));C$1;"")

Яндекс: 410016850021169

Ответить

_Boroda_

Дата: Четверг, 18.04.2019, 16:37 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16913

Репутация: 6617 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Так нужно?

Код

=ЕСЛИ(СЧЁТ(ПОИСК('Варианты содержания ячеек кол.A'!B$1:ИНДЕКС('Варианты содержания ячеек кол.A'!B:B;СЧЁТЗ('Варианты содержания ячеек кол.A'!B:B));$A2));C$1;"")

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

* Для ПОИСК звездочки не нужны. Они нужны (с двух сторон - *текст*) для ПОИСКПОЗ или СЧЁТЕСЛИ, который тоже можно в формулу всунуть

Код

=ЕСЛИ(СУММПРОИЗВ(СЧЁТЕСЛИ($A2;'Варианты содержания ячеек кол.A'!B$1:ИНДЕКС('Варианты содержания ячеек кол.A'!B:B;СЧЁТЗ('Варианты содержания ячеек кол.A'!B:B))));C$1;"")

Код

=ЕСЛИ(СЧЁТ(ПОИСКПОЗ('Варианты содержания ячеек кол.A'!B$1:ИНДЕКС('Варианты содержания ячеек кол.A'!B:B;СЧЁТЗ('Варианты содержания ячеек кол.A'!B:B));$A2;));C$1;"")

СЧЁТЕСЛИ немассивная

Можно еще кучу вариантов придумать

К сообщению приложен файл: 2830241_1.xlsx (16.3 Kb)

Ответить

arxon

Дата: Четверг, 18.04.2019, 16:40 | Сообщение № 6

Группа: Пользователи

Ранг: Участник

Сообщений: 54

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Цитата sboy, 18.04.2019 в 16:27, в сообщении № 5 ()

Добрый день.
Так хотели? формула массива

=ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК('Варианты содержания ячеек кол.A'!$B$2:$B$4;$A2)));C$1;"")

Добрый день, да, но не совсем.
во-первых $ перед колонкой не нужен

Код

=ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК('Варианты содержания ячеек кол.A'!B$2:B$4;$A2)));C$1;"")

во-вторых если диапазон B$2:B$4 содержит пустую ячейку, то формула выдает C$1. Я бы хотел в формуле использовать B$2:B$20 для всех, и дописывать варианты в таблицу на листе "Варианты содержания ячеек кол.A"

Ответить

arxon

Дата: Четверг, 18.04.2019, 16:54 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 54

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Цитата _Boroda_, 18.04.2019 в 16:37, в сообщении № 6 ()

Так нужно?

=ЕСЛИ(СЧЁТ(ПОИСК('Варианты содержания ячеек кол.A'!B$1:ИНДЕКС('Варианты содержания ячеек кол.A'!B:B;СЧЁТЗ('Варианты содержания ячеек кол.A'!B:B));$A2));C$1;"")

вот эта работала сперва, но как только я добавил новый вариант на лист Варианты содержания ячеек кол.A (dj в ячейку О20) формула заполнила значением "DJ оборудование" весь столбец P, а это неправильно.

остальные 2 формулы результата не дали

Ответить

_Boroda_

Дата: Четверг, 18.04.2019, 16:58 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 16913

Репутация: 6617 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Не

Цитата arxon, 18.04.2019 в 16:54, в сообщении № 8 ()

остальные 2 формулы результата не дали

, а вы не прочитали мой пост полностью и не посмотрели мойфайл-пример

Цитата arxon, 18.04.2019 в 16:54, в сообщении № 8 ()

dj в ячейку О20

С каких пирогов в О20-то? В О4 нужно добавлять, по порядку чтобы было

Ответить

arxon

Дата: Четверг, 18.04.2019, 16:58 | Сообщение № 9

Группа: Пользователи

Ранг: Участник

Сообщений: 54

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Цитата arxon, 18.04.2019 в 16:54, в сообщении № 8 ()

Кажется я понял, если последовательно список наполнять, без разрывов, все должно нормально рассчитываться

Ответить

arxon

Дата: Четверг, 18.04.2019, 18:02 | Сообщение № 10

Группа: Пользователи

Ранг: Участник

Сообщений: 54

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Цитата _Boroda_, 18.04.2019 в 16:37, в сообщении № 6 ()

Спасибо, решение подходит и отлично работает

Ответить

arxon

Дата: Четверг, 18.04.2019, 22:09 | Сообщение № 11

Группа: Пользователи

Ранг: Участник

Сообщений: 54

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

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

К сообщению приложен файл: ____2.xlsx (12.4 Kb)