Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Промежуточные итоги.
 
#1
11.12.2009 00:24:38
Столкнулась с такой проблемой - есть большой список. К нему идеально подходят промежуточные итоги. Проблема в том, что нужно в итоги вставить не стандартную функцию, уже готовую (типа 1, 2......) а вместо готовой. использовать формулу, которая будет работать на этом участке. Каждый раз вводить руками или хотя бы только изменять диапазоны - достаточно долго.
 
 
 
#2
11.12.2009 03:31:08
Приняли к сведению. Спасибо.
 
 
 
#3
11.12.2009 03:34:49
Хотя нет, буду добрым.  
Дайте пример с подробным описанием задачи.  
Мои телепатические способности подсказывают, что вам скорее всего понадобятся "одноклеточные" массивы, полученные функциями двссыл() или смещ(). Примеры можете поиском попытаться найти, скажем, по запросу "KL двссыл".
 
 
 
#4
11.12.2009 09:16:39
Извините, что не отправила файлик сразу, просто я не знала что такое вполне возможно, хотя уже и пора привыкнуть что на этом форуме ничего невозможного в Excel близится к минимуму. Спасибо вам!  
 
Приложение перевела в формат Excel 2003, хотя ругался при конвертировании с 2007 на возможную замену некоторых ссылок на адреса.
 
 
 
#5
11.12.2009 10:33:25
По поводу формулы, которую хотелось бы проссчитать в промежуточных итогах, чтобы не повторяться был пост "Опять суммирование ЕСЛИ.." от 17.11.2009. http://www.planetaexcel.ru/forum.php?thread_id=11340&page_forum=1&allnum_forum=42
 
 
 
#6
14.12.2009 11:35:32
Непонятно применение в итоговой строке (например, 223) ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Достаточно СУММ.  
Если ссылки в формуле на тот же лист, имя листа можно не указывать (формулы столбца М).  
Как вставляете итоговые строки - автоматически (макрос) или вручную. Может, меет смысл вынести итоги в отдельную таблицу?  
Два примера для определения диапазона. Например, F236=СУММ(F224:F235).  
1. =СМЕЩ($F$1;ПОИСКПОЗ(B236;$B$1:B235;;СЧЕТЕСЛИ($B$1:B235;B236)-1;1)), или с помощью ИНДЕКС/ПОИСКПОЗ, или =СУММЕСЛИ($B$1:B235;B236;$F$1:F235). Ищем начало дипазона и задаем его ширину. Недостаток - чем дальше вниз, тем длиннее диапазон пересчета, т.е. чем больше таблица, тем медленнее считает.  
2. =СУММЕСЛИ(B1:B235;B236;F1:F235) - диапазон не закреплен, сдвигается по строкам вместе с формулой. Недостаток - нужно задать ширину диапазона, достаточную для возможного максимального количества одинаковых дат. И если, например, первый диапазон маленький, нужно добавлять пустые строки сверху таблицы (можно и скрытые, но это сути не меняет).  
Формула в столбце L. Если правильно понял - при невыполнении условия округление 5% от суммы (F224:F235)-K236, если выполняется, то E224:E235*10. Зачем тода разбивать ЕСЛИ на две части? ЕСЛИ(условие;E224:E235*10;ОКРУГЛ(...)).  
Если аренда (K236) всегда равна 39, в формулу вписать без ссылки.  
Нужна ли СЖПРОБЕЛЫ в формуле?  
 
Возможно, не понял задание.
 
 
 
#7
15.12.2009 00:30:13
Основная задача (на данном этапе) - учет реализуемой продукции, выручки расходов.  
Это уже второй вариант таблицы (на мой взляд наиболее удачный, нежели    
http://www.planetaexcel.ru/forum.php?thread_id=11340&page_forum=1&allnum_forum=42)  
Попробую ответить на вопросы:  
1)Почему в строке 223 промежуточные итоги а не СУММ. - По идее группировка по дате это первый уровень, а второй уровень - группировка по месяцу. При подсчете  следующих итогов будет удобнее воспользоваться "промежуточные итоги", которые просуммируют только промежуточные итоги, а при переведении в формат 2003 не будут ругаться что много аргументов.  
2) Как вставляла итоговые строги - глупо - руками (потому как вначале определила диапазон таблицы, а в таблице как оказалось группировка с итоговыми строками невозможна.  
3)Идея вынести итоговую таблицу на отдельный лист - Отлично! (только я боюсь что не справлюсь с ней)  
4) насколько я поняла, то если это сделать макросами, то это будет самое лучшее решение..... (опять этого я вообще не смогу сделать)  
5) суть формулы (стобец L):  
реализатор получает 5% от выручки за вычетом аренды и возврата. (не рискую ставить константу с нашим админресурсом) Пример формулы =(округл((F-H-K223)*0,05;0)  
Эта формула работает до тех пор, пока в список не попадают позиции, находящиеся в списке "В_одежда"и цена их >200, в этом случае формула принимает вид - количество вещей*10  
=(округл((F-H-K223)*0,05;0+E*10)  
Вот и получается что формула состоит из двух частей.    
По поводу  СЖПРОБЕЛЫ  я не могу сказать точно, мне формулу помогли составить на форуме. По всей видимости в наименованиях столбцов было достаточно много пробелов.  
Если реально мне помочь, пожалуйста помогите.  
Если вдруг ошибки в тексте, простите, еще не совсем очухалась от гриппа. Он у нас гуляет на широкую ногу.
 
 
 
#8
15.12.2009 00:40:02
Пока Вас небыло - формула для L236 (та же, но короче):  
=ОКРУГЛ(СУММ(ЕСЛИ(СЧЁТЕСЛИ(В_одежда;СЖПРОБЕЛЫ(C224:C235))*(D224:D235>200);E224:E235*10;F224:F235*0,05))-K236*0,05;0)  
Пока буду читать Ваше сообщение, проверьте.
 
 
 
#9
15.12.2009 00:53:07
Ваш текст прочитал, мысли в ответ есть.  
Но основная мысль - СПАТЬ ХОЧЕТСЯ! :)  
До завтра.
 
 
 
#10
15.12.2009 12:52:38
Формула гараздо короче!!! Проверила и на других диапазонах (естественно корректируя границы диапазонов)где используются ЕСЛИ. Работает, однако все это нужно будет вводить вручную. Однако в формуле пропущена одна её часть - минус возврат (столбец Н)Для первых промежуточных итогов это формула будет работать отлично, а вот для строки 276 и ей подобных, результат будет неверный.
 
 
 
#11
15.12.2009 14:28:34
Посмотрите пример итоговой таблицы. Вот в данном случае ПРОМИТОГИ в тему :)  
Итоговые результаты по дате, по месяцу, можно добавить по любому периоду.  
Можно создать такую же табличку отдельно по месяцам. Формулу реализации пока не вводил.  
Таблица в данных заполняется без итоговых результатов.
 
 
 
#12
15.12.2009 14:35:20
Тут прицепчик
 
 
 
#13
15.12.2009 18:32:58
прекрасная идея.    
В свою очередь, я решила немного "сократить" количество данных и некоторые данные убрать из этой таблицы. Плюс подкорректировала, чтобы лучше проверить работу ненаписанной формулы.
 
 
 
#14
16.12.2009 11:20:03
Не проверял, потестируйте. Формула для Н3:  
=ОКРУГЛ(СУММ((данные!$B$7:$B$1000=B3)*ЕСЛИ(СЧЁТЕСЛИ(В_одежда;данные!$C$7:$C$1000)*(данные!$D$7:$D$1000>200);данные!$E$7:$E$1000*10;данные!$F$7:$F$1000*0,05-данные!$H$7:$H$1000*0,05))-39*0,05;0)  
Не забывайте - формула массива.  
При большом количестве строк в итоговой таблице при пересчетах будет тормозить.  
Я пока что не разбирался в нужной логике вычисления, составил формулу по описанному. Возможно, стоит подумать об оптимизации данных для этой формулы или изменении методики расчета.
 
 
 
#15
16.12.2009 17:17:41
попробовала формулу. Что то в ней не так, потому как выдает ошибку #ЗНАЧ!  
Попробовала оптимизировать вычисления. Может так будет немного быстрее для компа?
 
 
 
#16
17.12.2009 14:19:09
{quote}{login=owl-ka}{date=16.12.2009 05:17}{thema=}{post}Что то в ней не так, потому как выдает ошибку #ЗНАЧ!{/post}{/quote}  
Все в ней так (или тик-так-тип-топ :)).  
Формула массива (вводится через Ctrl-Shift-Enter). У меня работает, выдает число. Возможно, неправильное :)  
Теперь по условию. Куда делась аренда из формулы? Зажали?  
Условие (E-G)*10 можно добавить, не проблема. И, если еще нужно будет, сделаем.  
Но, как писал выше, при большом количестве строк в итоговой таблице при пересчетах будет тормозить. А это неибежно, так как строк будет, как дат в году.  
Поэтому предлагаю не мудрить с одной "массивной" формулой, а на листе "данные" в доп. столбце проводить промежуточные вычисления, приблизительно:  
=ЕСЛИ(СЧЁТЕСЛИ(В_одежда;C7)*(D7>200);(E7-G7)*10;(F7-H7)*0,05))-39*0,05)  
Эта формула "не массива", обычная.  
Результат на итоговом листе считать с помощью этого дополнения.  
=ОКРУГЛ(СУММЕСЛИ(данные!$B$7:$B$1000;B3;доп_столб)  
Проверяйте и ругайте :)
 
 
 
#17
17.12.2009 14:21:19
Наверное, правильнее -39*0,05 перенести в итоговую формулу.
 
 
 
#18
17.12.2009 17:43:17
Формула мне понравилась, это то чего именно хотелось.    
Спасибо огромное
 
 
 
#19
18.12.2009 01:26:47
{quote}{login=owl-ka}{date=17.12.2009 05:43}{thema=}{post}это то чего именно хотелось. Спасибо огромное{/post}{/quote}  
Рад помочь, особенно если Вам целую неделю только именно этого хотелось :)
 
 
 
Страницы: 1
Читают тему
Наверх