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

Вход

Регистрация

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

 

= Мир MS Excel/расширения диапазона для суммрования от кол-ва дней месяца - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » расширения диапазона для суммрования от кол-ва дней месяца (Формулы/Formulas)
расширения диапазона для суммрования от кол-ва дней месяца
djorje15 Дата: Суббота, 12.09.2015, 16:43 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 41
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Доброго времени суток! Помогите подправить формулами диапазон AI27:BM28 в формуле в ячейке СС, чтобы в зависимости от количества дней месяца изменялся диапазон. В 26 строке даты месяца. В феврале 28 дней, в остальных 30 или 31, нужно чтоб диапазон суммирования в зависимости от количества дней в месяце изменялся. Например: в феврале должно быть AI27:BJ28, в апреле AI27:BL28. Обязательное условие, чтоб формулу можно было протягивать вниз или по типу именного диапазона чтоб работало на добавленных ниже.
К сообщению приложен файл: 1936094.xlsx (14.8 Kb)
 
Ответить
СообщениеДоброго времени суток! Помогите подправить формулами диапазон AI27:BM28 в формуле в ячейке СС, чтобы в зависимости от количества дней месяца изменялся диапазон. В 26 строке даты месяца. В феврале 28 дней, в остальных 30 или 31, нужно чтоб диапазон суммирования в зависимости от количества дней в месяце изменялся. Например: в феврале должно быть AI27:BJ28, в апреле AI27:BL28. Обязательное условие, чтоб формулу можно было протягивать вниз или по типу именного диапазона чтоб работало на добавленных ниже.
Автор - djorje15
Дата добавления - 12.09.2015 в 16:43
Udik Дата: Суббота, 12.09.2015, 17:23 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Что-то такое можно (массив формула)
Код
{=СУММПРОИЗВ(ЕСЛИОШИБКА(--AI27:BM27;0)*(AI26:BM26>0))*24}

Сейчас просто суммирует числа пригодного диапазона и умножает на 24

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

Сообщение отредактировал Udik - Суббота, 12.09.2015, 17:26
 
Ответить
СообщениеЧто-то такое можно (массив формула)
Код
{=СУММПРОИЗВ(ЕСЛИОШИБКА(--AI27:BM27;0)*(AI26:BM26>0))*24}

Сейчас просто суммирует числа пригодного диапазона и умножает на 24
Автор - Udik
Дата добавления - 12.09.2015 в 17:23
djorje15 Дата: Суббота, 12.09.2015, 17:35 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 41
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Udik, спасибо за быстрый ответ. Но не помогло. Задача чтобы изменялся диапазон т.к. суммируемые ячейки не остаются пустым, а данные в них могут быть различны: текст, время, даты, ноль, пусто, крукозябры и др, которые не нужны для подсчета и скрываются Условным Форматированием.
 
Ответить
СообщениеUdik, спасибо за быстрый ответ. Но не помогло. Задача чтобы изменялся диапазон т.к. суммируемые ячейки не остаются пустым, а данные в них могут быть различны: текст, время, даты, ноль, пусто, крукозябры и др, которые не нужны для подсчета и скрываются Условным Форматированием.
Автор - djorje15
Дата добавления - 12.09.2015 в 17:35
Nic70y Дата: Суббота, 12.09.2015, 17:53 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 8821
Репутация: 2298 ±
Замечаний: 0% ±

Excel 2010
Код
=(СУММ(AI28:ИНДЕКС(AI28:BM28;ДЕНЬ(КОНМЕСЯЦА(BB$23;0))))+СУММ(ЕСЛИ((AI28:ИНДЕКС(AI28:BM28;ДЕНЬ(КОНМЕСЯЦА(BB$23;0))))="24:00";1))-СУММ(AI27:ИНДЕКС(AI27:BM27;ДЕНЬ(КОНМЕСЯЦА(BB$23;0)))))*24

ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=(СУММ(AI28:ИНДЕКС(AI28:BM28;ДЕНЬ(КОНМЕСЯЦА(BB$23;0))))+СУММ(ЕСЛИ((AI28:ИНДЕКС(AI28:BM28;ДЕНЬ(КОНМЕСЯЦА(BB$23;0))))="24:00";1))-СУММ(AI27:ИНДЕКС(AI27:BM27;ДЕНЬ(КОНМЕСЯЦА(BB$23;0)))))*24
Автор - Nic70y
Дата добавления - 12.09.2015 в 17:53
Udik Дата: Суббота, 12.09.2015, 18:10 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Цитата djorje15, 12.09.2015 в 17:35, в сообщении № 3
Задача чтобы изменялся диапазон

Не понял, в первом посте вопрос был об отсечке лишних дней, так моя формула и делает это. Вот этот кусок: AI26:BM26>0
А в 26 строке ваши же формулы в лишних днях нули выводят, за суммируемые ячейки первая половина формулы отвечает, она пытается получить число, если не получается, то возвращает 0, т.е. не мешают ей кракозяблы :)

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщение
Цитата djorje15, 12.09.2015 в 17:35, в сообщении № 3
Задача чтобы изменялся диапазон

Не понял, в первом посте вопрос был об отсечке лишних дней, так моя формула и делает это. Вот этот кусок: AI26:BM26>0
А в 26 строке ваши же формулы в лишних днях нули выводят, за суммируемые ячейки первая половина формулы отвечает, она пытается получить число, если не получается, то возвращает 0, т.е. не мешают ей кракозяблы :)
Автор - Udik
Дата добавления - 12.09.2015 в 18:10
jakim Дата: Суббота, 12.09.2015, 18:25 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1205
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Смотрите и вникайте, мне кажется что можно применить в решении Вашей проблемы.
К сообщению приложен файл: Copy_of_1936094.xlsx (36.7 Kb)
 
Ответить
Сообщение
Смотрите и вникайте, мне кажется что можно применить в решении Вашей проблемы.
Автор - jakim
Дата добавления - 12.09.2015 в 18:25
Karataev Дата: Суббота, 12.09.2015, 18:26 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1334
Репутация: 533 ±
Замечаний: 0% ±

Excel
Формула массива
Код
=(СУММ(ЕСЛИ(ЕЧИСЛО(СМЕЩ(AI27:AI28;;;;ДЕНЬ(КОНМЕСЯЦА($BB$23;0))));СМЕЩ(AI27:AI28;;;;ДЕНЬ(КОНМЕСЯЦА($BB$23;0)));0)*{-1:1}))*24

P.S. убрал замену пробела на двоеточие, т.к. не понял, зачем это делается


Сообщение отредактировал Karataev - Суббота, 12.09.2015, 18:27
 
Ответить
СообщениеФормула массива
Код
=(СУММ(ЕСЛИ(ЕЧИСЛО(СМЕЩ(AI27:AI28;;;;ДЕНЬ(КОНМЕСЯЦА($BB$23;0))));СМЕЩ(AI27:AI28;;;;ДЕНЬ(КОНМЕСЯЦА($BB$23;0)));0)*{-1:1}))*24

P.S. убрал замену пробела на двоеточие, т.к. не понял, зачем это делается
Автор - Karataev
Дата добавления - 12.09.2015 в 18:26
djorje15 Дата: Суббота, 12.09.2015, 18:36 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 41
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Udik,не получается у меня модифицировать Ваш вариант, т.к. диапазон отсекается, но суммируется не диапазон AI27:BM28, а одна строка только одна строка AI27:BM27.. А должна суммироваться разница из каждого дня берется конец работы вычитается начало работы, получаем отработанные часы и минуты их то и суммировала раньше формула в ячейке СС, но начались ошибки(после некоторой автоматизации) из-за ненужных данных в ячейках вне текущего месяца.. Например: AZ28-AZ27=8 часов работы. Или Я не понял в какой кусок формулы ее вставить.
 
Ответить
СообщениеUdik,не получается у меня модифицировать Ваш вариант, т.к. диапазон отсекается, но суммируется не диапазон AI27:BM28, а одна строка только одна строка AI27:BM27.. А должна суммироваться разница из каждого дня берется конец работы вычитается начало работы, получаем отработанные часы и минуты их то и суммировала раньше формула в ячейке СС, но начались ошибки(после некоторой автоматизации) из-за ненужных данных в ячейках вне текущего месяца.. Например: AZ28-AZ27=8 часов работы. Или Я не понял в какой кусок формулы ее вставить.
Автор - djorje15
Дата добавления - 12.09.2015 в 18:36
djorje15 Дата: Суббота, 12.09.2015, 18:49 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 41
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Karataev, замена пробела делалась для борьбы с человеческим фактором(победили время бьют теперь правильно через двоеточие). Что- то похожее на Ваш вариант крутил в голове, но боялся отказаться от замены пробела (без него даже лучше). Но Ваш вариант корректно считает первый диапазон, но при протаскивании сбивается и неверно считает диапазон AI29:BM30
 
Ответить
СообщениеKarataev, замена пробела делалась для борьбы с человеческим фактором(победили время бьют теперь правильно через двоеточие). Что- то похожее на Ваш вариант крутил в голове, но боялся отказаться от замены пробела (без него даже лучше). Но Ваш вариант корректно считает первый диапазон, но при протаскивании сбивается и неверно считает диапазон AI29:BM30
Автор - djorje15
Дата добавления - 12.09.2015 в 18:49
djorje15 Дата: Суббота, 12.09.2015, 18:52 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 41
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Nic70y, тестирую Ваш вариант, в файле примере все ОК, однако также сбивается при протаскивании когда вставляют в основной файл. Ищу..
 
Ответить
СообщениеNic70y, тестирую Ваш вариант, в файле примере все ОК, однако также сбивается при протаскивании когда вставляют в основной файл. Ищу..
Автор - djorje15
Дата добавления - 12.09.2015 в 18:52
Karataev Дата: Суббота, 12.09.2015, 19:03 | Сообщение № 11
Группа: Проверенные
Ранг: Старожил
Сообщений: 1334
Репутация: 533 ±
Замечаний: 0% ±

Excel
Цитата djorje15, 12.09.2015 в 18:49, в сообщении № 9
Но Ваш вариант корректно считает первый диапазон, но при протаскивании сбивается и неверно считает диапазон AI29:BM30

почему-то "0:00" и "24:00" excel не считает числом. Для "0:00" это не имеет значения в вашей задаче, а вот для "24:00" имеет.
Вот так работает (формула массива)
Код
=(СУММ(ЕСЛИ(ЕЧИСЛО(--СМЕЩ(AI29:AI30;;;;ДЕНЬ(КОНМЕСЯЦА($BB$23;0))));СМЕЩ(AI29:AI30;;;;ДЕНЬ(КОНМЕСЯЦА($BB$23;0)));0)*{-1:1}))*24


Сообщение отредактировал Karataev - Суббота, 12.09.2015, 20:19
 
Ответить
Сообщение
Цитата djorje15, 12.09.2015 в 18:49, в сообщении № 9
Но Ваш вариант корректно считает первый диапазон, но при протаскивании сбивается и неверно считает диапазон AI29:BM30

почему-то "0:00" и "24:00" excel не считает числом. Для "0:00" это не имеет значения в вашей задаче, а вот для "24:00" имеет.
Вот так работает (формула массива)
Код
=(СУММ(ЕСЛИ(ЕЧИСЛО(--СМЕЩ(AI29:AI30;;;;ДЕНЬ(КОНМЕСЯЦА($BB$23;0))));СМЕЩ(AI29:AI30;;;;ДЕНЬ(КОНМЕСЯЦА($BB$23;0)));0)*{-1:1}))*24
Автор - Karataev
Дата добавления - 12.09.2015 в 19:03
Udik Дата: Суббота, 12.09.2015, 19:14 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Цитата djorje15, 12.09.2015 в 18:36, в сообщении № 8
из каждого дня берется конец работы вычитается начало работы

Тогда так вроде
Код

=СУММПРОИЗВ(ЕСЛИОШИБКА(AI28:BM28-AI27:BM27;0)*($AI$26:$BM$26>0))*24
К сообщению приложен файл: workday.xlsx (16.8 Kb)

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

Сообщение отредактировал Udik - Суббота, 12.09.2015, 19:26
 
Ответить
Сообщение
Цитата djorje15, 12.09.2015 в 18:36, в сообщении № 8
из каждого дня берется конец работы вычитается начало работы

Тогда так вроде
Код

=СУММПРОИЗВ(ЕСЛИОШИБКА(AI28:BM28-AI27:BM27;0)*($AI$26:$BM$26>0))*24
Автор - Udik
Дата добавления - 12.09.2015 в 19:14
djorje15 Дата: Суббота, 12.09.2015, 19:18 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 41
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Karataev, похоже решено! с двойным отрицанием заработало!

Спасибо всем озадачившимся и сопричастным! Отдельное спасибо Karataev!
 
Ответить
СообщениеKarataev, похоже решено! с двойным отрицанием заработало!

Спасибо всем озадачившимся и сопричастным! Отдельное спасибо Karataev!
Автор - djorje15
Дата добавления - 12.09.2015 в 19:18
djorje15 Дата: Суббота, 12.09.2015, 19:25 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 41
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Udik, Ваше решение также работает! Спасибо
 
Ответить
СообщениеUdik, Ваше решение также работает! Спасибо
Автор - djorje15
Дата добавления - 12.09.2015 в 19:25
Мир MS Excel » Вопросы и решения » Вопросы по Excel » расширения диапазона для суммрования от кол-ва дней месяца (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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