Здравствуйте. Недавно помогал центру с переходом от бумажного в цифровой учёт табелей услуг. Производится пересчёт оказанных услуг 4 специалистов матерям и детям в нескольких категориях. Вёл услуги по специалистам для каждой семьи. Ближе к концу месяца по этим данным понадобилось сделать два листа с отчётами: один общий по услугам матерей и детей, другой отдельно для каждой семьи. В общем отчёте суммируется диапазон (листы {:}) постоянных семей по дополнительным таблицам и формула всегда привязывается к одной ячейке. Из-за этого после добавления новой услуги таблица сместится и будет выдавать неверный результат. Для срочной семьи подсчитывает только по её собственному листу. Листы всегда имеют фамилию и инициалы семьи, у меня не удаётся сделать привязку формулы к ячейки с названием листа. Заблокировал на всех листах ячейки с формулами, серые ячейки доступны для редактирования. Возник вопрос как с помощью ячейки, указывающей на лист семьи сделать вывод списка членов семьи и провести подсчёт услуг для них каждым специалистом?
Таблица на яндекс диске Ссылка удалена. Нарушение Правил форума
Здравствуйте. Недавно помогал центру с переходом от бумажного в цифровой учёт табелей услуг. Производится пересчёт оказанных услуг 4 специалистов матерям и детям в нескольких категориях. Вёл услуги по специалистам для каждой семьи. Ближе к концу месяца по этим данным понадобилось сделать два листа с отчётами: один общий по услугам матерей и детей, другой отдельно для каждой семьи. В общем отчёте суммируется диапазон (листы {:}) постоянных семей по дополнительным таблицам и формула всегда привязывается к одной ячейке. Из-за этого после добавления новой услуги таблица сместится и будет выдавать неверный результат. Для срочной семьи подсчитывает только по её собственному листу. Листы всегда имеют фамилию и инициалы семьи, у меня не удаётся сделать привязку формулы к ячейки с названием листа. Заблокировал на всех листах ячейки с формулами, серые ячейки доступны для редактирования. Возник вопрос как с помощью ячейки, указывающей на лист семьи сделать вывод списка членов семьи и провести подсчёт услуг для них каждым специалистом?
Таблица на яндекс диске Ссылка удалена. Нарушение Правил форума
=СУММЕСЛИМН('Семья с 1'!C5:AG5;'Семья с 1'!C3:AG3;C$3)
Как будет правильнее разбить эту формулу для пересчёта через ячуйку, указывающую на отдельную семью? Пробовал следующую, где $A$2 название листа нужной семьи, C5:AG5 месячный диапазон услуги, C3:AG3 месячная смена специалистов, C$3 специалист, по которому нужно провести подсчёты.
Помимо этой функции мне нужно узнать как вывести список семьи по ячейкам "Мать:", "Ребёнок 1:", "Ребёнок 2: ", "Ребёнок 3: ", "Ребёнок 4: ", "Ребёнок 5: "
По листу "Общий отчёт" Единственное меня не покидает опасение, что формула по всем листам указанного диапазона "{:}" не адаптируется под изменения в таблице. Любое изменение даже во всех листах оставит операцию на той же самой ячейке
Код
=СУММ('{:}'!AJ14)
У детей всё куда хуже. После добавления новой услуги все ячейки придётся указывать вручную для каждого ребёнка снова
=СУММЕСЛИМН('Семья с 1'!C5:AG5;'Семья с 1'!C3:AG3;C$3)
Как будет правильнее разбить эту формулу для пересчёта через ячуйку, указывающую на отдельную семью? Пробовал следующую, где $A$2 название листа нужной семьи, C5:AG5 месячный диапазон услуги, C3:AG3 месячная смена специалистов, C$3 специалист, по которому нужно провести подсчёты.
Помимо этой функции мне нужно узнать как вывести список семьи по ячейкам "Мать:", "Ребёнок 1:", "Ребёнок 2: ", "Ребёнок 3: ", "Ребёнок 4: ", "Ребёнок 5: "
По листу "Общий отчёт" Единственное меня не покидает опасение, что формула по всем листам указанного диапазона "{:}" не адаптируется под изменения в таблице. Любое изменение даже во всех листах оставит операцию на той же самой ячейке
Код
=СУММ('{:}'!AJ14)
У детей всё куда хуже. После добавления новой услуги все ячейки придётся указывать вручную для каждого ребёнка снова
Pelena, Извините, не заметил как удалилась ссылка с яндекс диска. На тот момент табица весила 169КБ и я не успел сжать её до 100. Думал смена формата поможет, но та только раздулась до 497КБ. Если под сокращением таблицы вы имеете ввиду удаление лишних строк со сменой и датой, то это не подойдёт, т.к. на бумаге принято печатать отчёты вместе с ними. Как говориться "Все события и герои вымышлены. Любые совпадения с реальными личностями случайны" Не думаю, что кого-то это побеспокоит. Сейчас залью упрощённый файл пример
Pelena, Извините, не заметил как удалилась ссылка с яндекс диска. На тот момент табица весила 169КБ и я не успел сжать её до 100. Думал смена формата поможет, но та только раздулась до 497КБ. Если под сокращением таблицы вы имеете ввиду удаление лишних строк со сменой и датой, то это не подойдёт, т.к. на бумаге принято печатать отчёты вместе с ними. Как говориться "Все события и герои вымышлены. Любые совпадения с реальными личностями случайны" Не думаю, что кого-то это побеспокоит. Сейчас залью упрощённый файл примерWrascon
Я оставил 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
Из-за двойной ссылки перестали действовать $ в выделенных диапазонах, а каждую строчку забивать вручную довольно проблематично. Пока я не знаю как это переделать хотя бы так. В идеале проверка пересечений Специалист - Смена - Услуга должна перепроверять сам номер услуги и определять её нахождение конкретно для взрослого или ребёнка. Обновил файл пример, и заполнил лишь первый столбец услуг.
Из-за двойной ссылки перестали действовать $ в выделенных диапазонах, а каждую строчку забивать вручную довольно проблематично. Пока я не знаю как это переделать хотя бы так. В идеале проверка пересечений Специалист - Смена - Услуга должна перепроверять сам номер услуги и определять её нахождение конкретно для взрослого или ребёнка. Обновил файл пример, и заполнил лишь первый столбец услуг.Wrascon
Сообщение отредактировал Wrascon - Четверг, 26.04.2018, 16:18
Всё ещё нужна формула, если не с проверкой на услуги, то хотя бы эта же с возможностью перетаскивать вниз по строкам. Вправо она и так растягивается
Всё ещё нужна формула, если не с проверкой на услуги, то хотя бы эта же с возможностью перетаскивать вниз по строкам. Вправо она и так растягиваетсяWrascon
Сообщение отредактировал Wrascon - Вторник, 01.05.2018, 11:28
Данная формула проверяет вручную указанный диапазон с/быт услуг матери $C$5:$AG$13 и ищет совпадение услуг 1 специалиста, начиная с 1.1. и заканчивая 1.15. Какой массив проверки нужно подключить, чтобы находился диапазон матери или ребёнка по с/быт, с/мед,с/пед, с/труд, с/комм, с/прав типам услуг?
Сейчас имена членов семьи выводятся без интуитивного поиска по указанным ячейкам на условиях, где в обеих ДВССЫЛ A2 меняется на A39, A78, A117,A156, A195 смотря сколько человек в семье.
Код
=ЕСЛИОШИБКА(ЕСЛИ(A$2="";"Укажите название семьи в ячейку A2";ЕСЛИ(ДВССЫЛ("'"&$A$2&"'!A2")="";"";ДВССЫЛ("'"&$A$2&"'!A2")));"Такая семья отсутствует. Проверьте A2")
Данная формула проверяет вручную указанный диапазон с/быт услуг матери $C$5:$AG$13 и ищет совпадение услуг 1 специалиста, начиная с 1.1. и заканчивая 1.15. Какой массив проверки нужно подключить, чтобы находился диапазон матери или ребёнка по с/быт, с/мед,с/пед, с/труд, с/комм, с/прав типам услуг?
Сейчас имена членов семьи выводятся без интуитивного поиска по указанным ячейкам на условиях, где в обеих ДВССЫЛ A2 меняется на A39, A78, A117,A156, A195 смотря сколько человек в семье.
Код
=ЕСЛИОШИБКА(ЕСЛИ(A$2="";"Укажите название семьи в ячейку A2";ЕСЛИ(ДВССЫЛ("'"&$A$2&"'!A2")="";"";ДВССЫЛ("'"&$A$2&"'!A2")));"Такая семья отсутствует. Проверьте A2")
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
