Перенос данных из таблицы по условиям в заданный столбец
Штурмовик
Дата: Пятница, 12.01.2018, 16:50 |
Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Добрый день. Не могу сообразить как прописать формулами: из исходной таблицы нужно выбрать не пустые значения по поставщику и виду а перенести данные по сумме в столбец с датой платежа. Пример во вложенном файле.
Добрый день. Не могу сообразить как прописать формулами: из исходной таблицы нужно выбрать не пустые значения по поставщику и виду а перенести данные по сумме в столбец с датой платежа. Пример во вложенном файле. Штурмовик
пытаюсь освоить
Ответить
Сообщение Добрый день. Не могу сообразить как прописать формулами: из исходной таблицы нужно выбрать не пустые значения по поставщику и виду а перенести данные по сумме в столбец с датой платежа. Пример во вложенном файле. Автор - Штурмовик Дата добавления - 12.01.2018 в 16:50
Che79
Дата: Пятница, 12.01.2018, 16:52 |
Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
Пример во вложенном файле
Штурмовик , файл не прикрепился, обратите внимание на размер. Д.б. не более 100 кб.
Пример во вложенном файле
Штурмовик , файл не прикрепился, обратите внимание на размер. Д.б. не более 100 кб.Che79
Делай нормально и будет нормально!
Ответить
Сообщение Пример во вложенном файле
Штурмовик , файл не прикрепился, обратите внимание на размер. Д.б. не более 100 кб.Автор - Che79 Дата добавления - 12.01.2018 в 16:52
Штурмовик
Дата: Пятница, 12.01.2018, 16:54 |
Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
файл прикрепил
пытаюсь освоить
Сообщение отредактировал Штурмовик - Пятница, 12.01.2018, 16:56
Ответить
Сообщение файл прикрепил Автор - Штурмовик Дата добавления - 12.01.2018 в 16:54
Che79
Дата: Пятница, 12.01.2018, 16:55 |
Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
снова ничего. обратите внимание на размер. Д.б. не более 100 кб.
снова ничего. обратите внимание на размер. Д.б. не более 100 кб.
Che79
Делай нормально и будет нормально!
Ответить
Сообщение снова ничего. обратите внимание на размер. Д.б. не более 100 кб.
Автор - Che79 Дата добавления - 12.01.2018 в 16:55
Штурмовик
Дата: Пятница, 12.01.2018, 16:57 |
Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
снова ничего. Цитата Che79, 12.01.2018 в 16:52, в сообщении № 2 ( писал(а)):
исправился
снова ничего. Цитата Che79, 12.01.2018 в 16:52, в сообщении № 2 ( писал(а)):
исправилсяШтурмовик
пытаюсь освоить
Ответить
Сообщение снова ничего. Цитата Che79, 12.01.2018 в 16:52, в сообщении № 2 ( писал(а)):
исправилсяАвтор - Штурмовик Дата добавления - 12.01.2018 в 16:57
Che79
Дата: Пятница, 12.01.2018, 17:05 |
Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
Штурмовик , уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?
Штурмовик , уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?Che79
Делай нормально и будет нормально!
Ответить
Сообщение Штурмовик , уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?Автор - Che79 Дата добавления - 12.01.2018 в 17:05
Che79
Дата: Пятница, 12.01.2018, 17:10 |
Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
Такой вариант. В С8 и протянуть Код
=СУММПРОИЗВ($C$27:$C$30*($D$27:$D$30=C$7)*(ЛЕВБ($A$27:$A$30;8)=ЛЕВБ($A8;8))*($B$27:$B$30=$B8))
+ формат ячейки 0;;; для скрытия нулей в итоговой таблице
Такой вариант. В С8 и протянуть Код
=СУММПРОИЗВ($C$27:$C$30*($D$27:$D$30=C$7)*(ЛЕВБ($A$27:$A$30;8)=ЛЕВБ($A8;8))*($B$27:$B$30=$B8))
+ формат ячейки 0;;; для скрытия нулей в итоговой таблице Che79
Делай нормально и будет нормально!
Сообщение отредактировал Che79 - Пятница, 12.01.2018, 17:10
Ответить
Сообщение Такой вариант. В С8 и протянуть Код
=СУММПРОИЗВ($C$27:$C$30*($D$27:$D$30=C$7)*(ЛЕВБ($A$27:$A$30;8)=ЛЕВБ($A8;8))*($B$27:$B$30=$B8))
+ формат ячейки 0;;; для скрытия нулей в итоговой таблице Автор - Che79 Дата добавления - 12.01.2018 в 17:10
Штурмовик
Дата: Пятница, 12.01.2018, 17:23 |
Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?
поставщик и тип сырья должны совпадать - это в файле я ошибся.
уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?
поставщик и тип сырья должны совпадать - это в файле я ошибся.Штурмовик
пытаюсь освоить
Ответить
Сообщение уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?
поставщик и тип сырья должны совпадать - это в файле я ошибся.Автор - Штурмовик Дата добавления - 12.01.2018 в 17:23
Штурмовик
Дата: Пятница, 12.01.2018, 17:26 |
Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Такой вариант. В С8 и протянуть
а как можно понять (расшифровать) формулу? Логику хочется понять...
Такой вариант. В С8 и протянуть
а как можно понять (расшифровать) формулу? Логику хочется понять...Штурмовик
пытаюсь освоить
Ответить
Сообщение Такой вариант. В С8 и протянуть
а как можно понять (расшифровать) формулу? Логику хочется понять...Автор - Штурмовик Дата добавления - 12.01.2018 в 17:26
Che79
Дата: Пятница, 12.01.2018, 17:39 |
Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
СУММПРОИЗВ(), в данном случае, перемножает массив сумм платежей и массивы доп. условий по заданным критериям, которые дают 1 (ИСТИНА), если критерий выполняется и 0 (ЛОЖЬ) если критерий не выполняется. Таким образом на выходе получим массив платежей и массивы (в нашем случае их будет 3) из 0 и 1. Далее эти нули или единицы перемножаются с суммами платежей и в итоге мы получаем только ту сумму, которая удовлетворяет всем критериям сразу - дате, поставщику и типу сырья. Также на сайте есть статья Суммирование по нескольким критериям .
СУММПРОИЗВ(), в данном случае, перемножает массив сумм платежей и массивы доп. условий по заданным критериям, которые дают 1 (ИСТИНА), если критерий выполняется и 0 (ЛОЖЬ) если критерий не выполняется. Таким образом на выходе получим массив платежей и массивы (в нашем случае их будет 3) из 0 и 1. Далее эти нули или единицы перемножаются с суммами платежей и в итоге мы получаем только ту сумму, которая удовлетворяет всем критериям сразу - дате, поставщику и типу сырья. Также на сайте есть статья Суммирование по нескольким критериям . Che79
Делай нормально и будет нормально!
Ответить
Сообщение СУММПРОИЗВ(), в данном случае, перемножает массив сумм платежей и массивы доп. условий по заданным критериям, которые дают 1 (ИСТИНА), если критерий выполняется и 0 (ЛОЖЬ) если критерий не выполняется. Таким образом на выходе получим массив платежей и массивы (в нашем случае их будет 3) из 0 и 1. Далее эти нули или единицы перемножаются с суммами платежей и в итоге мы получаем только ту сумму, которая удовлетворяет всем критериям сразу - дате, поставщику и типу сырья. Также на сайте есть статья Суммирование по нескольким критериям . Автор - Che79 Дата добавления - 12.01.2018 в 17:39
Штурмовик
Дата: Пятница, 12.01.2018, 17:43 |
Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
пытаюсь освоить
Ответить
Сообщение Понял. Спасибо.Автор - Штурмовик Дата добавления - 12.01.2018 в 17:43
Штурмовик
Дата: Понедельник, 15.01.2018, 12:54 |
Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Добрый день! СУММПРОИЗВ() не работает при увеличении диапазонов. Или я не правильно прописываю синтаксис. Есть ли другие варианты? Исходные данные и итоговая таблица (на разных листах) во вложенном файле.
Добрый день! СУММПРОИЗВ() не работает при увеличении диапазонов. Или я не правильно прописываю синтаксис. Есть ли другие варианты? Исходные данные и итоговая таблица (на разных листах) во вложенном файле. Штурмовик
пытаюсь освоить
Ответить
Сообщение Добрый день! СУММПРОИЗВ() не работает при увеличении диапазонов. Или я не правильно прописываю синтаксис. Есть ли другие варианты? Исходные данные и итоговая таблица (на разных листах) во вложенном файле. Автор - Штурмовик Дата добавления - 15.01.2018 в 12:54
Che79
Дата: Понедельник, 15.01.2018, 13:06 |
Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
Здравствуйте. Проверяйте. Для С8 Код
=СУММПРОИЗВ(($C$27:$C$30+$E$27:$E$30+$G$27:$G$30+$I$27:$I$30)*($D$27:$D$30=C$7)*(($B$27:$B$30=$B8)*($A$27:$A$30=$A8)))
Здравствуйте. Проверяйте. Для С8 Код
=СУММПРОИЗВ(($C$27:$C$30+$E$27:$E$30+$G$27:$G$30+$I$27:$I$30)*($D$27:$D$30=C$7)*(($B$27:$B$30=$B8)*($A$27:$A$30=$A8)))
Che79
Делай нормально и будет нормально!
Ответить
Сообщение Здравствуйте. Проверяйте. Для С8 Код
=СУММПРОИЗВ(($C$27:$C$30+$E$27:$E$30+$G$27:$G$30+$I$27:$I$30)*($D$27:$D$30=C$7)*(($B$27:$B$30=$B8)*($A$27:$A$30=$A8)))
Автор - Che79 Дата добавления - 15.01.2018 в 13:06
Штурмовик
Дата: Понедельник, 15.01.2018, 13:19 |
Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Считает, но только сумму без условия по дате. Т.е. если даты по строке 27 будут разные то это не учитывается, а считается вся сумма по этой строке.
Считает, но только сумму без условия по дате. Т.е. если даты по строке 27 будут разные то это не учитывается, а считается вся сумма по этой строке.Штурмовик
пытаюсь освоить
Ответить
Сообщение Считает, но только сумму без условия по дате. Т.е. если даты по строке 27 будут разные то это не учитывается, а считается вся сумма по этой строке.Автор - Штурмовик Дата добавления - 15.01.2018 в 13:19
Che79
Дата: Понедельник, 15.01.2018, 13:20 |
Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)
Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл) Che79
Делай нормально и будет нормально!
Ответить
Сообщение Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл) Автор - Che79 Дата добавления - 15.01.2018 в 13:20
Штурмовик
Дата: Понедельник, 15.01.2018, 13:32 |
Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
ПОИСКПОПОЗ в данном случае может как то помочь?
ПОИСКПОПОЗ в данном случае может как то помочь? Штурмовик
пытаюсь освоить
Ответить
Сообщение ПОИСКПОПОЗ в данном случае может как то помочь? Автор - Штурмовик Дата добавления - 15.01.2018 в 13:32
Штурмовик
Дата: Понедельник, 15.01.2018, 13:35 |
Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)
В таком случае у меня исходная таблица будет неимоверных размеров, да и ее заполнение автоматизировать будет сложнее.
Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)
В таком случае у меня исходная таблица будет неимоверных размеров, да и ее заполнение автоматизировать будет сложнее.Штурмовик
пытаюсь освоить
Ответить
Сообщение Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)
В таком случае у меня исходная таблица будет неимоверных размеров, да и ее заполнение автоматизировать будет сложнее.Автор - Штурмовик Дата добавления - 15.01.2018 в 13:35
Che79
Дата: Понедельник, 15.01.2018, 13:49 |
Сообщение № 18
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
тогда так Код
=СУММПРОИЗВ((($C$27:$C$30*($D$27:$D$30=C$7))+($E$27:$E$30*($F$27:$F$30=C$7))+($G$27:$G$30*($H$27:$H$30=C$7))+($I$27:$I$30*($J$27:$J$30=C$7)))*($B$27:$B$30=$B8)*($A$27:$A$30=$A8))
тогда так Код
=СУММПРОИЗВ((($C$27:$C$30*($D$27:$D$30=C$7))+($E$27:$E$30*($F$27:$F$30=C$7))+($G$27:$G$30*($H$27:$H$30=C$7))+($I$27:$I$30*($J$27:$J$30=C$7)))*($B$27:$B$30=$B8)*($A$27:$A$30=$A8))
Che79
Делай нормально и будет нормально!
Ответить
Сообщение тогда так Код
=СУММПРОИЗВ((($C$27:$C$30*($D$27:$D$30=C$7))+($E$27:$E$30*($F$27:$F$30=C$7))+($G$27:$G$30*($H$27:$H$30=C$7))+($I$27:$I$30*($J$27:$J$30=C$7)))*($B$27:$B$30=$B8)*($A$27:$A$30=$A8))
Автор - Che79 Дата добавления - 15.01.2018 в 13:49
vikttur
Дата: Понедельник, 15.01.2018, 14:25 |
Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;);C$7;ИНДЕКС($C$27:$I$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;))
Если вынести определение строки в отдельную ячейку:Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;$B16;);C$7;ИНДЕКС($C$27:$I$30;$B16;))
А если строки не меняют порядка, то и номер строки вычислять не нужно:Код
=СУММЕСЛИ($D27:$J27;C$7;$C27:$I27)
Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;);C$7;ИНДЕКС($C$27:$I$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;))
Если вынести определение строки в отдельную ячейку:Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;$B16;);C$7;ИНДЕКС($C$27:$I$30;$B16;))
А если строки не меняют порядка, то и номер строки вычислять не нужно:Код
=СУММЕСЛИ($D27:$J27;C$7;$C27:$I27)
vikttur
Ответить
Сообщение Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;);C$7;ИНДЕКС($C$27:$I$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;))
Если вынести определение строки в отдельную ячейку:Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;$B16;);C$7;ИНДЕКС($C$27:$I$30;$B16;))
А если строки не меняют порядка, то и номер строки вычислять не нужно:Код
=СУММЕСЛИ($D27:$J27;C$7;$C27:$I27)
Автор - vikttur Дата добавления - 15.01.2018 в 14:25
Che79
Дата: Понедельник, 15.01.2018, 14:45 |
Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
Что-то я напрочь забыл про СУММЕСЛИ(), vikttur , спасибо за подсказку! Тогда забываем про СУММПРОИЗВ(), в С8 Код
=СУММЕСЛИ($D19:$XFD19;C$7;$C19:$XFC19)
, в исходной таблице просто протягиваете вправо ячейки "Сумма", "Дата оплаты", а Итоговую таблицу протягивайте вправо по датам насколько нужно.
Что-то я напрочь забыл про СУММЕСЛИ(), vikttur , спасибо за подсказку! Тогда забываем про СУММПРОИЗВ(), в С8 Код
=СУММЕСЛИ($D19:$XFD19;C$7;$C19:$XFC19)
, в исходной таблице просто протягиваете вправо ячейки "Сумма", "Дата оплаты", а Итоговую таблицу протягивайте вправо по датам насколько нужно. Che79
Делай нормально и будет нормально!
Сообщение отредактировал Che79 - Понедельник, 15.01.2018, 15:10
Ответить
Сообщение Что-то я напрочь забыл про СУММЕСЛИ(), vikttur , спасибо за подсказку! Тогда забываем про СУММПРОИЗВ(), в С8 Код
=СУММЕСЛИ($D19:$XFD19;C$7;$C19:$XFC19)
, в исходной таблице просто протягиваете вправо ячейки "Сумма", "Дата оплаты", а Итоговую таблицу протягивайте вправо по датам насколько нужно. Автор - Che79 Дата добавления - 15.01.2018 в 14:45