Страницы: 1
RSS
Наименьший по трём условиям с частичным совпадением, Найти ФОРМУЛАМИ НАИМЕНЬЩУЮ по критерию ЦЕНУ и по ТРЁМ УСЛОВИЯМ с ЧАСТИЧНЫМ СОВПАДЕНИЕМ в середине текста.
 

Здравствуйте УВАЖАЕМЫЕ ЭКСПЕРТЫ и посетители ФОРУМА!

Нужно найти ФОРМУЛАМИ НАИМЕНЬЩУЮ по критерию ЦЕНУ и по ТРЁМ УСЛОВИЯМ с ЧАСТИЧНЫМ СОВПАДЕНИЕМ в середине текста.  В файле пример, где таблица ИСХОДНИК и ТАБЛИЦА с ОТВЕТАМИ на одном листе (странице).

*Варианта решения два, но ВТОРОЙ вариант в работе будет более универсальный и практичный:

*1. В первом варианте – ЧАСТИНОЕ СОВПАДЕНИЕ СЛЕВА в ТЕКСТЕ.

*2. Во втором варианте - ЧАСТИНОЕ СОВПАДЕНИЕ в СЕРЕДИНЕ в ТЕКСТЕ.

Оба варианта предусматривают ПОИСК НАИМЕНЬШЕЙ ЦЕНЫ с КРИТЕРИЕМ для СОРТИРОВКИ ЦЕНЫ, т.е.: 1-я наименьшая цена, 2-я, 3-я и т.д. (значение критерия для фильтра цены указывается вручную в ячейке), а ПОЗИЦИЯ ЦЕНЫ определяется по ТРЁМ УСЛОВИЯМ, содержащим ТЕКСТ и ЧИСЛА в комбинации (вперемешку).

Файл с примером прилагаю. Таблицы на одном листе ТРИ –

1-я табл.: ИСХОДНИК ДАННЫХ (Может быть важно! В первых двух строках в исходнике в аргументах указано специально на один символ больше, чтобы исключить совпадение данных с первой ячейкой, а также в других НЕКОТОРЫХ ячейках есть ПУСТОТЫ – ОБОЗНАЧЕНЫ КРАСНЫМ ФОНОМ)

2-я табл.: УСЛОВИЯ для ПОИСКА и КРИТЕРИЙ, а затем мои НЕПРАВИЛЬНЫЕ формулы с попыткой решения И ПРИМЕРЫ - КАК ДОЛЖНО БЫТЬ – В ДВУХ СТОЛБЦАХ через один с формулами.

3-я табл.: Куда можно вставить предлагаемые Вами формулы, и там же ПРИМЕР КАК ДОЛЖНО БЫТЬ - это для ориентации. Макросы, пока что, не рассматриваю.

Спасибо за любой ответ!

 
Вы не могли бы упростить ваш пример оставив только данные, условия и как при таких условиях выглядят ответы, и желательно в чёрно-белом формате. (Эх, к такому меня жизнь не готовила. Ничего же не сделал, только зашел.)
Изменено: R091n - 07.12.2023 07:03:16
 

Здравствуйте, R091n !
По Вашей просьбе я отредактировал пример в файле для визуального восприятия с минимальным форматированием. Да, и в примере - как должно быть, исправил ответ в двух ячейках, т.к. в исходнике заменил данные двух ячеек на пусто.
Сама суть вопроса после редактирования не изменилась - отредактированный файл в этом сообщении.
Спасибо Вам огромное за логичное замечание!

 
Код
=1/НАИБОЛЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ЦЕЛОЕ((ЕЧИСЛО(ПОИСК([@Условие1];Таблица23813245[Аргумент1]))+ЕЧИСЛО(ПОИСК([@Условие2];Таблица23813245[Аргумент2]))+ЕЧИСЛО(ПОИСК([@Условие3];Таблица23813245[Аргумент3])))/МАКС(ЕЧИСЛО(ПОИСК([@Условие1];Таблица23813245[Аргумент1]))+ЕЧИСЛО(ПОИСК([@Условие2];Таблица23813245[Аргумент2]))+ЕЧИСЛО(ПОИСК([@Условие3];Таблица23813245[Аргумент3]))));1/Таблица23813245[Цена]);0);[@[№ Наименьшего]])
 
вариант
{ }
=НАИМЕНЬШИЙ(ЕСЛИОШИБКА(1/ЕЧИСЛО(ПОИСК(G3;A$3:A$12)/ПОИСК(H3;B$3:B$12)/ПОИСК(I3;C$3:C$12))/(1/D$3:D$12);"");F3)
 

Здравствуйте УВАЖАЕМЫЕ ЭКСПЕРТЫ и посетители ФОРУМА!

Огромное спасибо за помощь R091n и Павел \Ʌ/ !

Долго не отвечал, т.к. не было времени проверить предлагаемые формулы в комбинации с другими функциями.

В общем в комбинации с ПОИСКПОЗ выдало НЕ правильный ответ на конкретном этапе вычислений, т.е. несколько строк считалось ПРАВИЛЬНО, затем одна строка НЕ ПРАВИЛЬНО, потом снова ПРАВИЛЬНО!?

При этом, сначала применил ФОРМУЛ №2 предлагаемую Павел \Ʌ/, т.к. она покороче, в более массивной комбинации формул, а затем ФОРМУЛУ №1 предлагаемую R091n, и в обоих случаях выдало ОШИБКУ! А там требовалось использовать ссылку на исходник на другой странице, и предусматривается НЕСКОЛЬКО ИСХОДНИКОВ, где они имеют одинаковый формат, но разные текстовое и числовое содержание. И когда создал ИСХОДНИК №2, и изменил в нём ЦЕНЫ, то тогда и заметил РАЗНИЦУ в расчётах формулами, т.е. ОШИБКУ.

И что ВАЖНО! Ошибку выдаёт ТОЛЬКО, если в ИСХОДНЫХ ДАННЫХ, в столбце ЦЕНА, изменить КОЛИЧЕСТВО ЗНАКОВ, например, вместо 33 (вместо двух знаков), поставить 3333 (четыре знака).

Пример в ФАЙЛЕ прилагается в данном сообщении (версия Excel 2013).

Я создал на двух листах (страницах), абсолютно одинаковые таблицы (формулы и формат те же), за ИСКЛЮЧЕНИЕМ ИСХОДННЫХ ДАННЫХ, как писал выше - изменил КОЛИЧЕСТВО ЗНАКОВ в столбце ЦЕНА, но по факту последовательность чисел НЕ ИЗМЕНИЛАСЬ. Разницу сразу видно, если поочерёдно кликать на название ЛИСТОВ (СТРАНИЦ).

Да, мне ответ нужен НЕ СРОЧНО!

Спасибо всем за помощь!

 
It.Alex.K, да по тому что в  результате деление получилось не целое число, хоть и очень близкое

=MATCH(SMALL(IF(
ISNUMBER(SEARCH($I7;$A$4:$A$13)*SEARCH($J7;$B$4:$B$13)*SEARCH($K7;$C$4:$C$13)/$D$4:$D$13);
$D$4:$D$13);$H7);$D$4:$D$13;0)
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ:     в  результате деление получилось не целое число
ну да - "старые грабли"
=ОКРУГЛ(  НАИМЕНЬШИЙ(ЕСЛИОШИБКА(1/ЕЧИСЛО(ПОИСК(G3;A$3:A$12)/ПОИСК(H3;B$3:B$12)/ПОИСК(I3;C$3:C$12))/(1/D$3:D$12);"");F3) ; )
Изменено: Павел \Ʌ/ - 10.12.2023 10:13:20
 
Павел \Ʌ/, я указал на ошибку, а формула концептуально не верна, ибо округлять данные плохая затея, и  кроме определения минимального нужно учесть что его поиск нужно вести также с условиями критериев. Иначе при равенстве  которое в примере отсутствует, но вполне может быть, будет сделан выбор первого вхождения, и это может быть ошибкой. получаем двойной поиск,
Предлагаю два вариант короткий и медленный и длинный но быстрее
=MATCH(SMALL(IF(MMULT(--ISNUMBER(SEARCH(I7:K7,A$4:C$13)/D$4:D$13),{1;1;1})=3,D$4:D$13),H7),IF(MMULT(--ISNUMBER(SEARCH(I7:K7,A$4:C$13)/D$4:D$13),{1;1;1})=3,D$4:D$13),)   15,625
=MATCH(SMALL(IF(
ISNUMBER(SEARCH($I7,$A$4:$A$13)*SEARCH($J7,$B$4:$B$13)*SEARCH($K7,$C$4:$C$13)/$D$4:$D$13),
$D$4:$D$13),$H7),IF(
ISNUMBER(SEARCH($I7,$A$4:$A$13)*SEARCH($J7,$B$4:$B$13)*SEARCH($K7,$C$4:$C$13)/$D$4:$D$13),
$D$4:$D$13),0)
              7,8125
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ: концептуально не верна
Название темы какое?
Формула верно находит наименьшее, а вот искать что-то по этому значению действительно плохая идея
Вариант "если искать":   { }
=ИНДЕКС(E:E;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(1/ЕЧИСЛО(ПОИСК($I4;$A$4:$A$13)/ПОИСК($J4;$B$4:$B$13)/ПОИСК($K4;$C$4:$C$13))/(1/$D$4:$D$13)+СТРОКА(D$4:D$13)%%;"");$H4);1)/1%%)
 
Цитата
Павел \Ʌ/ написал:
СТРОКА(D$4:D$13)%%
и снова не лучший выбор. Цена может быть не целым числом, что может внести коррективы и ошибки.
По вопросам из тем форума, личку не читаю.
 
БМВ, цену можно округлить до копеек и умножить на сотню (а то может пример составите за ТС?)
Может всёж по теме?
...

=ИНДЕКС(E:E;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ОКРУГЛ(1/ЕЧИСЛО(ПОИСК($I4;$A$4:$A$13)/ПОИСК($J4;$B$4:$B$13)/ПОИСК($K4;$C$4:$C$13))/(1%/$D$4:$D$13);)+СТРОКА(D$4:D$13)%%;"");$H4);1)/1%%)
Изменено: Павел \Ʌ/ - 10.12.2023 16:08:12
 
Павел \Ʌ/, Видя синтетические данные, можно всегда предположить иные потребности по факту. Я не критикую то или иное решение, скорее предупреждаю, что могут вылезти косяки, которые можно предусмотреть. Да и решение может быть использовано для других данных, а не только ТСом, хотя я в это слабо верю. Ну а раз знаю что косяки, то предлагаю решение, их обходящие.
По вопросам из тем форума, личку не читаю.
 

Здравствуйте УВАЖАЕМЫЕ ЭКСПЕРТЫ и посетители ФОРУМА!

Огромное спасибо за помощь R091n, Павел \Ʌ/ и БМВ!

Все предложенные формулы (некоторые после корректировки ДЛЯ КОНКРЕТНЫХ УСЛОВИЙ) РАБОТАЮТ в соответствии с запросом по указанной теме.

Изначально я планировал применить предлагаемые ФОРМУЛЫ с таким УНИВЕРСАЛЬНЫМ функционалом в КОМБИНИРОВАННОЙ ФОРМУЛЕ для извлечения данных из ДВУХ ИСХОДНИКОВ находящихся на РАЗНЫХ ЛИСТАХ (СТРАНИЦАХ). Для извлечения данных из ДВУХ ИСХОДНИКОВ находящихся на РАЗНЫХ ЛИСТАХ я ранее применял ФОРМУЛУ создающую такую ссылку для поиска на разных листа, но в условиях ПОИСКА предусматривалось ПОЛНОЕ совпадение – всё работало, но с частичным совпадением до сих пор не нашёл решение.

Учитывая такие НЕ СТАНДАРТНЫЕ условия, и НЕ СОВСЕМ СОВПАДАЮЩИЕ с ТЕМОЙ ДАННОГО ПОСТА, решил создать НОВУЮ ТЕМУ: «Извлечь НАИМЕНЬШЕЕ по ТРЁМ УСЛОВИЯМ с ЧАСТИЧНЫМ СОВПАДЕНИЕМ в СЕРЕДИНЕ текста из ДВУХ ИСХОДНИКОВ находящихся на РАЗНЫХ ЛИСТАХ (СТРАНИЦАХ). Версия Excel-2013.»

Ссылка на НОВУЮ тему: https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=161778&TITLE_SEO=161778-izvlech-naimenshee-po-tryem-usloviyam-s-chastichnym-sovpadeniem-v-seredine-teksta-iz-dvukh-iskhodnikov-nakhodyashchikhsya-na-raznykh-listakh-_stranitsakh_.-versiya-excel_2013.&MID=1279136&result=new#message1279136

Всем ОГРОМНОЕ СПАСИБО!

Страницы: 1
Наверх