Блог

Предположим, у меня есть следующая таблица:

   State        CompanyTypes     Year      Sales
  AZ           A, C             2008      
  CA           B, C, D          2009      
  TX           C                2007      
  WA           A, D             2008      
  

Мне нужно заполнить Sales столбец на основе SUM объема продаж по всем типам компаний для определенного штата в определенном году.

Исходная таблица со всеми данными выглядит следующим образом:

  Year    State     CompanyType   TotalSales  
 2008    AZ        A             100
 2008    AZ        C             500
 2009    CA        B             9000
 2009    CA        C             15664
 2009    CA        D             12351
 2008    TX        C             5
 2009    WA        A             789
  

Я знаком с SUMIF , но я не знаю, как заполнить Sales столбец первой таблицы из-за значений, разделенных запятыми, в столбце CompanyTypes.

Кто-нибудь знает??

Рэй, я заметил, что таблица продаж обладает следующим свойством: в каждой строке комбинация состояния и года уникальна в том смысле, что «AZ» и «2008» появляются только один раз среди всех строк. Если это верно, то эта формула должна работать

 =SUMIFS(D4:D10,A4:A10,I4,B4:B10,G4)
  

Эта формула находит сумму всех продаж в определенном штате за конкретный год, независимо от компании. Обратите внимание, что это не сработает, если у вас есть что-то подобное в таблице sales.

 AZ  A  2008
AZ  C  2008
  

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

 =IF(
    IFERROR(
        FIND("A",$H4),
        0
    ),
    SUMIFS(
        D$4:D$10,
        C$4:C$10,"A",
        A$4:A$10,$I4,
        B$4:B$10,$G4
    ),
    0
)
 IF(
    IFERROR(
        FIND("B",$H4),
        0
    ),
    SUMIFS(
        D$4:D$10,
        C$4:C$10,"B",
        A$4:A$10,$I4,
        B$4:B$10,$G4
    ),
    0
)
 IF(
    IFERROR(
        FIND("C",$H4),
        0
    ),
    SUMIFS(
        D$4:D$10,
        C$4:C$10,"C",
        A$4:A$10,$I4,
        B$4:B$10,$G4
    ),
    0
)
 IF(
    IFERROR(
        FIND("D",$H4),
        0
    ),
    SUMIFS(
        D$4:D$10,
        C$4:C$10,"D",
        A$4:A$10,$I4,
        B$4:B$10,$G4
    ),
    0
)
  

Вы можете скопировать и вставить это непосредственно в строку формул.
Эта формула содержит 4 блока «ЕСЛИ», по одному для каждого типа компании. Если их больше, следует также добавить соответствующие блоки ‘IF’.

Ссылка на документы Google, содержащие две таблицы. Формула была протестирована в Excel 2010. Google Docs показывает не формулу, а сумму, которую она рассчитала при импорте файла Excel.

Надеюсь, это поможет.

Основываясь на точном формате в типах компаний «A, B, C» (т. Е. пробелы имеют значение), вы можете использовать следующую формулу. Я использовал именованный диапазон «CT» для CompanyType, «TS» для TotalSales, «State» для State, все в исходной таблице. У меня были типы компаний в столбце B, а первый столбец состояния — A.

 =SUMIFS(TS, CT, MID(B2,1,1), State, A2)   
 SUMIFS(TS, CT, MID(B2,4,1), State, A2)  
 SUMIFS(TS, CT, MID(B2,7,1), State, A2)
  

Просто поместите это в первую ячейку Sales (например, предполагаемая строка 2) и заполните ее.