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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчёт среднего дохода за последние N месяцев - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт среднего дохода за последние N месяцев (Формулы/Formulas)
Подсчёт среднего дохода за последние N месяцев
PIMPOK Дата: Понедельник, 26.06.2017, 04:42 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день.

Есть компания, имеющая произвольное количество заказов каждый месяц. Хочется знать среднюю прибыль за последние N месяцев. N вводится руками. Должна строиться средний доход по месяцам и средний доход за последние N месяцев, помесячно.

У меня сделано так, что в конце каждого месяца мы объединяем ячейки и суммируем всю прибыль за месяц. Здесь появилась первая проблема, объединённые ячейки здесь нежелательно использовать, потому что смещение по ним не делается (может и делается, но я не в курсе).
1. Как подсчитать сумму всех заказов по месяцам автоматически? Можно не объединять ячейки, а ставить число в первое или последнее значение месяца.
2. Как скопировать все ячейки со значениями в объединённых ячейках (или в не объединённых, а таких, как описано в п.1) в отдельный столбец, но чтобы не было между ними пробелов (пустых строк)?

Я применил формулу:
Код
=СУММ(D3:СМЕЩ(D3;-$M$2+1;0;1;1))

Для суммирования дохода за последние N месяцев. Здесь есть ещё одна загвоздка.
3. Если выбираемый диапазон уходит вверх, где заканчиваются строки (например, если подсчитать за последние 20 месяцев), то формула не работает. Как это исправить? Какую проверку нужно делать?
К сообщению приложен файл: 1905992.xlsx(12Kb)


Сообщение отредактировал PIMPOK - Понедельник, 26.06.2017, 04:44
 
Ответить
СообщениеДобрый день.

Есть компания, имеющая произвольное количество заказов каждый месяц. Хочется знать среднюю прибыль за последние N месяцев. N вводится руками. Должна строиться средний доход по месяцам и средний доход за последние N месяцев, помесячно.

У меня сделано так, что в конце каждого месяца мы объединяем ячейки и суммируем всю прибыль за месяц. Здесь появилась первая проблема, объединённые ячейки здесь нежелательно использовать, потому что смещение по ним не делается (может и делается, но я не в курсе).
1. Как подсчитать сумму всех заказов по месяцам автоматически? Можно не объединять ячейки, а ставить число в первое или последнее значение месяца.
2. Как скопировать все ячейки со значениями в объединённых ячейках (или в не объединённых, а таких, как описано в п.1) в отдельный столбец, но чтобы не было между ними пробелов (пустых строк)?

Я применил формулу:
Код
=СУММ(D3:СМЕЩ(D3;-$M$2+1;0;1;1))

Для суммирования дохода за последние N месяцев. Здесь есть ещё одна загвоздка.
3. Если выбираемый диапазон уходит вверх, где заканчиваются строки (например, если подсчитать за последние 20 месяцев), то формула не работает. Как это исправить? Какую проверку нужно делать?

Автор - PIMPOK
Дата добавления - 26.06.2017 в 04:42
gling Дата: Понедельник, 26.06.2017, 06:31 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1621
Репутация: 394 ±
Замечаний: 0% ±

2010
Здравствуйте. Вариант в файле. Не понятно со среднем значением. Как считать среднее по дням за 20 месяцев, по месяцам и учитывать ли месяцы без доходов?
К сообщению приложен файл: 8293456.xlsx(13Kb)
 
Ответить
СообщениеЗдравствуйте. Вариант в файле. Не понятно со среднем значением. Как считать среднее по дням за 20 месяцев, по месяцам и учитывать ли месяцы без доходов?

Автор - gling
Дата добавления - 26.06.2017 в 06:31
pabchek Дата: Понедельник, 26.06.2017, 13:45 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 745
Репутация: 168 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!
Мой вариант (если правильно понял):
для каждого месяца
Код
=СУММПРОИЗВ((МЕСЯЦ($A$3:$A$15)=МЕСЯЦ(N6))*$B$3:$B$15)

для последних nn месяцев
Код
=СУММПРОИЗВ(($A$3:$A$15>КОНМЕСЯЦА(СЕГОДНЯ();-M2))*$B$3:$B$15)
К сообщению приложен файл: 1444401.xlsb(10Kb)


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
СообщениеЗдравствуйте!
Мой вариант (если правильно понял):
для каждого месяца
Код
=СУММПРОИЗВ((МЕСЯЦ($A$3:$A$15)=МЕСЯЦ(N6))*$B$3:$B$15)

для последних nn месяцев
Код
=СУММПРОИЗВ(($A$3:$A$15>КОНМЕСЯЦА(СЕГОДНЯ();-M2))*$B$3:$B$15)

Автор - pabchek
Дата добавления - 26.06.2017 в 13:45
PIMPOK Дата: Понедельник, 26.06.2017, 15:31 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте! Большое спасибо, что ответили!

Смотрите, идея со средним значением такая: нужно знать, как повышается средняя прибыль за последние полгода по месяцам. То есть, в январе средняя прибыль считается как сумма прибыли за август-январь, разделённая на 6. В феврале - сентябрь-февраль и т.д. Из этих данных должен строиться график, по которому видно, что прибыль растёт или падает в динамике. Если брать 1 месяц, то среднее по месяцам будет совпадать со средним за последний месяц) Как-то так.

Из ваших формул я взял на вооружение эту:
Код
=СУММПРОИЗВ((МЕСЯЦ($A$3:$A$15)=МЕСЯЦ(--(1&L9)))*$D$3:$D$15)

В принципе, я уже могу реализовать то, что задумал. Единственное, нельзя в столбец с месяцами вставить год. Это не очень удобно для графика, потому что аналитика нужна за несколько лет. Никак нельзя добавить год?
И придётся сделать заготовку из месяцев на несколько месяцев вперёд и не забывать добавлять. Нельзя сделать добавление строчек с месяцем автоматическим при появлении даты с доходом? То есть добавился 04.01.2017, появилась строка в столбце L с новым месяцем?

И как мне всё-таки в ячейке P9 сделать проверку, чтобы когда диапазон уезжает за пределы таблицы, ячейка всё-таки считалась. А то при вводе в M2 значения 10, эта ячейка фактически становится равна нулю.
К сообщению приложен файл: 8546366.xlsx(17Kb)


Сообщение отредактировал PIMPOK - Понедельник, 26.06.2017, 15:32
 
Ответить
СообщениеЗдравствуйте! Большое спасибо, что ответили!

Смотрите, идея со средним значением такая: нужно знать, как повышается средняя прибыль за последние полгода по месяцам. То есть, в январе средняя прибыль считается как сумма прибыли за август-январь, разделённая на 6. В феврале - сентябрь-февраль и т.д. Из этих данных должен строиться график, по которому видно, что прибыль растёт или падает в динамике. Если брать 1 месяц, то среднее по месяцам будет совпадать со средним за последний месяц) Как-то так.

Из ваших формул я взял на вооружение эту:
Код
=СУММПРОИЗВ((МЕСЯЦ($A$3:$A$15)=МЕСЯЦ(--(1&L9)))*$D$3:$D$15)

В принципе, я уже могу реализовать то, что задумал. Единственное, нельзя в столбец с месяцами вставить год. Это не очень удобно для графика, потому что аналитика нужна за несколько лет. Никак нельзя добавить год?
И придётся сделать заготовку из месяцев на несколько месяцев вперёд и не забывать добавлять. Нельзя сделать добавление строчек с месяцем автоматическим при появлении даты с доходом? То есть добавился 04.01.2017, появилась строка в столбце L с новым месяцем?

И как мне всё-таки в ячейке P9 сделать проверку, чтобы когда диапазон уезжает за пределы таблицы, ячейка всё-таки считалась. А то при вводе в M2 значения 10, эта ячейка фактически становится равна нулю.

Автор - PIMPOK
Дата добавления - 26.06.2017 в 15:31
Pelena Дата: Понедельник, 26.06.2017, 17:39 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11481
Репутация: 2549 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
не уверена, что правильно поняла. Посмотрите розовые ячейки
К сообщению приложен файл: 7002003.xlsx(18Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениене уверена, что правильно поняла. Посмотрите розовые ячейки

Автор - Pelena
Дата добавления - 26.06.2017 в 17:39
gling Дата: Понедельник, 26.06.2017, 18:38 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1621
Репутация: 394 ±
Замечаний: 0% ±

2010
как мне всё-таки в ячейке P9 сделать проверку
Избавьтесь от СМЕЩения и не нужна будет проверка. Со среднем, до конца так и не понял. Но может угадал?
К сообщению приложен файл: 9463427.xlsx(16Kb)


Сообщение отредактировал gling - Понедельник, 26.06.2017, 18:39
 
Ответить
Сообщение
как мне всё-таки в ячейке P9 сделать проверку
Избавьтесь от СМЕЩения и не нужна будет проверка. Со среднем, до конца так и не понял. Но может угадал?

Автор - gling
Дата добавления - 26.06.2017 в 18:38
anvg Дата: Вторник, 27.06.2017, 09:58 | Сообщение № 7
Группа: Друзья
Ранг: Обитатель
Сообщений: 348
Репутация: 196 ±
Замечаний: 0% ±

2010, 2016
Доброе время суток.
А почему бы не задействовать Power Pivot?
К сообщению приложен файл: 7664189.zip(66Kb)
 
Ответить
СообщениеДоброе время суток.
А почему бы не задействовать Power Pivot?

Автор - anvg
Дата добавления - 27.06.2017 в 09:58
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт среднего дохода за последние N месяцев (Формулы/Formulas)
Страница 1 из 11
Поиск:

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