Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 14.04.2018, 16:29   #1
Wrascon
 
Аватар для Wrascon
 
Регистрация: 03.05.2014
Сообщений: 5
По умолчанию Подсчёт соц.услуг специалистов для семей по категориям

Здравствуйте. Недавно помогал центру с переходом от бумажного в цифровой учёт табелей услуг. Производится пересчёт оказанных услуг 4 специалистов матерям и детям в нескольких категориях. Вёл услуги по специалистам для каждой семьи. Ближе к концу месяца по этим данным понадобилось сделать два листа с отчётами: один общий по услугам матерей и детей, другой отдельно для каждой семьи.
В общем отчёте суммируется диапазон (листы {:}) постоянных семей по дополнительным таблицам и формула всегда привязывается к одной ячейке. Из-за этого после добавления новой услуги таблица сместится и будет выдавать неверный результат.
Для срочной семьи подсчитывает только по её собственному листу. Листы всегда имеют фамилию и инициалы семьи, у меня не удаётся сделать привязку формулы к ячейки с названием листа.
Заблокировал на всех листах ячейки с формулами, серые ячейки доступны для редактирования.
Возник вопрос как с помощью ячейки, указывающей на лист семьи сделать вывод списка членов семьи и провести подсчёт услуг для них каждым специалистом?

Таблица на яндекс диске https://yadi.sk/d/qX69iLk33UQgiQ

P.S. Также моя тема на других форумах
Мир MS Excel
Планета Excel
Киберфорум
Вложения
Тип файла: xls ТАБЕЛЬ УЧЕТА УСЛУГ АПРЕЛЬ 2018.xls (168.1 Кб, 16 просмотров)

Последний раз редактировалось Wrascon; 14.04.2018 в 21:02.
Wrascon вне форума Ответить с цитированием
Старый 15.04.2018, 16:40   #2
Wrascon
 
Аватар для Wrascon
 
Регистрация: 03.05.2014
Сообщений: 5
По умолчанию

По листу "Семейный отчёт"
Код:
=СУММЕСЛИМН('Семья с 1'!C5:AG5;'Семья с 1'!C3:AG3;C$3)
Как будет правильнее разбить эту формулу для пересчёта через ячуйку, указывающую на отдельную семью?
Пробовал следующую, где $A$2 название листа нужной семьи, C5:AG5 месячный диапазон услуги, C3:AG3 месячная смена специалистов, C$3 специалист, по которому нужно провести подсчёты.
Код:
=СУММЕСЛИМН(ДВССЫЛ($A$2&"!"&C5:AG5);ДВССЫЛ($A$2&"!"&C3:AG3);C$3)
Помимо этой функции мне нужно узнать как вывести список семьи по ячейкам "Мать:", "Ребёнок 1:", "Ребёнок 2: ", "Ребёнок 3: ", "Ребёнок 4: ", "Ребёнок 5: "

По листу "Общий отчёт"
Единственное меня не покидает опасение, что формула по всем листам указанного диапазона "{:}" не адаптируется под изменения в таблице. Любое изменение даже во всех листах оставит операцию на той же самой ячейке
Код:
=СУММ('{:}'!AJ14)
У детей всё куда хуже. После добавления новой услуги все ячейки придётся указывать вручную для каждого ребёнка снова
Код:
=СУММ('{:}'!AJ49;'{:}'!AJ88;'{:}'!AJ127;'{:}'!AJ166;'{:}'!AJ205)
Wrascon вне форума Ответить с цитированием
Старый 26.04.2018, 11:38   #3
Wrascon
 
Аватар для Wrascon
 
Регистрация: 03.05.2014
Сообщений: 5
По умолчанию

Код:
=СУММЕСЛИМН(ДВССЫЛ("'"&$A$2&"'!$C5:$AG5");ДВССЫЛ("'"&$A$2&"'!$C$3:$AG$3");C$3)
Из-за двойной ссылки перестали действовать $ в выделенных диапазонах, а каждую строчку забивать вручную довольно проблематично. Пока я не знаю как это переделать хотя бы так. В идеале проверка пересечений Специалист - Смена - Услуга должна перепроверять сам номер услуги и определять её нахождение конкретно для взрослого или ребёнка.
Обновил файл пример, и заполнил лишь первый столбец услуг.

Последний раз редактировалось Wrascon; 26.04.2018 в 16:38.
Wrascon вне форума Ответить с цитированием
Старый 01.05.2018, 11:27   #4
Wrascon
 
Аватар для Wrascon
 
Регистрация: 03.05.2014
Сообщений: 5
По умолчанию

Всё ещё нужна формула, если не с проверкой на услуги, то хотя бы эта же с возможностью перетаскивать вниз по строкам. Вправо она и так растягивается
Wrascon вне форума Ответить с цитированием
Старый 02.05.2018, 12:40   #5
Wrascon
 
Аватар для Wrascon
 
Регистрация: 03.05.2014
Сообщений: 5
По умолчанию

Данная формула проверяет вручную указанный диапазон с/быт услуг матери $C$5:$AG$13 и ищет совпадение услуг 1 специалиста, начиная с 1.1. и заканчивая 1.15. Какой массив проверки нужно подключить, чтобы находился диапазон матери или ребёнка по с/быт, с/мед,с/пед, с/труд, с/комм, с/прав типам услуг?
Код:
=СУММПРОИЗВ(ДВССЫЛ("'"&$A$2&"'!$C$5:$AG$13")*(ДВССЫЛ("'"&$A$2&"'!$B5:$B13")=$B5)*(ДВССЫЛ("'"&$A$2&"'!C$3:AG$3")=C$3))
Сейчас имена членов семьи выводятся без интуитивного поиска по указанным ячейкам на условиях, где в обеих ДВССЫЛ A2 меняется на A39, A78, A117,A156, A195 смотря сколько человек в семье.
Код:
=ЕСЛИОШИБКА(ЕСЛИ(A$2="";"Укажите название семьи в ячейку A2";ЕСЛИ(ДВССЫЛ("'"&$A$2&"'!A2")="";"";ДВССЫЛ("'"&$A$2&"'!A2")));"Такая семья отсутствует. Проверьте A2")
Wrascon вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Как добавить лайки для соц. сетей для своего сайта IT-Lopata HTML и CSS 2 09.04.2014 23:33