☰

CountIf()-данные из разных книг

tag-icon • tag-icon microsoft-excel worksheet-function
CountIf()-данные из разных книг

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

picker  | lines
--------+------
zf      |    4
ag      |   22
jw      |    8
zf      |    7
cr      |   39
ag      |   10
ag      |   16
ag      |    1
cr      |    4
ag      |    2

... и подсчитать количество раз, когда встречаются инициалы, и собрать их в совершенно другой рабочей книге. Вот формула, которая у меня получилась на данный момент:

=COUNTIF('\\Shipping\C\Agri-Pet Log\2013\November\December\[December 3, 2013.xls]Ag & Pet Shipping'!$B$4:$B$200,"CR")+COUNTIF('\\Foodcomp\c\Dog Food Log\2013\December\[(df) December 3, 2013.xls]Dog Food'!$B$4:$B$300,"CR")

Это отлично работает, пока исходные рабочие книги открыты на моем компьютере, но если они закрыты, все, что я получаю, это #VALUE! В идеале я бы хотел также иметь что-то, что суммирует числа, связанные с инициалами в ячейках рядом с ними, но по одному за раз.

решение1

Семейство COUNTIF/SUMIFфункций не работает с закрытыми рабочими книгами, попробуйте SUMPRODUCT, например, для одной рабочей книги, чтобы повторить ваш подсчет

=SUMPRODUCT(('\\Shipping\C\Agri-Pet Log\2013\November\December\[December 3, 2013.xls]Ag & Pet Shipping'!$B$4:$B$200="CR")+0)

и получить сумму для столбца C, когда B = "CR"

=SUMPRODUCT(('\\Shipping\C\Agri-Pet Log\2013\November\December\[December 3, 2013.xls]Ag & Pet Shipping'!$B$4:$B$200="CR")+0,'\\Shipping\C\Agri-Pet Log\2013\November\December\[December 3, 2013.xls]Ag & Pet Shipping'!$C$4:$C$200)

решение2

Если вы хотите объединить информацию из нескольких закрытых рабочих книг, один из способов — использоватьMicrosoft PowerQuery. Это надстройка бизнес-аналитики для Excel, которая способна запрашивать, фильтровать и агрегировать информацию из многих источников, включая другие рабочие книги.

Хотя это немного монстр, чтобы разобраться в нем, он бесплатный и полностью поддерживается Microsoft. Вам не нужно изучать кодирование, чтобы делать большинство вещей.

Он, безусловно, сможет сделать то, что вы хотите.

Единственное, за чем нужно следить, это за тем, чтобы другие люди не открывали рабочие книги. Это блокирует их и приведет к сбою запросов. Если это произойдет, вам придется подождать, пока рабочие книги будут закрыты, а затем просто обновить запрос.

Связанный контент