Есть файл, на листе которого (лист "хранение") напротив каждого наименования оборудования есть столбец (вид документа) для выбора вида отчетной документации по обслуживанию этого оборудования. Наименование оборудования могут повторяться - разные сроки занесения данных, разные документы по обслуживание и пр. Но сами строки целиком по содержимому будут уникальны, хотя к моему вопросу это отношение навряд ли имеет. Для каждого оборудования может быть свой список отчетной документации - он перечислен на листе "проверка_данных" напротив каждого наименования, которое на этом листе уникально. Есть проблемы с проверкой данных: 1.Делаешь в столбце ВИД ДОКУМЕНТА на листе "хранение" проверку данных по списку с листа "проверка_данных" и источник - по формуле. Но если после того, как сделаешь проверку данных на листе "проверка_данных" вставишь новые данные или удалишь что то, то при выборе данных на листе "хранение" выпадающий список не соответствует тому, что на листе "проверка_данных". Например - удалите на листе "проверка_данных" для электродвигателя №6 акт технического состояния и тогда на листе "хранение" в выпадающем меню останется только аварийный акт; 2. Еще проблема в том, что если на листе "хранение" вид документа писать вручную, то проверяет нормально (до внесения изменений на лист "проверка_данных" после того, как настроишь эту проверку). А вот если вставлять данные копированием, то почему то позволяет вставить любую чушую... почему - не пойму; 3. Перечень для каждого оборудования будет свой, но последовательность, которая указана на листе "проверка_данных" для всех одинакова. Т.е. для кого то оборудования будет все виды отчетной документации использоваться (их в примере 7, но в реалии может быть и больше), а для какого то не все. Когда выбираешь на листе "хранение" и выпадающего списка документ очень не удобно, если он находиться где то в конце (в крайних столбцах) листа "проверка_данных". Хотелось бы, чтобы в выпадающем меню пустые ячейки не учитывались и отображались только документы, которые надо выбрать; 4. Как сделать, чтобы при внесении вида документа вручную на листе "хранение" предлагались варианты указанные для этого оборудования на листе "проверка_данных". Т.е если в своем случае набираю вручную "деф.....", то предлагалось слово ДЕФЕКТОВКА, но только для того оборудования, для которого оно есть на листе "проверка_данных".. прошу прощения за повтор.
Есть файл, на листе которого (лист "хранение") напротив каждого наименования оборудования есть столбец (вид документа) для выбора вида отчетной документации по обслуживанию этого оборудования. Наименование оборудования могут повторяться - разные сроки занесения данных, разные документы по обслуживание и пр. Но сами строки целиком по содержимому будут уникальны, хотя к моему вопросу это отношение навряд ли имеет. Для каждого оборудования может быть свой список отчетной документации - он перечислен на листе "проверка_данных" напротив каждого наименования, которое на этом листе уникально. Есть проблемы с проверкой данных: 1.Делаешь в столбце ВИД ДОКУМЕНТА на листе "хранение" проверку данных по списку с листа "проверка_данных" и источник - по формуле. Но если после того, как сделаешь проверку данных на листе "проверка_данных" вставишь новые данные или удалишь что то, то при выборе данных на листе "хранение" выпадающий список не соответствует тому, что на листе "проверка_данных". Например - удалите на листе "проверка_данных" для электродвигателя №6 акт технического состояния и тогда на листе "хранение" в выпадающем меню останется только аварийный акт; 2. Еще проблема в том, что если на листе "хранение" вид документа писать вручную, то проверяет нормально (до внесения изменений на лист "проверка_данных" после того, как настроишь эту проверку). А вот если вставлять данные копированием, то почему то позволяет вставить любую чушую... почему - не пойму; 3. Перечень для каждого оборудования будет свой, но последовательность, которая указана на листе "проверка_данных" для всех одинакова. Т.е. для кого то оборудования будет все виды отчетной документации использоваться (их в примере 7, но в реалии может быть и больше), а для какого то не все. Когда выбираешь на листе "хранение" и выпадающего списка документ очень не удобно, если он находиться где то в конце (в крайних столбцах) листа "проверка_данных". Хотелось бы, чтобы в выпадающем меню пустые ячейки не учитывались и отображались только документы, которые надо выбрать; 4. Как сделать, чтобы при внесении вида документа вручную на листе "хранение" предлагались варианты указанные для этого оборудования на листе "проверка_данных". Т.е если в своем случае набираю вручную "деф.....", то предлагалось слово ДЕФЕКТОВКА, но только для того оборудования, для которого оно есть на листе "проверка_данных".. прошу прощения за повтор.ovechkin1973
Через танцы с бубном, но нужные Вам списки по-моему получились. Для упрощения восприятия промежуточные таблицы сделал на двух листах ("1" и "2")
Через танцы с бубном, но нужные Вам списки по-моему получились. Для упрощения восприятия промежуточные таблицы сделал на двух листах ("1" и "2")_Igor_61
_Igor_61, на первый взгляд получилось! Буду разбираться. Вопросы наверняка появятся
Игорь, все работает! чудеса! Еще вопрос - можно, чтобы в выпадающем меня по алфавиту были документы, но на листе "проверка данных" где эти документы берутся они остаются как сейчас есть?
_Igor_61, на первый взгляд получилось! Буду разбираться. Вопросы наверняка появятся
Игорь, все работает! чудеса! Еще вопрос - можно, чтобы в выпадающем меня по алфавиту были документы, но на листе "проверка данных" где эти документы берутся они остаются как сейчас есть?ovechkin1973
Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
Формулами много промежуточных действий придется делать, поэтому наверное лучше делать отдельный лист для сортировки, на него макросом копировать исходную таблицу и так же макросом сортировать каждую строку, и уже после сортировки с этого листа вытягивать полученные отсортированные списки. Но это я уже не смогу сделать - маловато знаний в VBA. И если уже связываться с макросами, то тогда вообще все наверное логично тоже макросами делать, без формул. Может кто-нибудь из более опытных сможет и формулами это сделать, не знаю.
Формулами много промежуточных действий придется делать, поэтому наверное лучше делать отдельный лист для сортировки, на него макросом копировать исходную таблицу и так же макросом сортировать каждую строку, и уже после сортировки с этого листа вытягивать полученные отсортированные списки. Но это я уже не смогу сделать - маловато знаний в VBA. И если уже связываться с макросами, то тогда вообще все наверное логично тоже макросами делать, без формул. Может кто-нибудь из более опытных сможет и формулами это сделать, не знаю._Igor_61
Да, и еще как! _Boroda_, Вы в очередной раз поражаете своими решениями. Пока не понял до конца, как это работает, (почему работает "ПРОСМОТР" без первого аргумента, и как формируется вектор просмотра - т.е. -1/СЧЁТЕСЛИ - как и почему это работает???!!!). А именованный диапазон? Там один ИНДЕКС на другой делится - для меня пока это все непонятно, буду вникать. Положу этот способ себе в копилку, может со временем получится понять принцип работы этих формул в таком виде и в таких связках. Спасибо за мастер-класс!
Да, и еще как! _Boroda_, Вы в очередной раз поражаете своими решениями. Пока не понял до конца, как это работает, (почему работает "ПРОСМОТР" без первого аргумента, и как формируется вектор просмотра - т.е. -1/СЧЁТЕСЛИ - как и почему это работает???!!!). А именованный диапазон? Там один ИНДЕКС на другой делится - для меня пока это все непонятно, буду вникать. Положу этот способ себе в копилку, может со временем получится понять принцип работы этих формул в таком виде и в таких связках. Спасибо за мастер-класс!_Igor_61
- да с первым, просто если отсутствует, значит равен нулюй;
Цитата
-1/СЧЁТЕСЛИ
- отрицательное число делим на условия, если условия выполняются - выходит делим на единицу, если не выполняются - делим на ноль и получаем ошибку - на выходе получаем массив из ошибок и отрицательных чисел, а ищем ноль - просмотр выдаст соответствие по последнему отрицательному числу. Аналогично можно
Код
ПРОСМОТР(2;1/СЧЁТЕСЛИ...
Цитата
без первого аргумента
- да с первым, просто если отсутствует, значит равен нулюй;
Цитата
-1/СЧЁТЕСЛИ
- отрицательное число делим на условия, если условия выполняются - выходит делим на единицу, если не выполняются - делим на ноль и получаем ошибку - на выходе получаем массив из ошибок и отрицательных чисел, а ищем ноль - просмотр выдаст соответствие по последнему отрицательному числу. Аналогично можно
По поводу именованного диапазона - там формула страшная, но достаточно шустрая. Ее можно написать и короче, но далеко не факт, что короткая запись будет работать быстрее
не совсем так. Двоеточие - это не знак деления в Excel, это, в данном случае, знак обозначения диапазона. Когда Вы пишете A1:С4, двоеточие показывает, что диапазон с А1 по С4 По поводу самой формулы - совсем подробно расписывать, думаю, не стоит, буду описывать большими блоками и начну, как все нормальные люди, с середины: 1. ИНДЕКС(проверка_данных!$J$2:$J$21;ПОИСКПОЗ(Хранение!$A31;проверка_данных!$A$2:$A$21;)) - связка ИНДЕКС-ПОИСКПОЗ Вам, конечно же, знакома и понятно, что она выводит количество документов из столбца J для соответствующей элекродетали (мы заранее их там посчитали) 2. ИНДЕКС(проверка_данных!$K$2:$Q$21;ПОИСКПОЗ(Хранение!$A25;проверка_данных!$A$2:$A$21;);п.1) - тоже обычный ИНДЕКС по двумерному диапазону. Первый ПОИСКПОЗ дает нам номер строки, а номер столбца мы посчитали в п.1 3. ИНДЕКС(проверка_данных!$K$2:$K$21;ПОИСКПОЗ(Хранение!$A25;проверка_данных!$A$2:$A$21;)) - ищем значение в первом столбце документов для соответствующей электродетали 4. Смотрим справку по ИНДЕКС - "ИНДЕКС ... возвращает значение или ссылку на значение из таблицы или диапазона". Нас сейчас интересует то, что ИНДЕКС не только выводит значение из найденной ячейки, но и одновременно дает и ссылку на нее (примерно как ДВССЫЛ). Другими словами, формула А1:С4 аналогична формуле ИНДЕКС(A:A;1):ИНДЕКС(C:C;3) Исходя из этого, запись п.3:п.2 (на забываем, что п.3 и п.2 - это ИНДЕКСы) равнозначна тому, как если бы мы просто написали обычную ссылку на ячейки. Например, для 3-й записи K4:M4
Если что-то не совсем понятно - пишите, я перепоясню
По поводу именованного диапазона - там формула страшная, но достаточно шустрая. Ее можно написать и короче, но далеко не факт, что короткая запись будет работать быстрее
не совсем так. Двоеточие - это не знак деления в Excel, это, в данном случае, знак обозначения диапазона. Когда Вы пишете A1:С4, двоеточие показывает, что диапазон с А1 по С4 По поводу самой формулы - совсем подробно расписывать, думаю, не стоит, буду описывать большими блоками и начну, как все нормальные люди, с середины: 1. ИНДЕКС(проверка_данных!$J$2:$J$21;ПОИСКПОЗ(Хранение!$A31;проверка_данных!$A$2:$A$21;)) - связка ИНДЕКС-ПОИСКПОЗ Вам, конечно же, знакома и понятно, что она выводит количество документов из столбца J для соответствующей элекродетали (мы заранее их там посчитали) 2. ИНДЕКС(проверка_данных!$K$2:$Q$21;ПОИСКПОЗ(Хранение!$A25;проверка_данных!$A$2:$A$21;);п.1) - тоже обычный ИНДЕКС по двумерному диапазону. Первый ПОИСКПОЗ дает нам номер строки, а номер столбца мы посчитали в п.1 3. ИНДЕКС(проверка_данных!$K$2:$K$21;ПОИСКПОЗ(Хранение!$A25;проверка_данных!$A$2:$A$21;)) - ищем значение в первом столбце документов для соответствующей электродетали 4. Смотрим справку по ИНДЕКС - "ИНДЕКС ... возвращает значение или ссылку на значение из таблицы или диапазона". Нас сейчас интересует то, что ИНДЕКС не только выводит значение из найденной ячейки, но и одновременно дает и ссылку на нее (примерно как ДВССЫЛ). Другими словами, формула А1:С4 аналогична формуле ИНДЕКС(A:A;1):ИНДЕКС(C:C;3) Исходя из этого, запись п.3:п.2 (на забываем, что п.3 и п.2 - это ИНДЕКСы) равнозначна тому, как если бы мы просто написали обычную ссылку на ячейки. Например, для 3-й записи K4:M4
Если что-то не совсем понятно - пишите, я перепоясню_Boroda_
Вариант с дополнительной табличкой и именованным диапазоном (Контрл F3)
Не могу понять, как вы проверку данных по списку сделали. В источнике написано "=вып".. что это значит? Моих знаний только формулы применить аналогично хватило.
Вариант с дополнительной табличкой и именованным диапазоном (Контрл F3)
Не могу понять, как вы проверку данных по списку сделали. В источнике написано "=вып".. что это значит? Моих знаний только формулы применить аналогично хватило.ovechkin1973
Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
ovechkin1973, почитайте про имена (их можно присваивать диапазонам и формулам). В Вашем случае диапазону присвоено имя и его границы определяются формулой (Ctrl+F3 - в этом окне в нижнем поле эта формула)
ovechkin1973, почитайте про имена (их можно присваивать диапазонам и формулам). В Вашем случае диапазону присвоено имя и его границы определяются формулой (Ctrl+F3 - в этом окне в нижнем поле эта формула)_Igor_61
Про именованные диапазоны читал, но приспособить вариант Бороды (как в принципе и Ваш) к своему файлу без помощи сына не смог.. Вариант Бороды при сохранении файла весит значительно меньше и в выпадающем меню имена предлагает по алфавиту. Большое человеческое всем спасибо за помощь!
Про именованные диапазоны читал, но приспособить вариант Бороды (как в принципе и Ваш) к своему файлу без помощи сына не смог.. Вариант Бороды при сохранении файла весит значительно меньше и в выпадающем меню имена предлагает по алфавиту. Большое человеческое всем спасибо за помощь!ovechkin1973
Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
buchlotnik, спасибо за пояснения!
