Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
ПоискПозХ(ИЛИ ВПР), когда в ячейке одной ячейке несколько значений того, что нужно найти
 
#1
27.03.2024 09:37:18
Подскажите пожалуйста, как можно преобразовать данные, чтобы добиться желаемого результата как на листе Предметы в ячейке A16, B16 (это пример). Для всех параллелей, указанных в диапазоне А2:С5 этого же листа предметы. Соответствие параллель и номер класса на листе классы в плоской таблице.
Изменено: extrafant - 27.03.2024 09:37:34
 
 
 
#2
27.03.2024 12:30:51
Приветствую.
Я по-простому сделал, добавив доп. столбец Индекс на лист Предметы.
Результаты убрал на отдельный лист.
 
 
 
#3
27.03.2024 13:33:38
Спасибо! Огромное спасибо за оперативность
 
 
 
#4
28.03.2024 07:38:47
extrafant, на здоровье!
Ещё небольшое замечание по формуле колонки Параллель листа Классы.
Она сейчас выглядит так (я лишь добавил в начале --, чтобы перевести в число - т.к. на листе Предметы параллели числовые):
Код
{=--ПСТР(ЛЕВСИМВ(A2; МАКС(ЕЧИСЛО(ПСТР(A2;
СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)*1)*
СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))))); ПОИСКПОЗ(ИСТИНА; ЕЧИСЛО(ПСТР(A2;
СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)*1); 0); ДЛСТР(A2))}
Формула правильная, но использование ДВССЫЛ() может приводить на больших данных к тормозам, т.к. она "волатильная", т.е. пересчитывается при любом изменении книги. ДВССЫЛ() можно заменять сочетанием ИНДЕКС():ИНДЕКС().
Код
{=--ПСТР(ЛЕВСИМВ(A2; МАКС(ЕЧИСЛО(--ПСТР(A2;
СТРОКА(ИНДЕКС($A:$A;1) : ИНДЕКС($A:$A; ДЛСТР(A2))); 1)) *
СТРОКА(ИНДЕКС($A:$A;1) : ИНДЕКС($A:$A; ДЛСТР(A2))); 1)); ПОИСКПОЗ(ИСТИНА; ЕЧИСЛО(--ПСТР(A2;
СТРОКА(ИНДЕКС($A:$A;1) : ИНДЕКС($A:$A; ДЛСТР(A2))); 1));0); ДЛСТР(A2))}
Либо воспользоваться формулой Дмитрия Щербакова (https://www.excel-vba.ru/chto-umeet-excel/kak-ostavit-v-yachejke-tolko-cifry-ili-tolko-tekst/):
Код
{=--ПСТР(A2; МИН(ЕСЛИ(
ЕЧИСЛО(-ПСТР(A2; СТРОКА($1:$99); 1)); СТРОКА($1:$99))); ПРОСМОТР(2;1/
ЕЧИСЛО(-ПСТР(A2; СТРОКА($1:$99); 1)); СТРОКА($1:$99)) - МИН(ЕСЛИ(
ЕЧИСЛО(-ПСТР(A2; СТРОКА($1:$99); 1)); СТРОКА($1:$99)))+1)}
Все вышеприведённые формулы универсальные и извлекают число, стоящее на любом месте в строке текста: для "производительность труда выросла на 13,78 процентов" => "13,78".

Если же принять ряд упрощений и допущений (в названии класса 0 или 1 буква, например) можно совсем упростить формулу:
Код
=--ЛЕВСИМВ(A2; ДЛСТР(A2) - ЕСЛИОШИБКА(--ПРАВСИМВ(A2)<0;1))
PS. Формулы я разбил на строки для читаемости.
 
 
 
#5
28.03.2024 07:55:00
=INDEX($C$2:$C$5;MATCH(VLOOKUP(A16;Таблица1;3;)&" *№"&VLOOKUP(A16;Таблица1;2;)&",*";$A$2:$A$5&" "&$B$2:$B$5&",";))

Первый лист - требует переработки ибо очень не оптимальнопараллеь - =-IFERROR(-LEFT([@Класс],2),IFERROR(-LEFT([@Класс],1),0))
По вопросам из тем форума, личку не читаю.
 
 
 
#6
28.03.2024 09:09:13
Спасибо огромное за советы!

С одной стороны - потребности оптимизировать нет т.к. это и все, что нужно от книги
С другой стороны я согласен с тем, что привыкать надо делать сразу правильно!
Огромное спасибо за ответы!
 
 
 
#7
28.03.2024 09:21:16
Я попробую. еще доработать
Да, сейчас находит предметы, преобразовав название, но вот самое главное - не создает массивы с параллелью
Например,
6№1, №3, №5, №7, №9
6№2, №4, №6, №8, №10, №11
Вот что во входящей таблице
В результате хотелось бы так:
1. столбец список классов с буквами № 1 это А № 2 это Б № 3 это В (сочетание класс, буква на первом листе)
2 столбец с предметом
 
 
 
#8
28.03.2024 10:02:59
Цитата
extrafant написал:
потребности оптимизировать нет т.к. это и все, что нужно от книги
дело в том что формула исходная на первом листе и не оптимальна , да еще и летуча. Это заставляет пересчитывать все книги открытые при каждом чихе. В какой то момент окажется что ввели в ячейку данные и приложение задумалось на пару секунд. Вам это надо?

Цитата
extrafant написал:
В результате хотелось бы так:1. столбец список классов с буквами № 1 это А № 2 это Б № 3 это В (сочетание класс, буква на первом листе)2 столбец с предметом
не относится к данной теме.
Изменено: БМВ - 28.03.2024 10:03:47
По вопросам из тем форума, личку не читаю.
 
 
 
#9
28.03.2024 10:24:40
Цитата
написал:
В результате хотелось бы так:
Возможно, формула массива потребуется, а я в них не силён.
Давайте пример желаемого результата, попробую(ем) что-то порешать.
Т.к. в исходном примере вид был простой:
КлассПредмет
6АБиология линейная
 
 
 
#10
28.03.2024 10:30:17
Огромное спасибо, что посмотрите можно ли доделать до конца
На листе Предметы желтым - то, что должно создаваться само
Все. Остальное готово.
 
 
 
#11
28.03.2024 14:13:13
С несколькими доп. столбцами, но зато формулы достаточно простые получились.
 
 
 
#12
28.03.2024 14:18:35
Огромное спасибо! тут уж важно, что работает. А так, как у таблицы одна функция, то в принципе не важно. Протягивать можно, если что)! Спасибо
Есть, конечно, один недостаток. Не понятно (пока сам не посчитаешь) насколько тянуть. И, если ее перетянуть, то будет ошибка уже во всех столбцах
Изменено: extrafant - 28.03.2024 14:22:48
 
 
 
#13
28.03.2024 14:32:30
Чуть улучшил.
 
 
 
#14
28.03.2024 14:37:08
Спасибо! Теперь, ошибки нет! Можно протянуть и быть уверенным, что значение найдется
 
 
 
#15
28.03.2024 17:16:59
extrafant, подскажите, на листе "предметы" каждый номер в столбце "номер класса" - это номера из столбца "порядковый номер" на листе "Классы"?
 
 
 
#16
28.03.2024 17:18:55
именно. По нему присваивается буква, а затем предмет, который дети пишут
 
 
 
#17
28.03.2024 17:35:41
del
Изменено: voler83 - 04.04.2024 11:51:42
 
 
 
#18
29.03.2024 06:14:45
Спасибо за такое решение,  но хотелось именно формулами. Ведь формулами можно работать и он лайн и с устройства андроид
 
 
 
#19
29.03.2024 07:43:11
Цитата
extrafant написал:
но хотелось именно формулами
именно по этому важно указывать все подобные условия включая версию excel. Хотя иные предложенные решения могут помочь другим, ведь часто важен не ответ под копирку, а метод, способ .....
По вопросам из тем форума, личку не читаю.
 
 
 
#20
08.04.2024 09:23:30
Обнаружил, что в формуле все-же где-то закралась логическая ошибка!
Во входящих данных на листе Предметы  внесены данные:
8№5, №6, №11,   №12Обществознание   в компьютерной форме
видно, что пишут 4 класса (на листе предметы строка 6), а на листе "В приказ" только два класса обозначены почему-то
Изменено: extrafant - 08.04.2024 09:23:45
 
 
 
#21
08.04.2024 10:33:30
А это нормально, что один и тот же класс (6, 11, 12) на разные предметы записан?
ПараллельКлассыПредмет
8№6, №10, №11, №12Биология   концентрическая
8№5, №6, №11, №12Обществознание в   компьютерной форме
Формулы на листе Результат этого не предусматривали - поправил, см. файл.
На листе "В приказ" используются новые функции, которых нет в моём Excel 2016, поэтому я там ничего проверить не могу - выводятся ошибки #ИМЯ?.
Изменено: andypetr - 08.04.2024 10:44:00 (Изменил файл.)
 
 
 
#22
08.04.2024 10:41:19
Оказывается, что нормально. Работы проходят в разные дни. Если честно, я не ожидал, что так будет. Но оно так


Спасибо огромное! Вроде, все ОК
Изменено: extrafant - 08.04.2024 10:50:41
 
 
 
Страницы: 1
Наверх