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

Вход

Регистрация

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

 

= Мир MS Excel/Автоматический подсчет значений с нескольких листов - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматический подсчет значений с нескольких листов (Формулы/Formulas)
Автоматический подсчет значений с нескольких листов
alpl88 Дата: Среда, 06.08.2014, 15:37 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Доброго времени суток!

Имеется книга exel (см. приложенный файл) в ней листы с "01" по "15" и лист "Расчет смен"
Задача: сделать автоматический подсчет количества человеко/смен на листе "Расчет смен" по статьям на основании ежедневных справок на листах "01" - "15"

На строительном объекте имеются четыре бригадира Иванов, Петров, Сидоров и Джамшут
Они выполняют работы на участках здания от подвала и до 3 этажа
Имеется четыре вида работ Демонтаж, Стяжка, Штукатурка и Окраска
Каждая работа оплачивается из соответствующей статьи - 0-01, 0-02, 0-03 и 0-04 соответственно
В ежедневных справках (листы с "01" по "15") произведен учет количества человеко/смен каждого бригадира на каждом участке по каждому виду работ
На листе "Расчет смен" представлена таблица, в которой необходимо подсчитать суммарное количество человеко/смен каждого бригадира по каждому виду работ на каждый день

Для удобства все справки заполнены одинаково, но в реальности имеется большее количество бригад, участков и видов работ

Что делал я:
На листе "Расчет смен" ставлю "=" в ячейке на пересечении "1 августа" и бригадира "Иванов" в статье "0-01"
Далее формула СУММ, щелкаю на лист "01", выделяю все ячейки на пересечении столбца бригадира Иванова и работ Демонтаж (статья 0-01) включая обе смены работы (столбца смена 1 и смена 2)
получается следующая формула:

=СУММ('01'!RC[-2]:RC[-1];'01'!R[5]C[-2]:R[5]C[-1];'01'!R[10]C[-2]:R[10]C[-1];'01'!R[15]C[-2]:R[15]C[-1])

значение получилось 11
и вот так мне необходимо сделать с каждым бригадиром на каждый день и на каждый вид работы-статью бюджета

Сложность заключается в создании формулы, которую можно было бы "растянуть" на все ячейки дней в таблице расчета, ведь формула, которую я указал выше, для данного бригадира на данную статью бюджета, но уже на следующий день ,будет отличаться только номером листа, т.е. будет выглядеть так:

=СУММ('02'!RC[-2]:RC[-1];'02'!R[5]C[-2]:R[5]C[-1];'02'!R[10]C[-2]:R[10]C[-1];'02'!R[15]C[-2]:R[15]C[-1])

красным я выделил то, что поменялось
вручную менять все это очень долго + можно ошибиться
подскажите пожалуйста, может есть каки-то другие варианты решить данную задачу.

Заранее благодарен!
К сообщению приложен файл: 1373192.xlsx (40.2 Kb)
 
Ответить
СообщениеДоброго времени суток!

Имеется книга exel (см. приложенный файл) в ней листы с "01" по "15" и лист "Расчет смен"
Задача: сделать автоматический подсчет количества человеко/смен на листе "Расчет смен" по статьям на основании ежедневных справок на листах "01" - "15"

На строительном объекте имеются четыре бригадира Иванов, Петров, Сидоров и Джамшут
Они выполняют работы на участках здания от подвала и до 3 этажа
Имеется четыре вида работ Демонтаж, Стяжка, Штукатурка и Окраска
Каждая работа оплачивается из соответствующей статьи - 0-01, 0-02, 0-03 и 0-04 соответственно
В ежедневных справках (листы с "01" по "15") произведен учет количества человеко/смен каждого бригадира на каждом участке по каждому виду работ
На листе "Расчет смен" представлена таблица, в которой необходимо подсчитать суммарное количество человеко/смен каждого бригадира по каждому виду работ на каждый день

Для удобства все справки заполнены одинаково, но в реальности имеется большее количество бригад, участков и видов работ

Что делал я:
На листе "Расчет смен" ставлю "=" в ячейке на пересечении "1 августа" и бригадира "Иванов" в статье "0-01"
Далее формула СУММ, щелкаю на лист "01", выделяю все ячейки на пересечении столбца бригадира Иванова и работ Демонтаж (статья 0-01) включая обе смены работы (столбца смена 1 и смена 2)
получается следующая формула:

=СУММ('01'!RC[-2]:RC[-1];'01'!R[5]C[-2]:R[5]C[-1];'01'!R[10]C[-2]:R[10]C[-1];'01'!R[15]C[-2]:R[15]C[-1])

значение получилось 11
и вот так мне необходимо сделать с каждым бригадиром на каждый день и на каждый вид работы-статью бюджета

Сложность заключается в создании формулы, которую можно было бы "растянуть" на все ячейки дней в таблице расчета, ведь формула, которую я указал выше, для данного бригадира на данную статью бюджета, но уже на следующий день ,будет отличаться только номером листа, т.е. будет выглядеть так:

=СУММ('02'!RC[-2]:RC[-1];'02'!R[5]C[-2]:R[5]C[-1];'02'!R[10]C[-2]:R[10]C[-1];'02'!R[15]C[-2]:R[15]C[-1])

красным я выделил то, что поменялось
вручную менять все это очень долго + можно ошибиться
подскажите пожалуйста, может есть каки-то другие варианты решить данную задачу.

Заранее благодарен!
Автор - alpl88
Дата добавления - 06.08.2014 в 15:37
Russel Дата: Среда, 06.08.2014, 16:27 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
Сложный путь Вы себе избрали. Если еще не поздно, рассматривайте другие варианты организации информации.
Как пример набросал Вам на листе 1
К сообщению приложен файл: 9286346.xlsx (49.6 Kb)

QIWI 9173973973
 
Ответить
СообщениеСложный путь Вы себе избрали. Если еще не поздно, рассматривайте другие варианты организации информации.
Как пример набросал Вам на листе 1
Автор - Russel
Дата добавления - 06.08.2014 в 16:27
_Boroda_ Дата: Среда, 06.08.2014, 16:41 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16913
Репутация: 6617 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Согласен с Русланом. Но, уж если очень хочется - держите
Код
=СУММПРОИЗВ(ДВССЫЛ(ТЕКСТ(K$12;"'00")&"'!$I$13:$P$31")*(ДВССЫЛ(ТЕКСТ(K$12;"'00")&"'!$G$13:$G$31")=ПРОСМОТР(;-КОДСИМВ($H$13:$H13);$H$13:$H13))*((ДВССЫЛ(ТЕКСТ(K$12;"'00")&"'!$I$11:$P$11")=$J13)+(ДВССЫЛ(ТЕКСТ(K$12;"'00")&"'!$H$11:$O$11")=$J13)))
К сообщению приложен файл: 1373192_1.xlsx (47.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеСогласен с Русланом. Но, уж если очень хочется - держите
Код
=СУММПРОИЗВ(ДВССЫЛ(ТЕКСТ(K$12;"'00")&"'!$I$13:$P$31")*(ДВССЫЛ(ТЕКСТ(K$12;"'00")&"'!$G$13:$G$31")=ПРОСМОТР(;-КОДСИМВ($H$13:$H13);$H$13:$H13))*((ДВССЫЛ(ТЕКСТ(K$12;"'00")&"'!$I$11:$P$11")=$J13)+(ДВССЫЛ(ТЕКСТ(K$12;"'00")&"'!$H$11:$O$11")=$J13)))
Автор - _Boroda_
Дата добавления - 06.08.2014 в 16:41
ArkaIIIa Дата: Среда, 06.08.2014, 16:44 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 894
Репутация: 115 ±
Замечаний: 0% ±

2010
А я как раз ждал очередное произведение искусства от Бороды :-)
Думал посидеть - поразбираться. Но вот, дождался, и понял, что в такой конструкции до конца рабочего дня - не разберусь)


Сообщение отредактировал ArkaIIIa - Среда, 06.08.2014, 16:44
 
Ответить
СообщениеА я как раз ждал очередное произведение искусства от Бороды :-)
Думал посидеть - поразбираться. Но вот, дождался, и понял, что в такой конструкции до конца рабочего дня - не разберусь)
Автор - ArkaIIIa
Дата добавления - 06.08.2014 в 16:44
_Boroda_ Дата: Среда, 06.08.2014, 16:55 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16913
Репутация: 6617 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Да ладно! Там обычная
Код
=СУММПРОИЗВ('01'!$I$13:$P$31*('01'!$G$13:$G$31=ПРОСМОТР(;-КОДСИМВ($H$13:$H19);$H$13:$H19))*(('01'!$I$11:$P$11=$J19)+('01'!$H$11:$O$11=$J19)))

В ней сложность может вызвать разве что кусок ПРОСМОТР(;-КОДСИМВ($H$13:$H19);$H$13:$H19)
А потом названия листа просто заменены на ДВССЫЛ(ТЕКСТ(K$12;"'00")

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДа ладно! Там обычная
Код
=СУММПРОИЗВ('01'!$I$13:$P$31*('01'!$G$13:$G$31=ПРОСМОТР(;-КОДСИМВ($H$13:$H19);$H$13:$H19))*(('01'!$I$11:$P$11=$J19)+('01'!$H$11:$O$11=$J19)))

В ней сложность может вызвать разве что кусок ПРОСМОТР(;-КОДСИМВ($H$13:$H19);$H$13:$H19)
А потом названия листа просто заменены на ДВССЫЛ(ТЕКСТ(K$12;"'00")
Автор - _Boroda_
Дата добавления - 06.08.2014 в 16:55
alpl88 Дата: Четверг, 07.08.2014, 07:42 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_,
Ого! Это конечно высший пилотаж!!! hands hands hands
Я попытался разобраться с формулой, но без помощи тут не обойтись
вообще сам документ в оригинале выглядит примерно так:
(чтобы вписаться в 100КБ мне пришлось оставить только две справки, в реале их 31)
К сообщению приложен файл: 2197077.xlsx (94.3 Kb)
 
Ответить
Сообщение_Boroda_,
Ого! Это конечно высший пилотаж!!! hands hands hands
Я попытался разобраться с формулой, но без помощи тут не обойтись
вообще сам документ в оригинале выглядит примерно так:
(чтобы вписаться в 100КБ мне пришлось оставить только две справки, в реале их 31)
Автор - alpl88
Дата добавления - 07.08.2014 в 07:42
_Boroda_ Дата: Четверг, 07.08.2014, 10:15 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16913
Репутация: 6617 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата alpl88, 07.08.2014 в 07:42, в сообщении № 6
вообще сам документ в оригинале выглядит примерно так:
А почему Вы в качестве примера приложили файл с совершенно другой по формату таблицей? Ну, не совершенно, но со значительными отличиями, которые на формулу серьезно влияют. В следующий раз следуйте п.3 Правил форума, строка "- При этом старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу", иначе нам сложно будет Вам помочь.
Держите
Код
=СУММ(ТЕКСТ(ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!$J$14:$AE$191");"0;;0;\0")*(ПРОСМОТР(СТРОКА(A$14:A$191);СТРОКА(A$14:A$191)/(ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!G$14:G191")<>"");ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!G$14:G$191"))=ПРОСМОТР(;-1/($E$10:$E81<>"");$E$10:$E81))*(ПРОСМОТР(СТОЛБЕЦ($J1:$AE1);СТОЛБЕЦ($J1:$AE1)/(ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!$J$12:$AE$12")<>"");ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!$J$12:$AE$12"))=$G81))
К сообщению приложен файл: 2197077_1.xlsx (99.9 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата alpl88, 07.08.2014 в 07:42, в сообщении № 6
вообще сам документ в оригинале выглядит примерно так:
А почему Вы в качестве примера приложили файл с совершенно другой по формату таблицей? Ну, не совершенно, но со значительными отличиями, которые на формулу серьезно влияют. В следующий раз следуйте п.3 Правил форума, строка "- При этом старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу", иначе нам сложно будет Вам помочь.
Держите
Код
=СУММ(ТЕКСТ(ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!$J$14:$AE$191");"0;;0;\0")*(ПРОСМОТР(СТРОКА(A$14:A$191);СТРОКА(A$14:A$191)/(ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!G$14:G191")<>"");ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!G$14:G$191"))=ПРОСМОТР(;-1/($E$10:$E81<>"");$E$10:$E81))*(ПРОСМОТР(СТОЛБЕЦ($J1:$AE1);СТОЛБЕЦ($J1:$AE1)/(ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!$J$12:$AE$12")<>"");ДВССЫЛ(ТЕКСТ(I$9;"'00")&"'!$J$12:$AE$12"))=$G81))
Автор - _Boroda_
Дата добавления - 07.08.2014 в 10:15
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматический подсчет значений с нескольких листов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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