Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Проверка данных от содержания в другой ячейке - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверка данных от содержания в другой ячейке (Формулы/Formulas)
Проверка данных от содержания в другой ячейке
ovechkin1973 Дата: Воскресенье, 10.09.2017, 20:00 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 121
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Есть файл, на листе которого (лист "хранение") напротив каждого наименования оборудования есть столбец (вид документа) для выбора вида отчетной документации по обслуживанию этого оборудования. Наименование оборудования могут повторяться - разные сроки занесения данных, разные документы по обслуживание и пр. Но сами строки целиком по содержимому будут уникальны, хотя к моему вопросу это отношение навряд ли имеет.
Для каждого оборудования может быть свой список отчетной документации - он перечислен на листе "проверка_данных" напротив каждого наименования, которое на этом листе уникально.
Есть проблемы с проверкой данных:
1.Делаешь в столбце ВИД ДОКУМЕНТА на листе "хранение" проверку данных по списку с листа "проверка_данных" и источник - по формуле. Но если после того, как сделаешь проверку данных на листе "проверка_данных" вставишь новые данные или удалишь что то, то при выборе данных на листе "хранение" выпадающий список не соответствует тому, что на листе "проверка_данных". Например - удалите на листе "проверка_данных" для электродвигателя №6 акт технического состояния и тогда на листе "хранение" в выпадающем меню останется только аварийный акт;
2. Еще проблема в том, что если на листе "хранение" вид документа писать вручную, то проверяет нормально (до внесения изменений на лист "проверка_данных" после того, как настроишь эту проверку). А вот если вставлять данные копированием, то почему то позволяет вставить любую чушую... почему - не пойму;
3. Перечень для каждого оборудования будет свой, но последовательность, которая указана на листе "проверка_данных" для всех одинакова. Т.е. для кого то оборудования будет все виды отчетной документации использоваться (их в примере 7, но в реалии может быть и больше), а для какого то не все. Когда выбираешь на листе "хранение" и выпадающего списка документ очень не удобно, если он находиться где то в конце (в крайних столбцах) листа "проверка_данных". Хотелось бы, чтобы в выпадающем меню пустые ячейки не учитывались и отображались только документы, которые надо выбрать;
4. Как сделать, чтобы при внесении вида документа вручную на листе "хранение" предлагались варианты указанные для этого оборудования на листе "проверка_данных". Т.е если в своем случае набираю вручную "деф.....", то предлагалось слово ДЕФЕКТОВКА, но только для того оборудования, для которого оно есть на листе "проверка_данных".. прошу прощения за повтор.
К сообщению приложен файл: 1933119.xlsx(11Kb)
 
Ответить
СообщениеЕсть файл, на листе которого (лист "хранение") напротив каждого наименования оборудования есть столбец (вид документа) для выбора вида отчетной документации по обслуживанию этого оборудования. Наименование оборудования могут повторяться - разные сроки занесения данных, разные документы по обслуживание и пр. Но сами строки целиком по содержимому будут уникальны, хотя к моему вопросу это отношение навряд ли имеет.
Для каждого оборудования может быть свой список отчетной документации - он перечислен на листе "проверка_данных" напротив каждого наименования, которое на этом листе уникально.
Есть проблемы с проверкой данных:
1.Делаешь в столбце ВИД ДОКУМЕНТА на листе "хранение" проверку данных по списку с листа "проверка_данных" и источник - по формуле. Но если после того, как сделаешь проверку данных на листе "проверка_данных" вставишь новые данные или удалишь что то, то при выборе данных на листе "хранение" выпадающий список не соответствует тому, что на листе "проверка_данных". Например - удалите на листе "проверка_данных" для электродвигателя №6 акт технического состояния и тогда на листе "хранение" в выпадающем меню останется только аварийный акт;
2. Еще проблема в том, что если на листе "хранение" вид документа писать вручную, то проверяет нормально (до внесения изменений на лист "проверка_данных" после того, как настроишь эту проверку). А вот если вставлять данные копированием, то почему то позволяет вставить любую чушую... почему - не пойму;
3. Перечень для каждого оборудования будет свой, но последовательность, которая указана на листе "проверка_данных" для всех одинакова. Т.е. для кого то оборудования будет все виды отчетной документации использоваться (их в примере 7, но в реалии может быть и больше), а для какого то не все. Когда выбираешь на листе "хранение" и выпадающего списка документ очень не удобно, если он находиться где то в конце (в крайних столбцах) листа "проверка_данных". Хотелось бы, чтобы в выпадающем меню пустые ячейки не учитывались и отображались только документы, которые надо выбрать;
4. Как сделать, чтобы при внесении вида документа вручную на листе "хранение" предлагались варианты указанные для этого оборудования на листе "проверка_данных". Т.е если в своем случае набираю вручную "деф.....", то предлагалось слово ДЕФЕКТОВКА, но только для того оборудования, для которого оно есть на листе "проверка_данных".. прошу прощения за повтор.

Автор - ovechkin1973
Дата добавления - 10.09.2017 в 20:00
_Igor_61 Дата: Воскресенье, 10.09.2017, 21:44 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 154
Репутация: 25 ±
Замечаний: 0% ±

Excel 2007
Через танцы с бубном, но нужные Вам списки по-моему получились. Для упрощения восприятия промежуточные таблицы сделал на двух листах ("1" и "2")
К сообщению приложен файл: 1933119_2.xlsx(17Kb)
 
Ответить
СообщениеЧерез танцы с бубном, но нужные Вам списки по-моему получились. Для упрощения восприятия промежуточные таблицы сделал на двух листах ("1" и "2")

Автор - _Igor_61
Дата добавления - 10.09.2017 в 21:44
ovechkin1973 Дата: Понедельник, 11.09.2017, 10:08 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 121
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Igor_61, на первый взгляд получилось! Буду разбираться. Вопросы наверняка появятся
 
Ответить
Сообщение_Igor_61, на первый взгляд получилось! Буду разбираться. Вопросы наверняка появятся

Автор - ovechkin1973
Дата добавления - 11.09.2017 в 10:08
ovechkin1973 Дата: Среда, 13.09.2017, 20:38 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 121
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Igor_61, на первый взгляд получилось! Буду разбираться. Вопросы наверняка появятся

Игорь, все работает! чудеса!
Еще вопрос - можно, чтобы в выпадающем меня по алфавиту были документы, но на листе "проверка данных" где эти документы берутся они остаются как сейчас есть?
 
Ответить
Сообщение
_Igor_61, на первый взгляд получилось! Буду разбираться. Вопросы наверняка появятся

Игорь, все работает! чудеса!
Еще вопрос - можно, чтобы в выпадающем меня по алфавиту были документы, но на листе "проверка данных" где эти документы берутся они остаются как сейчас есть?

Автор - ovechkin1973
Дата добавления - 13.09.2017 в 20:38
_Igor_61 Дата: Среда, 13.09.2017, 23:12 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 154
Репутация: 25 ±
Замечаний: 0% ±

Excel 2007
Формулами много промежуточных действий придется делать, поэтому наверное лучше делать отдельный лист для сортировки, на него макросом копировать исходную таблицу и так же макросом сортировать каждую строку, и уже после сортировки с этого листа вытягивать полученные отсортированные списки. Но это я уже не смогу сделать - маловато знаний в VBA. И если уже связываться с макросами, то тогда вообще все наверное логично тоже макросами делать, без формул. Может кто-нибудь из более опытных сможет и формулами это сделать, не знаю.
 
Ответить
СообщениеФормулами много промежуточных действий придется делать, поэтому наверное лучше делать отдельный лист для сортировки, на него макросом копировать исходную таблицу и так же макросом сортировать каждую строку, и уже после сортировки с этого листа вытягивать полученные отсортированные списки. Но это я уже не смогу сделать - маловато знаний в VBA. И если уже связываться с макросами, то тогда вообще все наверное логично тоже макросами делать, без формул. Может кто-нибудь из более опытных сможет и формулами это сделать, не знаю.

Автор - _Igor_61
Дата добавления - 13.09.2017 в 23:12
_Boroda_ Дата: Четверг, 14.09.2017, 00:42 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11327
Репутация: 4676 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вариант с дополнительной табличкой и именованным диапазоном (Контрл F3)
К сообщению приложен файл: 1933119_1.xlsx(13Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВариант с дополнительной табличкой и именованным диапазоном (Контрл F3)

Автор - _Boroda_
Дата добавления - 14.09.2017 в 00:42
_Igor_61 Дата: Четверг, 14.09.2017, 19:51 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 154
Репутация: 25 ±
Замечаний: 0% ±

Excel 2007
Может кто-нибудь из более опытных сможет

Да, и еще как! _Boroda_, Вы в очередной раз поражаете своими решениями. Пока не понял до конца, как это работает, (почему работает "ПРОСМОТР" без первого аргумента, и как формируется вектор просмотра - т.е. -1/СЧЁТЕСЛИ - как и почему это работает???!!!). А именованный диапазон? Там один ИНДЕКС на другой делится - для меня пока это все непонятно, буду вникать. Положу этот способ себе в копилку, может со временем получится понять принцип работы этих формул в таком виде и в таких связках. Спасибо за мастер-класс!
 
Ответить
Сообщение
Может кто-нибудь из более опытных сможет

Да, и еще как! _Boroda_, Вы в очередной раз поражаете своими решениями. Пока не понял до конца, как это работает, (почему работает "ПРОСМОТР" без первого аргумента, и как формируется вектор просмотра - т.е. -1/СЧЁТЕСЛИ - как и почему это работает???!!!). А именованный диапазон? Там один ИНДЕКС на другой делится - для меня пока это все непонятно, буду вникать. Положу этот способ себе в копилку, может со временем получится понять принцип работы этих формул в таком виде и в таких связках. Спасибо за мастер-класс!

Автор - _Igor_61
Дата добавления - 14.09.2017 в 19:51
buchlotnik Дата: Четверг, 14.09.2017, 20:24 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3004
Репутация: 839 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
без первого аргумента
- да с первым, просто если отсутствует, значит равен нулюй;
Цитата
-1/СЧЁТЕСЛИ
- отрицательное число делим на условия, если условия выполняются - выходит делим на единицу, если не выполняются - делим на ноль и получаем ошибку - на выходе получаем массив из ошибок и отрицательных чисел, а ищем ноль - просмотр выдаст соответствие по последнему отрицательному числу. Аналогично можно
Код
ПРОСМОТР(2;1/СЧЁТЕСЛИ...


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Четверг, 14.09.2017, 20:26
 
Ответить
Сообщение
Цитата
без первого аргумента
- да с первым, просто если отсутствует, значит равен нулюй;
Цитата
-1/СЧЁТЕСЛИ
- отрицательное число делим на условия, если условия выполняются - выходит делим на единицу, если не выполняются - делим на ноль и получаем ошибку - на выходе получаем массив из ошибок и отрицательных чисел, а ищем ноль - просмотр выдаст соответствие по последнему отрицательному числу. Аналогично можно
Код
ПРОСМОТР(2;1/СЧЁТЕСЛИ...

Автор - buchlotnik
Дата добавления - 14.09.2017 в 20:24
_Igor_61 Дата: Четверг, 14.09.2017, 22:12 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 154
Репутация: 25 ±
Замечаний: 0% ±

Excel 2007
Да, когда знаешь - все просто! :) buchlotnik, спасибо за пояснения!
 
Ответить
СообщениеДа, когда знаешь - все просто! :) buchlotnik, спасибо за пояснения!

Автор - _Igor_61
Дата добавления - 14.09.2017 в 22:12
_Boroda_ Дата: Четверг, 14.09.2017, 22:13 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11327
Репутация: 4676 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
_Igor_61, спасибо на добром слове
Про ПРОСМОТР я писал объяснялку вот здесь http://www.excelworld.ru/forum/2-16573-138042-16-1427281717

По поводу именованного диапазона - там формула страшная, но достаточно шустрая. Ее можно написать и короче, но далеко не факт, что короткая запись будет работать быстрее
Код
=ИНДЕКС(проверка_данных!$K$2:$K$21;ПОИСКПОЗ(Хранение!$A31;проверка_данных!$A$2:$A$21;)):ИНДЕКС(проверка_данных!$K$2:$Q$21;ПОИСКПОЗ(Хранение!$A31;проверка_данных!$A$2:$A$21;);ИНДЕКС(проверка_данных!$J$2:$J$21;ПОИСКПОЗ(Хранение!$A31;проверка_данных!$A$2:$A$21;)))

Вот это
один ИНДЕКС на другой делится
не совсем так. Двоеточие - это не знак деления в 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_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение_Igor_61, спасибо на добром слове
Про ПРОСМОТР я писал объяснялку вот здесь http://www.excelworld.ru/forum/2-16573-138042-16-1427281717

По поводу именованного диапазона - там формула страшная, но достаточно шустрая. Ее можно написать и короче, но далеко не факт, что короткая запись будет работать быстрее
Код
=ИНДЕКС(проверка_данных!$K$2:$K$21;ПОИСКПОЗ(Хранение!$A31;проверка_данных!$A$2:$A$21;)):ИНДЕКС(проверка_данных!$K$2:$Q$21;ПОИСКПОЗ(Хранение!$A31;проверка_данных!$A$2:$A$21;);ИНДЕКС(проверка_данных!$J$2:$J$21;ПОИСКПОЗ(Хранение!$A31;проверка_данных!$A$2:$A$21;)))

Вот это
один ИНДЕКС на другой делится
не совсем так. Двоеточие - это не знак деления в 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_
Дата добавления - 14.09.2017 в 22:13
_Igor_61 Дата: Пятница, 15.09.2017, 07:15 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 154
Репутация: 25 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, спасибо! Надеюсь, теперь смогу разобраться и понять
 
Ответить
Сообщение_Boroda_, спасибо! Надеюсь, теперь смогу разобраться и понять

Автор - _Igor_61
Дата добавления - 15.09.2017 в 07:15
ovechkin1973 Дата: Суббота, 16.09.2017, 11:16 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 121
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Вариант с дополнительной табличкой и именованным диапазоном (Контрл F3)


Не могу понять, как вы проверку данных по списку сделали. В источнике написано "=вып".. что это значит? Моих знаний только формулы применить аналогично хватило.
 
Ответить
Сообщение
Вариант с дополнительной табличкой и именованным диапазоном (Контрл F3)


Не могу понять, как вы проверку данных по списку сделали. В источнике написано "=вып".. что это значит? Моих знаний только формулы применить аналогично хватило.

Автор - ovechkin1973
Дата добавления - 16.09.2017 в 11:16
buchlotnik Дата: Суббота, 16.09.2017, 11:29 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3004
Репутация: 839 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
что это значит?
Ctrl+F3 - это именованный диапазон


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru
 
Ответить
Сообщение
Цитата
что это значит?
Ctrl+F3 - это именованный диапазон

Автор - buchlotnik
Дата добавления - 16.09.2017 в 11:29
ovechkin1973 Дата: Суббота, 16.09.2017, 11:48 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 121
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
мда.. нужно другую тему изучить, перед тем, как этой заниматься
 
Ответить
Сообщениемда.. нужно другую тему изучить, перед тем, как этой заниматься

Автор - ovechkin1973
Дата добавления - 16.09.2017 в 11:48
_Igor_61 Дата: Суббота, 16.09.2017, 19:01 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 154
Репутация: 25 ±
Замечаний: 0% ±

Excel 2007
ovechkin1973, почитайте про имена (их можно присваивать диапазонам и формулам). В Вашем случае диапазону присвоено имя и его границы определяются формулой (Ctrl+F3 - в этом окне в нижнем поле эта формула)
 
Ответить
Сообщениеovechkin1973, почитайте про имена (их можно присваивать диапазонам и формулам). В Вашем случае диапазону присвоено имя и его границы определяются формулой (Ctrl+F3 - в этом окне в нижнем поле эта формула)

Автор - _Igor_61
Дата добавления - 16.09.2017 в 19:01
ovechkin1973 Дата: Воскресенье, 17.09.2017, 18:21 | Сообщение № 16
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 121
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
ovechkin1973, почитайте про имена

Про именованные диапазоны читал, но приспособить вариант Бороды (как в принципе и Ваш) к своему файлу без помощи сына не смог.. Вариант Бороды при сохранении файла весит значительно меньше и в выпадающем меню имена предлагает по алфавиту.
Большое человеческое всем спасибо за помощь!
 
Ответить
Сообщение
ovechkin1973, почитайте про имена

Про именованные диапазоны читал, но приспособить вариант Бороды (как в принципе и Ваш) к своему файлу без помощи сына не смог.. Вариант Бороды при сохранении файла весит значительно меньше и в выпадающем меню имена предлагает по алфавиту.
Большое человеческое всем спасибо за помощь!

Автор - ovechkin1973
Дата добавления - 17.09.2017 в 18:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверка данных от содержания в другой ячейке (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!