Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Создание таблицы учёта нарушений
 
#1
17.01.2026 14:28:39
Доброго времени суток!
Долгое время пользуюсь подсказками экспертов на этом сайте в режиме чтения, за что очень признателен.
Возникла необходимость обратиться за помощью.

В прилагаемом файле 2 листа:
Реестр - где будут учитываться нарушения
Типы нарушений - где будут формировать данные для Реестра.

Суть в том, что в Типах нарушений для каждого нарушения будут указываться какие пункты какого документа были нарушены. Дальше, при выборе типа нарушения в листе Реестр, необходимо, чтобы в соседнем стоблце сфомировался список (желательно с разделителем CHAR(10)) документов и пунктов, которые были нарушены.
Использую Excel 2016/2024.

Коллеги, помогите реализовать это.
Благодарю!
 
 
 
#2
17.01.2026 17:55:47
messire, Добрый вечер. Вы покажите на вашем примере как это должно быть в результате.
И вдогонку вопросы:
- есть то, что называется "Документ" с нумерацией, и сколько их таких?
- в каждом документе есть несколько  пунктов, в приложенном примере их 3, это всегда так?
 
 
 
 
#3
17.01.2026 18:02:50
Добрый вечер!

Суть в том, что нужно сформировать перечень нарушений, для каждого из которых будут указаны документы и пункты этих документов. Это нужно для дальнейшего учёта при проведении служебных проверок. То есть, для субъекта (будет указываться его ФИО) выбирается тип нарушения, а в соседнем столбце подтягивается список нормативных документов, которые были нарушены. То есть, условно, опоздание на работу: ПВТР, п.3.14, Трудовой договор п.1.6.

Документ - это нормативные документы. Их может быть сколько угодно.
В каждом документе есть много пунктов и их количество, нумерация в каждом документе отличаются. В таблице просто указывается наименование документа и пункты этого документа, которые были нарушены в связи с допущенным нарушением.

Благодарю.
 
 
 
#4
17.01.2026 18:50:28
Цитата
messire написал:
Коллеги, помогите реализовать это.
Здравствуйте. См.вложение.
Изменено: asesja - 17.01.2026 18:50:45
 
 
 
#5
17.01.2026 19:12:03
asesja, это великолепно! Вопрос: можно ли сделать так, что если список пунктов и документов изменяется, это повлечёт актоматическое изменение в листе Реестр?
То есть, Нарушение 1 - было: п.1, п.3. Поменяли: стало - п.2, п.4. Соответственно, на листе Реестр автоматически произойдёт замена содержимого для данного типа (Нарушение 1).

Благодарю.
 
 
 
#6
17.01.2026 19:26:41
Цитата
messire написал:
Вопрос: можно ли сделать так, что если список пунктов и документов изменяется, это повлечёт автоматическое изменение в листе Реестр?
Да, теоретически можно сделать по событию изменения данных в определённом диапазоне на листе "Типы нарушений", но думаю, с увеличением базы, это будет чуть притормаживать работу.
На мой взгляд, лучше создать кнопку "ОБНОВИТЬ ДАННЫЕ" на листе "Реестр". Но вы сами смотрите как вам удобнее. Можно использовать и другие варианты взаимодействия, к примеру "Смена листа" и прочие события, а также создание "Пользовательской функции UDF", которая будет работать как формула.
Изменено: asesja - 17.01.2026 19:41:05
 
 
 
#7
17.01.2026 21:38:55
Цитата
messire написал:
Вопрос: можно ли сделать так
Можно. См.V2.
Изменено: asesja - 17.01.2026 21:39:21
 
 
 
#8
18.01.2026 10:21:14
такой вариант:
формула 2021+
 
 
 
#9
18.01.2026 12:04:40
Добрый день. Еще вариант формулами, можно добавлять данные по строкам и столбцам, в примере исходная таблица рассчитана на 20 строк и 18 столбцов.  
 
 
 
#10
18.01.2026 12:09:57
asesja, ПавелW, Старичок,
Уважаемые коллеги! Нет слов! Признателен за Ваш труд! Пойду тестировать предложенные варианты.
Скажу честно, мало работал с VBA, поэтому вариант уважаемого Старичок, представляется более универсальным. Единственное, неплохо бы предусмотреть возможность расширения таблицы - автоматическое определение числа строк и столбцов. Не наглею :) Просто, как программер на других языках, пытаюсь сделать результат более универсальным.
Но! Всем уважаемым коллегам, которые помогают, огромная благодарность и пожелания всяческих успехов!
 
 
 
#11
18.01.2026 14:02:07
Цитата
написал:
неплохо бы предусмотреть возможность расширения таблицы
 В моем примере такая возможность есть, вы можете добавлять строки и столбцы в таблицу на листе "Типы нарушений". В данном варианте формулы диапазон ограничен строкой 20 и столбцом R, если этого недостаточно, можно подправить формулу. Там где в диапазонах есть ссылка на столбец R (таких диапазонов 6) прописать нужный вам столбец (например CV , это столбец с номером 100). Так же со строками, в середине формулы есть ссылка на диапазон 'Типы нарушений'!$A$3:$A$20, вместо 20 подставьте нужное вам число строк. По возможности не нужно делать слишком большой запас, в формуле я использовал волатильную функцию СМЕЩ, а она может тормозить пересчет значений при любых изменениях значений.
На всякий случай дам совет - при добавлении столбцов на листе "Типы нарушений" сохраняйте структуру данных, как в примере, то есть в строке 1 должны быть объединенные ячейки, если количество пунктов больше 1.
 
 
 
 
#12
18.01.2026 14:27:57
можно и так:
Скрытый текст
 
 
 
#13
20.01.2026 15:04:44
Цитата
написал:
=LET(_;B2;_а;'Типы нарушений'!A$3:A$4;_б;'Типы нарушений'!B$1:G$1;_в;'Типы нарушений'!B$2:G$2;_г;'Типы нарушений'!B$3:G$4;а_;СТОЛБЕЦ(_б);б_;ФИЛЬТР(_г;_=_а);в_;СЦЕП(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(_б;ЕСЛИ(б_;ПРОСМОТР(а_;а_/(_б<>"");_б)));СИМВОЛ(10)&_б&": ";"")&ЕСЛИ(б_;" "&_в&" (";"")&ЕСЛИ(б_;б_&"),";""));ПСТР(в_;2;ДЛСТР(в_)-2))
Добрый день! Почему-то после этой формулы крашится сам Эксель и формула не считает.
 
 
 
#14
20.01.2026 15:31:07
messire, что значит "крашится"?
собрал формулы в один файл:
 
 
 
#15
21.01.2026 08:21:16
Доброго времени суток, коллеги!
Всем спасибо за помощь!

Теперь в формуле уважаемого Старичок, пытаюсь сделать динамический размер таблицы со списком нарушений.
То есть, кол-во строк в столбце А листа Типы нарушений -- динамическое (ввёл переменной _a в коде ниже).
Кол-во столбцов в строке 2 листа Типы нарушений - динамической и определяется длиной второй строки (там, где указываются конкретные пункты) - ввёл переменными _b1 _b2 в коде ниже. Расположение самой таблицы Типы нарушений не изменилось.
Само значение нарушения из списка лежит на листе Реестр столбец K (начинается с K3).

Попытался сделать формулой:
Скрытый текст

Но формула не сработала - выдаёт пустое значение.
 
 
 
Страницы: 1
Читают тему
Наверх