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

Вход

Регистрация

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

 

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

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

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


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

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

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

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

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


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

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

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

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


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

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

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

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

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

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


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

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

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

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

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

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

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


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

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

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

Автор - _Igor_61
Дата добавления - 14.09.2017 в 22:12
_Boroda_ Дата: Четверг, 14.09.2017, 22:13 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12440
Репутация: 5108 ±
Замечаний: 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 274
Репутация: 47 ±
Замечаний: 20% ±

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

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

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


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


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


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

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

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

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

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


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

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

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

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

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

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


Плохо когда не знаешь, да еще забудешь.
 
Ответить
Сообщение
ovechkin1973, почитайте про имена

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

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

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