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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчёт соц.услуг специалистов для семей по категориям - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт соц.услуг специалистов для семей по категориям (Формулы/Formulas)
Подсчёт соц.услуг специалистов для семей по категориям
Wrascon Дата: Суббота, 14.04.2018, 16:29 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

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

Таблица на яндекс диске Ссылка удалена. Нарушение Правил форума

P.S. Также моя тема на других форумах
Форум программистов
Планета Excel
Киберфорум
К сообщению приложен файл: ____2018.xlsb(90.6 Kb)


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

Таблица на яндекс диске Ссылка удалена. Нарушение Правил форума

P.S. Также моя тема на других форумах
Форум программистов
Планета Excel
Киберфорум

Автор - Wrascon
Дата добавления - 14.04.2018 в 16:29
Wrascon Дата: Воскресенье, 15.04.2018, 16:40 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
По листу "Семейный отчёт"
Код
=СУММЕСЛИМН('Семья с 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)
 
Ответить
СообщениеПо листу "Семейный отчёт"
Код
=СУММЕСЛИМН('Семья с 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
Дата добавления - 15.04.2018 в 16:40
Pelena Дата: Воскресенье, 15.04.2018, 19:28 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12833
Репутация: 2827 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Wrascon, файл надо прикладывать к сообщению. Таблицы можно сократить.
И надеюсь там нет конфиденциальных данных


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеWrascon, файл надо прикладывать к сообщению. Таблицы можно сократить.
И надеюсь там нет конфиденциальных данных

Автор - Pelena
Дата добавления - 15.04.2018 в 19:28
Wrascon Дата: Воскресенье, 15.04.2018, 21:22 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Pelena, Извините, не заметил как удалилась ссылка с яндекс диска. На тот момент табица весила 169КБ и я не успел сжать её до 100. Думал смена формата поможет, но та только раздулась до 497КБ.
Если под сокращением таблицы вы имеете ввиду удаление лишних строк со сменой и датой, то это не подойдёт, т.к. на бумаге принято печатать отчёты вместе с ними.
Как говориться "Все события и герои вымышлены. Любые совпадения с реальными личностями случайны" Не думаю, что кого-то это побеспокоит.
Сейчас залью упрощённый файл пример
 
Ответить
СообщениеPelena, Извините, не заметил как удалилась ссылка с яндекс диска. На тот момент табица весила 169КБ и я не успел сжать её до 100. Думал смена формата поможет, но та только раздулась до 497КБ.
Если под сокращением таблицы вы имеете ввиду удаление лишних строк со сменой и датой, то это не подойдёт, т.к. на бумаге принято печатать отчёты вместе с ними.
Как говориться "Все события и герои вымышлены. Любые совпадения с реальными личностями случайны" Не думаю, что кого-то это побеспокоит.
Сейчас залью упрощённый файл пример

Автор - Wrascon
Дата добавления - 15.04.2018 в 21:22
Wrascon Дата: Воскресенье, 15.04.2018, 22:34 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Я оставил 1 семью из 3, с 2 детьми из 5, вес .xlsx сократился со 169 до 83, а при конвертации в .xls что было 497, то стало 294. Залить 83МБ .xlsx версии конечно же сюда нельзя, а в старой .xls все функции перестали работать. Если речь идёт об обрубленном примере с двумя ячейками из используемых формул, где не соблюдается порядок с изложенной мыслью и оказание услуги детей отличается от взрослых отсутствием целых категорий, проще написать их тут, чем подогнать пример с такими ограничениями. Поправьте меня, если я чего-то не понимаю и есть способ легче поделиться таблицей.
 
Ответить
СообщениеЯ оставил 1 семью из 3, с 2 детьми из 5, вес .xlsx сократился со 169 до 83, а при конвертации в .xls что было 497, то стало 294. Залить 83МБ .xlsx версии конечно же сюда нельзя, а в старой .xls все функции перестали работать. Если речь идёт об обрубленном примере с двумя ячейками из используемых формул, где не соблюдается порядок с изложенной мыслью и оказание услуги детей отличается от взрослых отсутствием целых категорий, проще написать их тут, чем подогнать пример с такими ограничениями. Поправьте меня, если я чего-то не понимаю и есть способ легче поделиться таблицей.

Автор - Wrascon
Дата добавления - 15.04.2018 в 22:34
Pelena Дата: Воскресенье, 15.04.2018, 23:04 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12833
Репутация: 2827 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Я убрала одну семью и сохранила в .xlsb, получилось 91 Кб, вполне пролезет на форум.
Приложила к первому сообщению


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЯ убрала одну семью и сохранила в .xlsb, получилось 91 Кб, вполне пролезет на форум.
Приложила к первому сообщению

Автор - Pelena
Дата добавления - 15.04.2018 в 23:04
Wrascon Дата: Четверг, 26.04.2018, 11:40 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Код
=СУММЕСЛИМН(ДВССЫЛ("'"&$A$2&"'!$C5:$AG5");ДВССЫЛ("'"&$A$2&"'!$C$3:$AG$3");C$3)

Из-за двойной ссылки перестали действовать $ в выделенных диапазонах, а каждую строчку забивать вручную довольно проблематично. Пока я не знаю как это переделать хотя бы так. В идеале проверка пересечений Специалист - Смена - Услуга должна перепроверять сам номер услуги и определять её нахождение конкретно для взрослого или ребёнка.
Обновил файл пример, и заполнил лишь первый столбец услуг.


Сообщение отредактировал Wrascon - Четверг, 26.04.2018, 16:18
 
Ответить
Сообщение
Код
=СУММЕСЛИМН(ДВССЫЛ("'"&$A$2&"'!$C5:$AG5");ДВССЫЛ("'"&$A$2&"'!$C$3:$AG$3");C$3)

Из-за двойной ссылки перестали действовать $ в выделенных диапазонах, а каждую строчку забивать вручную довольно проблематично. Пока я не знаю как это переделать хотя бы так. В идеале проверка пересечений Специалист - Смена - Услуга должна перепроверять сам номер услуги и определять её нахождение конкретно для взрослого или ребёнка.
Обновил файл пример, и заполнил лишь первый столбец услуг.

Автор - Wrascon
Дата добавления - 26.04.2018 в 11:40
китин Дата: Четверг, 26.04.2018, 11:42 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4928
Репутация: 786 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
Wrascon, формулу в тэги, если не трудно


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
СообщениеWrascon, формулу в тэги, если не трудно

Автор - китин
Дата добавления - 26.04.2018 в 11:42
Wrascon Дата: Четверг, 26.04.2018, 16:21 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
китин, так тема уже стоит как (Формулы/Formulas)
поставил формулу под код


Сообщение отредактировал Wrascon - Четверг, 26.04.2018, 17:45
 
Ответить
Сообщениекитин, так тема уже стоит как (Формулы/Formulas)
поставил формулу под код

Автор - Wrascon
Дата добавления - 26.04.2018 в 16:21
Wrascon Дата: Вторник, 01.05.2018, 11:27 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Всё ещё нужна формула, если не с проверкой на услуги, то хотя бы эта же с возможностью перетаскивать вниз по строкам. Вправо она и так растягивается


Сообщение отредактировал Wrascon - Вторник, 01.05.2018, 11:28
 
Ответить
СообщениеВсё ещё нужна формула, если не с проверкой на услуги, то хотя бы эта же с возможностью перетаскивать вниз по строкам. Вправо она и так растягивается

Автор - Wrascon
Дата добавления - 01.05.2018 в 11:27
Wrascon Дата: Среда, 02.05.2018, 12:42 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Данная формула проверяет вручную указанный диапазон с/быт услуг матери $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")
К сообщению приложен файл: _-1-.xlsx(99.8 Kb)
 
Ответить
СообщениеДанная формула проверяет вручную указанный диапазон с/быт услуг матери $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
Дата добавления - 02.05.2018 в 12:42
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт соц.услуг специалистов для семей по категориям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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