Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск по таблице с подгруппами с возможностью её расширения, Таблица для расчёта режимов резания.
 
#1
03.12.2019 11:17:41
 Здравствуйте.

Когда начинал эту таблицу с формулами в Excel был не знаком совсем (не было необходимости). Но благодаря данному сайту смог добиться некоторых результатов. А теперь застрял на одном пункте и никак не могу найти решение данной проблемы.

Есть таблица со скоростью резания (Лист1),и лист с выбором нужных параметров и как итог выводом значение скорости из таблицы (Лист2). Строки в таблице построены просто и каждая строка это материал и найти нужную строку не вызывает проблем, а вот со столбцами немного сложнее. Есть 2 группы по охлаждению,кадая из них делиться на 3 группы по типу инструмента,каждый тип инструмента делиться на сплавы,а каждый сплав на режим обработки.

Когда таблица была небольшой то с помощью индекса найти нужное значение было не проблема, это и показал в Листе 2. Но сейчас таблица разрастается и некоторые сплавы добавляются только в определённый тип инструмента и добавлять в остальные типы этот сплав с пустыми значениями не выход, так как в итоговой таблице уже надо добавить новый тип инструмента, а в нём старых сплавов вообще нет. Режимы обработки у каждого сплава всегда одинаковые: лайт, медиум, хард.

Вопрос: как (желательно формульно) сделать поиск по столбцам с возможностью добавления новых сплавов и типов инструмента?
Изменено: edifer5 - 03.12.2019 13:09:05
 
 
 
#2
04.12.2019 07:38:59
Все ИМХО:
1. транспонировать исходную "таблицу со скоростью резания", тогда можно пользоваться автофильтром, сортировкой и т.п.
2. убрать объединенные ячейки (большое зло!), тогда можно быстро и просто добавлять новые сплавы и т.п.
3. добавить скрытый столбец к исходной таблице и применить в расчетах ВПР (определение номера столбца в ВПР выполняет ПОИСКПОЗ)
4. добавить в формулу проверку на незаполненные данные (ЕСЛИ, ИЛИ, ЕПУСТО) и проверку на наличие в исходной таблице запрашиваемых параметров (ЕСЛИОШИБКА)
Код
=ЕСЛИ(ИЛИ(ЕПУСТО(B2);ЕПУСТО(B3);ЕПУСТО(B4);ЕПУСТО(B5));"";ЕСЛИОШИБКА(ВПР(B2&B3&B4&B5;таблица;ПОИСКПОЗ(B6;Материал;0)+5;0);"нет в таблице"))
Удивление есть начало познания © Surprise me!
И да пребудет с нами сила ВПР.
 
 
 
#3
04.12.2019 07:54:09
Возможно Ваше решение лежит вот здесь: https://www.planetaexcel.ru/techniques/1/38/
 
 
 
#4
04.12.2019 08:49:28
наверное я не так понял проблему. ну раз сделал, то выложу. вариант с доп. столбцом строкой
Изменено: artyrH - 04.12.2019 08:57:07
 
 
 
#5
04.12.2019 08:53:09
Ёк-Мок, а если убрать объединённые ячейки и без транспонирования вместо впр использовать гпр?ведь это 2 функции практически одинаковы
 
 
 
#6
04.12.2019 10:04:28
Цитата
edifer5 написал:
если убрать объединённые ячейки
можно и не убирать. только добавить доп. строку
 
 
 
#7
04.12.2019 10:51:48
Приветствую.
Вариант решения задачи без каких либо изменений в данных на листе Лист1.
Есть следующие замечания по файлу:
1. Выпадающие списки лучше всего сразу привязывать к данным из умной таблицы. Все списки вынесла на лист Списки. Если у вас добавляется сплав новый, то просто вносите его в таблицу со сплавами на листе Списки.
2. Сделала на листе Лист2 ряд промежуточных вычислений. Единственно, не удобно, что в таблице с исходными данными Тип инструмента должен идти строго в определенном порядке: сначала Торц, потом Уступ, потом Пазовая. Под это подвязано определение границ данных для поиска.
3. Показала также вариант решения задачи, когда идет ссылка на данные с листа Лист1, пропущенные через Power Query. Эти обработанные данные находятся на листе "Данные с Лист1 в другом виде". Суть в том, что когда вы добавляете данные в таблицу на Лист1, то потом надо два раза нажать Обновить, на листе "Данные с Лист1..." отобразятся новые данные для поиска нужного показателя.
Изменено: turbidgirl - 04.12.2019 10:52:09
 
 
 
#8
04.12.2019 11:35:16
turbidgirl, Спасибо большое,идея с Power Query очень понравилась,пойду осваивать
 
 
 
#9
04.12.2019 11:48:55
Подправила промежуточные вычисления так, чтобы не было строгой подвязки к порядку написания Типов инструментов. Файл в приложении.
По поводу Power Quey: отчет ссылается на именованный диапазон Данные (=Лист1!$A$1:$ACV$100). Если этого диапазона перестанет хватать, то надо будет в Диспетчере имен зайти и выбрать новый диапазон данных.
Изменено: turbidgirl - 04.12.2019 12:26:26
 
 
 
Страницы: 1
Наверх