Производятся опыты (один опыт - одна строка) В каждом опыте производится 25 пАрных измерений (номера измерений в строке 2) по двум параметрам (Параметр 1 и Параметр 2). То есть, в разделах Параметр 1 и Параметр 2, номера 1-1, 2-2 и т.п. из строки 2 являются соответственно парными. В столбцах А и В задаются искомые значения: - столбец А, содержит искомое значение для Параметра 1 (т.е. для столбцов AB-AZ); - столбец В, содержит искомое значение для Параметра 2 (т.е. для столбцов C-AA).
Задача (общая формулировка): найти по искомым значениям (Иск1,Иск2), наиболее подходящие номера измерений (строка 2), при условии, что значение Иск1, определяет набор данных для Иск2.
Задача (кратко): 1. Берём A3 и ищем в диапазоне AB-AZ. 2. Для найденных, ищем парные ячейки (соответствия) из диапазона C-AA. 3. В значениях парных ячеек ищем В3. 4. Если есть совпадения, то в раздел "ТОЧНО" записываем соответствующие номера измерений (Параметр 2, строка 2). 5. Если совпадений нет, то СРЕДИ ЭТИХ ПАРНЫХ ЯЧЕЕК ищем ближайшую величину и записываем номера измерений (Параметр 2, строка 2) в колонку "НЕТОЧНО".
Монструозное, но более детальное описание. 1. Берём искомое значение Иск1 из столбца А (для примера - А3). 2. Ищем это значение в Параметр 1 (т.е. в диапазоне AB3-AZ3). 3. Если есть совпадения, то для этих ячеек получаем НОМЕРА ИЗМЕРЕНИЙ (Параметр 1, строка 2) в которых произошло совпадение. 4. Если совпадений нет, то ищем ближайшее ПО ВЕЛИЧИНЕ (из диапазона AB3-AZ3), и возвращаемся в пункт 3.
5. По полученным на шаге 3 НОМЕРАМ ИЗМЕРЕНИЙ, ищем в Параметр 2, ячейки с такими же номерами измерений и получаем СОДЕРЖАЩИЕСЯ В НИХ значения. 6. Ищем значение Иск2 из столбца В (для примера - В3) в значениях полученнымх в пункте 5. 7. Если есть совпадения, то НОМЕР ИЗМЕРЕНИЯ для совпавших, записываем в раздел "ТОЧНО совпадают по ОБОИМ условиям" (столбцы ВВ-BF), причём первое совпавшее в столбец BB, второе совпавшее в столбец BC и так далее до пяти совпадений (вряд ли их будет больше). Конец. 8. Если совпадений нет, то ищем ближайшее ПО ВЕЛИЧИНЕ из полученных в пункте 5 и записываем соответвующий НОМЕР ИЗМЕРЕНИЯ уже в раздел "НЕТОЧНО совпадают по ПАРАМЕТР 2 (минимально отличаются)", т.е. в ячейки BH-BL. 9. В случае неточного совпадения, в ячейках BM-BQ и BR-BV заносится величина (плюс или минус), на которую ближайшее ПО ВЕЛИЧИНЕ отличается от В2. Конец.
Пытался сделать через введение дополнительных столбцов с промежуточными расчётами, но застрял... Помогите, пожалуйста! Файл с примером прилагаю.
Уважаемые знатоки Excel-a!
Помогите, пожалуйста, с таблицей!
Производятся опыты (один опыт - одна строка) В каждом опыте производится 25 пАрных измерений (номера измерений в строке 2) по двум параметрам (Параметр 1 и Параметр 2). То есть, в разделах Параметр 1 и Параметр 2, номера 1-1, 2-2 и т.п. из строки 2 являются соответственно парными. В столбцах А и В задаются искомые значения: - столбец А, содержит искомое значение для Параметра 1 (т.е. для столбцов AB-AZ); - столбец В, содержит искомое значение для Параметра 2 (т.е. для столбцов C-AA).
Задача (общая формулировка): найти по искомым значениям (Иск1,Иск2), наиболее подходящие номера измерений (строка 2), при условии, что значение Иск1, определяет набор данных для Иск2.
Задача (кратко): 1. Берём A3 и ищем в диапазоне AB-AZ. 2. Для найденных, ищем парные ячейки (соответствия) из диапазона C-AA. 3. В значениях парных ячеек ищем В3. 4. Если есть совпадения, то в раздел "ТОЧНО" записываем соответствующие номера измерений (Параметр 2, строка 2). 5. Если совпадений нет, то СРЕДИ ЭТИХ ПАРНЫХ ЯЧЕЕК ищем ближайшую величину и записываем номера измерений (Параметр 2, строка 2) в колонку "НЕТОЧНО".
Монструозное, но более детальное описание. 1. Берём искомое значение Иск1 из столбца А (для примера - А3). 2. Ищем это значение в Параметр 1 (т.е. в диапазоне AB3-AZ3). 3. Если есть совпадения, то для этих ячеек получаем НОМЕРА ИЗМЕРЕНИЙ (Параметр 1, строка 2) в которых произошло совпадение. 4. Если совпадений нет, то ищем ближайшее ПО ВЕЛИЧИНЕ (из диапазона AB3-AZ3), и возвращаемся в пункт 3.
5. По полученным на шаге 3 НОМЕРАМ ИЗМЕРЕНИЙ, ищем в Параметр 2, ячейки с такими же номерами измерений и получаем СОДЕРЖАЩИЕСЯ В НИХ значения. 6. Ищем значение Иск2 из столбца В (для примера - В3) в значениях полученнымх в пункте 5. 7. Если есть совпадения, то НОМЕР ИЗМЕРЕНИЯ для совпавших, записываем в раздел "ТОЧНО совпадают по ОБОИМ условиям" (столбцы ВВ-BF), причём первое совпавшее в столбец BB, второе совпавшее в столбец BC и так далее до пяти совпадений (вряд ли их будет больше). Конец. 8. Если совпадений нет, то ищем ближайшее ПО ВЕЛИЧИНЕ из полученных в пункте 5 и записываем соответвующий НОМЕР ИЗМЕРЕНИЯ уже в раздел "НЕТОЧНО совпадают по ПАРАМЕТР 2 (минимально отличаются)", т.е. в ячейки BH-BL. 9. В случае неточного совпадения, в ячейках BM-BQ и BR-BV заносится величина (плюс или минус), на которую ближайшее ПО ВЕЛИЧИНЕ отличается от В2. Конец.
Пытался сделать через введение дополнительных столбцов с промежуточными расчётами, но застрял... Помогите, пожалуйста! Файл с примером прилагаю.Kaktus8
Ух! Огромное спасибо! А я такой огород нагородил со вспомогательными столбцами и т.п., что в итоге попросту завис. А у Вас без вспомогательных столбцов и так элегантно и компактно получилось! Я в восхищении! Спасибо большое!
Еще две просьбы, если Вас не затруднит. 1. Если в таблице попытаться вставить дополнительные столбцы в диапазоне А-Е, то формулы "плывут" и часть полученных значений исчезает. При вставке столбцов после колонки "Е" этого не происходит. Можно ли что-то с этим сделать? А то, вдруг забуду, вставлю столбцы, все данные "поплывут", а это не сразу заметно становится. Если возможно, то было бы здОрово что-то с этим сделать.
2. Если, например, в ячейке А3 (Иск1) попадётся значение, которого нет в Параметр 1 (т.е. в диапазоне AB-AZ), то "на выходе" расчётные данные будут отсутствовать (например, если ввести в А3 шестёрку, то выходных данных не будет).
Поэтому, возникает потребность в варианте с тремя дополнительными столбцами: ВG - найденное ближайшее по величине значение Иск1 из Параметр 1 (АВ-AZ) по которому теперь производится расчёт; BH - величина, на которую Иск1 стал БОЛЬШЕ. BI - величина, на которую Иск1 стал МЕНЬШЕ.
Смысл этих столбцов: если в Параметр 1 (т.е. в ячейках AB-AZ) значение Иск1 (т.е. столбец А) отсутствует, то находим среди них ближайшее по величине и дальнейший расчёт ведём уже по нему. Если для этого нового значения Иск1, найдутся точные соответствия и по Иск2, то их, как и раньше, заносим в столбцы (BB-BF), + в добавленный новый столбец BG записываем метку в виде нового найденного ближайшего значения (в противном случае - 0), а в столбцы BH и BI его отклонение от исходного Иск1 в большую или меньшую стороны. Если для этого нового значения НЕТ точных соответствий по Иск2, то столбцы (BB-BF) остаются пустыми, а в столбцах BQ-CE ищутся неточные совпадения по Параметр 2 (то есть всё рассчитывается как раньше). Правда, тут наверное, нужно как-то модифицировать формулы неточного поиска по Иск2, чтобы они искали эти неточные совпадения Иск2 не только с учётом оригинального Иск1, но также и учётом его найденного ближайшего значения. Ведь оригинальные значения Иск1 в столбце А изменяться не должны.
В целом, по общей логике работы - всё то же самое, только при отсутствии точного совпадения по Иск1, добавляются поиск его ближайшего по величине значения, метка его наличия (=новое значение Иск1) и отклонения в плюс/минус. Если не сложно, то помогите, пожалуйста, внести эти изменения. Файл с добавленными столбцами прилагаю.
Ух! Огромное спасибо! А я такой огород нагородил со вспомогательными столбцами и т.п., что в итоге попросту завис. А у Вас без вспомогательных столбцов и так элегантно и компактно получилось! Я в восхищении! Спасибо большое!
Еще две просьбы, если Вас не затруднит. 1. Если в таблице попытаться вставить дополнительные столбцы в диапазоне А-Е, то формулы "плывут" и часть полученных значений исчезает. При вставке столбцов после колонки "Е" этого не происходит. Можно ли что-то с этим сделать? А то, вдруг забуду, вставлю столбцы, все данные "поплывут", а это не сразу заметно становится. Если возможно, то было бы здОрово что-то с этим сделать.
2. Если, например, в ячейке А3 (Иск1) попадётся значение, которого нет в Параметр 1 (т.е. в диапазоне AB-AZ), то "на выходе" расчётные данные будут отсутствовать (например, если ввести в А3 шестёрку, то выходных данных не будет).
Поэтому, возникает потребность в варианте с тремя дополнительными столбцами: ВG - найденное ближайшее по величине значение Иск1 из Параметр 1 (АВ-AZ) по которому теперь производится расчёт; BH - величина, на которую Иск1 стал БОЛЬШЕ. BI - величина, на которую Иск1 стал МЕНЬШЕ.
Смысл этих столбцов: если в Параметр 1 (т.е. в ячейках AB-AZ) значение Иск1 (т.е. столбец А) отсутствует, то находим среди них ближайшее по величине и дальнейший расчёт ведём уже по нему. Если для этого нового значения Иск1, найдутся точные соответствия и по Иск2, то их, как и раньше, заносим в столбцы (BB-BF), + в добавленный новый столбец BG записываем метку в виде нового найденного ближайшего значения (в противном случае - 0), а в столбцы BH и BI его отклонение от исходного Иск1 в большую или меньшую стороны. Если для этого нового значения НЕТ точных соответствий по Иск2, то столбцы (BB-BF) остаются пустыми, а в столбцах BQ-CE ищутся неточные совпадения по Параметр 2 (то есть всё рассчитывается как раньше). Правда, тут наверное, нужно как-то модифицировать формулы неточного поиска по Иск2, чтобы они искали эти неточные совпадения Иск2 не только с учётом оригинального Иск1, но также и учётом его найденного ближайшего значения. Ведь оригинальные значения Иск1 в столбце А изменяться не должны.
В целом, по общей логике работы - всё то же самое, только при отсутствии точного совпадения по Иск1, добавляются поиск его ближайшего по величине значения, метка его наличия (=новое значение Иск1) и отклонения в плюс/минус. Если не сложно, то помогите, пожалуйста, внести эти изменения. Файл с добавленными столбцами прилагаю.Kaktus8
Очень сложно понять логику. "Многа букаф". А не устроит одна последовательность с наименьшими отклонениями от искомых значений. Если есть полные совпадения, они будут в начале, потом начнутся минимально отличающиеся от искомых. Хоть 1, хоть 2. Для 1 можно придать больший вес. Посмотрите на такую формулу:
1. Если в таблице попытаться вставить дополнительные столбцы
Если изменятся диапазоны или аргументы в формулах, их в любом случае надо править.
Очень сложно понять логику. "Многа букаф". А не устроит одна последовательность с наименьшими отклонениями от искомых значений. Если есть полные совпадения, они будут в начале, потом начнутся минимально отличающиеся от искомых. Хоть 1, хоть 2. Для 1 можно придать больший вес. Посмотрите на такую формулу:
А не устроит одна последовательность с наименьшими отклонениями от искомых значений. Если есть полные совпадения, они будут в начале, потом начнутся минимально отличающиеся от искомых. Хоть 1, хоть 2. Для 1 можно придать больший вес. Посмотрите на такую формулу:
Спасибо! Это тоже вариант, но не совсем подходит по ряду причин.
Возник ещё один вариант с минимумом переделок в Вашем первом файле. Вводим ОДНУ дополнительную колонку (столбец В), которую называем Иск1 (Изм). В него прописываем формулу, которая берёт исходное значение из столбца А и ищет его в диапазоне АС-ВА. Если такое значение есть, то записывает в столбец В значение из столбца А. Если такого значения нет, то записывает в столбец В ближайшее по величине из диапазона АС-ВА.* Все остальные формулы берут значение Иск1 уже из столбца В и больше в них ничего менять не нужно. Файл прилагаю.
* - если ближайших значений получается два, то выбирается ближайшее к среднему значению из диапазона AC-BA.
А не устроит одна последовательность с наименьшими отклонениями от искомых значений. Если есть полные совпадения, они будут в начале, потом начнутся минимально отличающиеся от искомых. Хоть 1, хоть 2. Для 1 можно придать больший вес. Посмотрите на такую формулу:
Спасибо! Это тоже вариант, но не совсем подходит по ряду причин.
Возник ещё один вариант с минимумом переделок в Вашем первом файле. Вводим ОДНУ дополнительную колонку (столбец В), которую называем Иск1 (Изм). В него прописываем формулу, которая берёт исходное значение из столбца А и ищет его в диапазоне АС-ВА. Если такое значение есть, то записывает в столбец В значение из столбца А. Если такого значения нет, то записывает в столбец В ближайшее по величине из диапазона АС-ВА.* Все остальные формулы берут значение Иск1 уже из столбца В и больше в них ничего менять не нужно. Файл прилагаю.
* - если ближайших значений получается два, то выбирается ближайшее к среднему значению из диапазона AC-BA.Kaktus8