Excel - мне нужна помощь, чтобы уменьшить избыточность СУММЕСЛИМН с датой и соответствием ключевых слов

Я пытаюсь собрать общую стоимость товаров, проданных по различным позициям, по мере того, как товары продаются, и группирую их по месяцам, чтобы обновить электронную таблицу.

Формула СУММЕСЛИМН, которую я использую, очень часто повторяется из-за ограничений даты, и я знаю, что должен быть более простой и понятный способ получения тех же результатов.

Формула, которую я сейчас использую:

=SUM((SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,"Apple")*0.5)+(SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,"Banana")*0.75)+(SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,"Orange")*1.25))

Заранее спасибо!


date excel sumifs date-range
person Austmado    schedule 30.01.2016    source источник
comment
Фрукты где-то в списке?   -  person Scott Craner    schedule 30.01.2016
comment
Скотт, в настоящее время не позволяет размещать картинки, но я постараюсь выписать это: КОЛОНКА A - ДАТА, 1/1/16, 1/3/16, 1/5/16, 1/20/16 , 1/25/16, 2/5/16, 2/10/16 КОЛОНКА B - количество, 1, 3, 2, 1, 5, 3, 1 КОЛОНКА C - Предмет, яблоко, банан, яблоко, апельсин, банан , Apple, Orange Надеюсь, это поможет. Извините, я еще новичок.   -  person Austmado    schedule 30.01.2016

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете использовать формулу массива. Введите следующую формулу и нажмите. CTRL + SHIFT + ВВОД:

=SUM(SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,{"Apple","Banana","Orange"})*{0.5,0.75,1.25})
person SincereApathy    schedule 30.01.2016
comment
Я обновил ответ и понял, что первый опубликованный мной ответ не будет работать, как я думал. Однако я тестировал это. - person SincereApathy; 30.01.2016
comment
Апатия, формула отлично сработала! Однако значения затрат (0,5, 0,75, 1,25) берутся из ячейки на другом листе. Когда я меняю значения на источник ячейки, он не принимает его. Прошу прощения за то, что не включил его в исходную кодировку, я подумал, что это сделало бы его более запутанным. - person Austmado; 30.01.2016
comment
вы можете заменить массив значений стоимости в конце ({0.50, 0.75, 1.25}) диапазоном ячеек, в которых находится стоимость, например, если бы они находились на листе 2 в ячейках от B1 до B3: =SUM(SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,{"Apple","Banana","Orange"})*Sheet2!B1:B3) - person SincereApathy; 30.01.2016
comment
Дальнейшее пояснение - вы используете фигурные скобки {}, если перечисляете массив констант - если вы ссылаетесь на ячейки, вы не используете фигурные скобки, вы просто вводите диапазон. - person SincereApathy; 30.01.2016
comment
Я узнал что-то новое сегодня! Никогда не знала фигурных скобок. Чтобы не бросать гаечный ключ в смесь, но затраты, которые он берет, указаны на нескольких строках. Может быть полезно знать, что это стоимость товаров, проданных для инвентаря, и инвентарь разделен по категориям продуктов в разных строках. - person Austmado; 30.01.2016
comment
например, СТРОКА A - яблоко, банан, апельсин, СТРОКА B - 0,5, 0,75, 1,25, СТРОКА F - коробки, лента, СТРОКА G - 1,10, 0,8 и т. д. - person Austmado; 30.01.2016
comment
Apathy, я пробовал несколько комбинаций с фигурными скобками и без них, взятых с другой страницы, и получаю результаты от 0,00 до #VALUE. - person Austmado; 30.01.2016
comment
Я пытаюсь понять, как использовать несмежный диапазон в формуле массива - никогда раньше не приходилось. Между тем, можно ли создать вспомогательный диапазон, в котором вы объедините соответствующие затраты, чтобы вы могли использовать обычный диапазон, например b1: b3 (можно было бы где-то скрыть его вне поля зрения) - person SincereApathy; 30.01.2016
comment
Я перечитывал ваш пост, и если у вас есть значения вместе в ROW в A2, B2 и C2, вы все равно можете использовать диапазон, вместо Sheet2!B1:B3 это будет Sheet2!A2:C2 - person SincereApathy; 30.01.2016
comment
Apathy, я установил значения items и cogs в скрытой ячейке на странице. У меня он настроен, поэтому, если я добавлю новый продукт в будущем, он автоматически поместит его на нужное место в списке. Таким образом, он будет читать - СТРОКА A - яблоки, бананы и т. Д. СТРОКА B - 0,5, 0,75 и т. Д. Если я добавлю «Виноград» позже, он будет обновлен до «Яблоки», «Виноград», «Бананы» и т. Д. С соответствующей ценой винтиков. Я попытался использовать формулу после ... $ C: $ C, Sheet2! A2: A10) * Sheet2! B2: B10) - и это обнуляет уравнение ... Примечание. Ячейки A&B 3-10 в настоящее время остаются пустыми. - person Austmado; 30.01.2016
comment
можно ли увидеть копию своей книги или снимок экрана? - person SincereApathy; 30.01.2016
comment
Если у вас есть только элементы, перечисленные в СТРОКЕ 1, и только значения стоимости, перечисленные в СТРОКЕ 2, вы можете использовать функцию смещения, чтобы определить свой диапазон, чтобы он увеличивался по мере добавления элементов. Вот новое полное уравнение: =SUM(SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,OFFSET(Sheet2!A1,0,0,1,COUNTA(Sheet2!1:1)))*OFFSET(Sheet2!A2,0,0,1,COUNTA(Sheet2!2:2))) - person SincereApathy; 30.01.2016
comment
Я бы хотел сделать снимок экрана, но я пока не могу загружать изображения, мне все еще нужно еще несколько очков репутации, так как я все еще новичок. Я знаю, что это сделало бы это намного проще. Я попробовал формулу, и она обнуляется. Было бы проще, если бы я заработал оставшиеся очки репутации и разместил новый вопрос с фотографиями? - person Austmado; 31.01.2016
comment
Можете ли вы загрузить снимок экрана на веб-сайт, на котором размещены изображения, и опубликовать ссылку, или загрузить копию книги на диск Google и вставить ссылку? - person SincereApathy; 31.01.2016
comment
imgur.com/a/snvwe Я пометил разные страницы с подробными описаниями для каждой, которые должны помочь больше чем то, что я могу предоставить здесь в настоящее время. Если вам нужна дополнительная информация, я могу обновить ее по мере необходимости. - person Austmado; 31.01.2016
comment
Я создал новую книгу на основе отправленного вами изображения, и формула работает на меня. Если вы вводите формулу общей себестоимости, которую мы создаем, на листе, отличном от данных о продажах, убедитесь, что вы добавляете ссылку на лист в формулу. Вот формула, которую я создал на основе ваших изображений в ячейке B4 на странице прибылей и убытков: =SUM(SUMIFS(Sales!$B:$B,Sales!$A:$A,">="&DATE(2016,1,1),Sales!$A:$A,"<="&DATE(2016,1,31),Sales!$C:$C,OFFSET(Inventory!B1,0,0,1,COUNTA(Inventory!1:1)))*OFFSET(Inventory!B2,0,0,1,COUNTA(Inventory!2:2))) - person SincereApathy; 31.01.2016
comment
Я попытался вставить эту формулу в свою электронную таблицу, и она снова была обнулена. Я попробовал использовать шаблон, который я вам прислал, и он дал мне значение 3. Но когда я складываю винтики вручную (в зависимости от проданного количества), истинное значение составляло 9,25. - person Austmado; 31.01.2016
comment
Причина, по которой вы получите 3 на листе шаблона, заключается в том, что вы не ввели его как формулу массива. После ввода формулы вы должны нажимать CTRL + SHIFT + ENTER (каждый раз) ... вся формула должна содержать {} при правильном вводе. Попробуйте это на обоих листах, и если он по-прежнему не дает правильного результата в вашей реальной электронной таблице, мне нужно будет узнать больше о методе, который вы используете для добавления новых продуктов и шестеренок в 2 верхних скрытых строки, на которые вы ссылаетесь. в формуле. - person SincereApathy; 31.01.2016
comment
Я попробовал CTRL + SHIFT + ENTER в конце формулы в примере шаблона, и он добавил фигурные скобки {} ко всей формуле; однако возвращается с #VALUE - person Austmado; 31.01.2016
comment
Скопируйте и вставьте имеющуюся формулу в шаблон, чтобы я мог ее видеть. Кроме того, дайте мне знать, как вы обновляете ячейки в двух верхних строках. - person SincereApathy; 31.01.2016
comment
Это формула копирования / вставки --- {= СУММ (СУММЕСЛИМН (Продажи! $ B: $ B, Продажи! $ A: $ A, ›= & DATE (2016,1,1), Продажи! $ A: $ A , ‹= & DATE (2016,1,31), Sales! $ C: $ C, OFFSET (Inventory! B1,0,0,1, COUNTA (Inventory! 1: 1))) * OFFSET (Inventory! B2,0 , 0,1, COUNTA (Инвентарь! 2: 2)))} - person Austmado; 31.01.2016
comment
Для обновления страницы инвентаря у меня есть двухэтапный процесс. Есть 3 дополнительные строки под двумя, которые я отправил вам в шаблоне. Строка 1 состоит из кода для нумерации любых ячеек, содержащих слова, с использованием формулы - = IF (B5 = ,, MAX ($ A $ 4: A4) +1) --- Строка ниже представляет собой простое уравнение = CELL для рисования слова по мере их ввода в разные категории, оставляя много пустых ячеек между категориями для дальнейшего расширения. Третья строка использует ту же формулу = CELL, что и вторая, сопоставляя винтики с соответствующим элементом ... вторая часть состоит из формулы = ERROR для нажатия ... - person Austmado; 31.01.2016
comment
... все пустые ячейки до конца списка. Эта формула --- = ЕСЛИОШИБКА (ИНДЕКС ($ B $ 5: $ CM $ 5, MATCH (COLUMN () - COLUMN ($ A $ 2), $ B $ 4: $ CM $ 4,0)),) --- Это формулы прямо из моих таблиц. Когда я добавляю новый элемент в инвентарь, он помещает его в соответствующее место в списке. - person Austmado; 31.01.2016
comment
Когда я скопировал и вставил формулу, которую вы только что вставили, я получил ту же ошибку #Value, я прошел через формулу, чтобы увидеть, где возникла ошибка. Функции СМЕЩЕНИЯ не работали должным образом. Формула была совершенно правильной, но по какой-то причине функции СМЕЩЕНИЯ не работают при вставке. Итак, я вернулся и вручную ввел конец формулы, начиная с первой функции СМЕЩЕНИЕ после продажи! $ C: $ C, и это сработало. Ты мог бы попробовать это ... Если это не поможет, я не знаю, смогу ли я оказать гораздо больше помощи без реальной рабочей тетради, лежащей передо мной. - person SincereApathy; 31.01.2016
comment
Я создал шаблон на основе той, что есть в моей электронной таблице, со всеми формулами. Как мне отправить его вам? - person Austmado; 31.01.2016
comment
Можете ли вы загрузить его на гугл диск или один диск и отправить ссылку? - person SincereApathy; 31.01.2016
comment
drive.google.com/open?id=0B3psOsVXjkQENDVpcm94MUFXeDQ ---- Это должно сработать для Google Drive. Никогда раньше не использовал. - person Austmado; 01.02.2016
comment
Я не могу открыть его, пока вы не предоставите доступ. Вы должны были получить электронное письмо. - person SincereApathy; 01.02.2016
comment
Я открыл его для тебя. Сообщите мне, работает ли он. - person Austmado; 01.02.2016
comment
Мне удалось заставить его работать, изменив способ установки размера массива с помощью функции смещения. Используя функцию COUNTA, он игнорирует только действительно пустые ячейки. Таким образом, он все еще считал ячейки с формулами, возвращающими нулевые значения. Я изменил его, чтобы взять МАКСИМАЛЬНОЕ из номеров индексов, которые вы установили в строке 4 на странице инвентаризации. Вот формула, которую я использовал для январских COGS: =SUM(SUMIFS(Sales!$G:$G,Sales!$A:$A,">="&DATE(2016,1,1),Sales!$A:$A,"<="&DATE(2016,1,31),Sales!$H:$H,OFFSET(Inventory!B2,0,0,1,MAX(Inventory!4:4)))*OFFSET(Inventory!B3,0,0,1,MAX(Inventory!4:4))) - person SincereApathy; 01.02.2016
comment
Помните, что по какой-то причине это не всегда будет работать при копировании и вставке, поэтому вам может потребоваться ввести функции СМЕЩЕНИЯ вручную. Кроме того, вам может потребоваться повторно ввести год в функциях даты (не спрашивайте меня, почему, это просто то, что я заметил, работает, когда я повторно набираю его, но не после вставки). - person SincereApathy; 01.02.2016
comment
Апатия, сработало !! Большое спасибо! Вы действительно мастер программирования. - person Austmado; 02.02.2016
comment
Рад слышать. Чтобы не сбивать с толку, меня беспокоило то, что функция смещения делала это настолько привередливым. Итак, я нашел альтернативный способ сделать диапазоны динамическими. Вот новая формула, которую я придумал: =SUM(SUMIFS(Sales!$G:$G,Sales!$A:$A,">="&DATE(2016,1,1),Sales!$A:$A,"<="&DATE(2016,1,31),Sales!$H:$H,Inventory!$B$2:INDEX(Inventory!$B$2:$CZ$2,MAX(Inventory!4:4)))*(Inventory!$B$3:INDEX(Inventory!$B$3:$CZ$3,MAX(Inventory!4:4)))) ... это может быть лучшим вариантом, поскольку смещение будет пересчитываться каждый раз, когда изменяется какая-либо часть книги, причем это будет пересчитываться только при изменении соответствующих данных. - person SincereApathy; 02.02.2016
comment
Апатия, они оба работали на меня копипастом. Я скопирую этот код для использования в будущем. Еще раз спасибо за вашу помощь! Вы были настоящим спасителем! - person Austmado; 06.02.2016