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

Получил наводящий ответ с Киберфорума. Данная формула проверяет вручную указанный диапазон с/быт услуг матери $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")

P.S. Также моя тема на других форумах
Форум программистов
Мир MS Excel
Киберфорум
Изменено: Wrascon - 02.05.2018 12:57:12
 
Офигеть можно!
Посмотрел перечень "услуг", за которые эти "соцработники" бабло рубят :)
И каждый день новый РАЬОТНИК! :)
Они лично, чтоли каждый день выгоняют  из помещения и снова загоняют постояльца?
Никаких слов нет.
Умножайте всё на - 50.
Пусть еще платят, что жопы просиживают.
Пред   площади
Пред   помещений для организайии реабилитации
Пред мебели
Предос   питания
Обеспеч   мягким инвентарём
Уборка жилого   помещения
Предост   транспорта
Оказание   прачечных услуг
Обеспеч   сохранности вещей
 
Конкретно в моём случае есть 4 специалиста, которые целые сутки и появляются на работе 7 раз за месяц. Им приходится не только следить за порядком, решать семейные проблемы и зачастую быть даже воспитателем, но под конец рабочего дня всего 2 месяца назад их основным якорем была тонна макулатуры и вычисления на калькуляторе без какого либо компьютера. С их появлением они стали клепать себе несвязанные таблицы без каких либо формул и всё также вручную как на бумаге. Лишь половина из них подсуетились и добавили первую функцию СУММ. За месяц таблицы семей приобрели хоть какой-то общий вид, но вторая половина стала лезть в форматирование и изменение формул на свои цифры. Тогда я сделал им боковую таблицу, которая искала специалиста в общем списке месяца и если тот проставлял услугу суммировал её.
Код
=СУММЕСЛИМН($C14:$AG14;$C$3:$AG$3;AJ$3)
Оно считало всё верно до тех пор, пока перестали совпадать имена. Оказалось они банально стёрли инициалы. И ведь действительно. Будь вся эта система табелей в каком-то подобии 1С Предприятии не было бы путаницы в отчётах и непозволительных исправлений со стороны нижнего персонала. Даже на таком деньги экономят, а те по прежнему ловят палки в колёса. Поставили всех на слуху о возможном внедрении УСОН, но до самой системы дело не дошло.
Приходится своими знаниями упрощать им работу, но они ставят такие условия, что я сам не настолько могу в программирование автоматического распознавания таблицы, чтобы та разворачивала все данные на новом листе по одному указанию ячейки. Было лишь предположение, что это сводная таблица, посмотрел и восхитился силе формул с этого видео. Думаю, когда-нибудь получится сделать нечто подобное.
Изменено: Wrascon - 14.04.2018 20:36:18
 
Кросс
 
Wrascon, нехорошо... Размещаете тему на нескольких форумах - информируйте об этом.
 
Юрий М, добавил ссылки в конце поста
 
По листу "Семейный отчёт"
Код
=СУММЕСЛИМН('Семья с 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)
 
Изменил файл пример как устроена таблица. Сам вопрос касается только последних двух листов с отчётами
 
зачем столько лишней информации в файле с кучей примечаний с таким примером долго будете ждать помощи
Лень двигатель прогресса, доказано!!!
 
Сергей, Что для вас есть пример? Для меня лист с одной полной семьёй, срочная семья и пару листов с отчётами, которые ссылаются на оба предыдущих листа. Я не могу перефразировать таблицу не потеряв связь ячеек для формул, где оказание услуги детей отличается от взрослых отсутствием целых категорий. Что мне стоит оставить в ней, если все ячейки указываются вручную, а не автоматически при нахождении Мать - с/быт - итого, Мать - с/пед - итого и так же с детьми. Формула не перетаскивается вниз и её по прежнему приходится корректировать для каждой строчки отдельно.
Если под примером подразумевается шаблон как должно выглядеть в итоге, мне неминуемо придётся указывать все эти моменты в примечаниях. Уж поверьте, тоже непонимание происходило при виде таблицы с вручную забитыми цифрами без формул.
 
Ещё раз здравствуйте. Большинство проблем успел решить, но близится конец месяца и остался один нерешённый вопрос с перетаскиванием вниз функции по всем услугам.
В "Семейном отчёте" происходит подсчёт услуг отдельной семьи, указанной в ячейке A2. Также к нему привязаны столбцы Мать + Рёбёнок (от их кол-ва) и все типы услуг по с/быт с/мед с/пед с/труд с/комм с/прав. При объединении полного перечня услуг отметил зачёркнутыми те, которые не оказываются взрослым или детям.
Код
=СУММЕСЛИМН(ДВССЫЛ("'"&$A$2&"'!$C5:$AG5");ДВССЫЛ("'"&$A$2&"'!$C$3:$AG$3");C$3)
Из-за двойной ссылки перестали действовать $ в выделенных диапазонах, а каждую строчку забивать вручную довольно проблематично. Пока я не знаю как это переделать хотя бы так. В идеале проверка пересечений Специалист - Смена - Услуга должна перепроверять сам номер услуги и определять её нахождение конкретно для взрослого или ребёнка.
Обновил файл пример, и заполнил лишь первый столбец услуг.
 
Неужели так трудно подправить эту формулу на изменение строк при растягивании вниз? Даже в Коде формулы подсвечиваются диапазоны ячеек в ДВССЫЛ, только не верно. Я всего неделю знаком с ним, а про моё кривое понимание выделения "текста" кавычками на 'листе' и объединение & их с ячейками !$C5:$AG5 оставляет желать лучшего.
 
Код
=СУММЕСЛИМН(ДВССЫЛ("'"&$A$2&"'!$C5:$AG5");ДВССЫЛ("'"&$A$2&"'!$C$3:$AG$3");C$3)
По-прежнему нужно исправить ссылку на растягивание вниз по "название листа в А2" + диапазон ячеек  "!$C5:$AG5"
 
Wrascon, для чего вы еще раз суммируете? если на каждом листе есть AJ-AN... чтобы формулу растянуть, нужно чтобы она находила позицию "Наименование услуги"
т.е чтобы формула(вроде MATCH) для 4.5. нашла номер строки в Фамилия И.О. (1) и передала в диапазон $C:$AG номер 17

Код
=СУММЕСЛИМН(ДВССЫЛ("'"&$A$2&"'!$C17:$AG17");ДВССЫЛ("'"&$A$2&"'!$C$3:$AG$3");C$3)
Опыт и практика - великое дело! Век живи, Век учись!
 
sanych09, не заметил никаких изменений в формуле, она не растягивается вниз. Я не ещё раз суммирую, а делаю отдельный лист с отчётом, который выводит список всей семьи по указанной на неё ячейке. Нужна перепроверка данных, чем просто ссылаться на боковые услуги по специалистам. Ещё не известно будет ли меняться перечень услуг, но появись новая строка и все значения сдвинуться на неверные. Печатается отчёт, фамилия меняется на другую семью и все ячейки изменяются на подсчёты значений её услуг. Когда приходят новые семьи, проще подогнать формулу к изменению под нужную семью, чем копировать лист на каждую семью отдельно.
 
Всё ещё нужна формула, если не с проверкой на услуги, то хотя бы эта же с возможностью перетаскивать вниз по строкам. Вправо она и так растягивается
Изменено: Wrascon - 01.05.2018 11:28:04
 
Wrascon, это же не кретическое замечание чтобы формулы копировались. Как вариант, добавьте в формулу что-то вроде ВПР(VLOOKUP) по Наименование услуги для нужноого диапазона Мать, Ребенок 1 и т.д
или же за время которое вы ждете ответа, можно было б руками все формулы изменить
Опыт и практика - великое дело! Век живи, Век учись!
 
sanych09, с моими попытками торопливо освоить комбинацию Поиск и ВПР формула пропускала значение, содержащее "Мать: " и указывала чуть ли не на одну из услуг второго ребёнка, содержимое которого также не имело ничего общего с искомым вопросом. Раньше я знал, что по обычному алгоритму наличия пустых ячеек через определённое число строк и повторное нахождение пустой, можно определить границы таблицы. В моём понимании такое логическое условие куда легче спрограммировать, чем пытаться повторить то же в Excel на условиях, потому я и прошу банального адаптирования той же функции к возможности растягиваться вниз для дальнейшего заполнения отчёта, а не что-то замудренное автоматическое определение, как задумывалось ранее.

А теперь о попытках разобраться с Поиском и ВПР.
Код
=ПОИСК("Мать: ";C2) - выдаёт единичку "1", видимо первый символ, мне же нужно вывести содержимое всей ячейки из диапазона поиска по всему столбцу.
Код
=ПОИСКПОЗ("Мать";ДВССЫЛ("'"&$A$2&"'!A:A")) - ищу мать через ячейку, указывающую на каком листе искать, выдаёт "91"
Код
=ВПР("Мать";'Семья с 5'!A:A;1;1) - далее смотрю он подразумевает под этим числом, выдаёт услугу "Выполнение процедур по сохранению здоровья" первого ребёнка на 52 стоке
Код
=ЯЧЕЙКА("содержимое";ДВССЫЛ("'"&$A$2&"'!A;ПОИСКПОЗ("Мать";ДВССЫЛ("'"&$A$2&"'!A:A")))) - ещё одна дичь с поиском содержимого ячейки с матерью, не работает
Код
=СУММЕСЛИМН('{:}'!$C5:$AG5;'{:}'!$C$3:$AG$3;C$3) - тоже хотел просуммировать услуги специалиста со всех листов семей, вручную ограниченными листами "{" и "}"
А теперь посмотрите на всё вышеперечисленное и оцените как у меня всё плохо с пониманием поиска в данной программе. Пример файла этого безумия оставлять не буду. Повторюсь  Если тот же СУММЕСЛИМН требует сверять пару диапазонов на один критерий, а этот диапазон включает в себя ссылку на другой лист, то ДВССЫЛ не хочет правильно им восприниматься и реагировать содержимому на растягивание формулы вниз. Получается проблема либо в ДВССЫЛ, либо неправильном указании на лист через дополнительную ячейку. Настройка диапазона через доллар ДВССЫЛ("'"&$A$2&"'!$C5:$AG5") теряет свою задачу менять значения вниз по строкам
 
Изменил формулу и последний пример, остаётся вопрос с нахождением ФИО ячеек Мать и Ребёнок и добавлением массива поиска по типам услуг каждого члена семьи
Изменено: Wrascon - 02.05.2018 12:40:15
Страницы: 1
Наверх