В Excel, как мне умножить разделенные значения на отдельные значения в другом столбце?

Question

У меня есть два столбца данных. Столбец A содержит элементы, столбец B содержит количество элементов. В приведенном ниже примере я хотел бы посчитать количество "вилок":

 - A1: spoon|fork|knife
 - A2: spoon|bottle|fork|piston
 - B1: 2|1|1
 - B2: 6|2|4|3

Или посмотрите на изображение ниже:

Всего будет пять вилок; одна вилка в ряду 1 и пять вилок в ряду 2.

Как бы я посчитал это общее количество вилок?

Answer 1

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

1. Вместо переноса данных в Excel сохраните исходные данные в виде файла .txt.
2. Откройте Excel и откройте новую пустую книгу.
3. Под лентой данных используйте параметр "Получить внешние данные" "Из текста" (слева)
4. Выберите файл .txt, который вы сохранили, вы попадете в Мастер импорта текста.
5. На шаге 1 выберите "С разделителями"
6. На шаге 2 выберите "Другое", а затем введите символ разделителя, который вы используете (в вашем случае это «|»).
7. Пропустите шаг 3, нажав Готово.

8. Выберите "Новый рабочий лист" в качестве места назначения вывода

   Теперь ваши данные будут в чуть лучшем формате. Обратите внимание, что вы можете выполнить описанные выше шаги, используя функцию "Текст в столбцы", если данные уже есть в Excel.

9. На Листе 1 (или любом пустом листе) сделайте заголовки столбцов:

   • A1: ПУНКТ
   • A2: КОЛИЧЕСТВО

10. Теперь вот как вы превращаете смешанные данные в пригодные для использования данные:

    В A2 поместите эту формулу:

    =OFFSET(Sheet4!$A$1,INT((ROW()-2)/4),MOD(ROW()-2,4))  
    

    • Убедитесь, что вы заменили "Sheet4!$A$1" с ячейкой, из которой начинаются ваши данные. В вашем примере это будет клетка с первой "ложкой"
    • Убедитесь, что у вас есть знаки доллара (т.е. не A1 , а $A$1)
    • Если необходимо, замените число 4 в формуле количеством столбцов данных элемента, в вашем случае максимальное значение равно 4).

11. В B2 повторите то, что вы делали в A2, за исключением замены "Sheet4!$A$1" с ячейкой, с которой начинаются ваши данные по количеству. Для меня это выглядит так:

    =OFFSET(Sheet4!$E$1,INT((ROW()-2)/4),MOD(ROW()-2,4))  
    

12. Теперь все, что вам нужно сделать, это заполнить эту формулу, пока у вас не будет строки для каждой комбинации «Товар / количество». С вашим примером это выглядит так:

    ITEM    QUANTITY
    spoon   2
    fork    1
    knife   1
    0       0
    spoon   6
    bottle  2
    fork    4
    piston  3
    

13. Затем скопируйте столбец A из приведенного выше результата и вставьте его на НОВЫЙ чистый лист в той же книге. (Я использую Sheet2)

14. Пока вставленные данные все еще выбраны, на ленте данных выберите "Удалить дубликаты".

15. В появившемся диалоговом окне установите флажок "Мои данные имеют заголовки" и нажмите кнопку "ОК".

    Вы должны иметь это сейчас:

    ITEM  
    spoon  
    fork  
    knife  
    0  
    spoon  
    bottle  
    

16. В B1 создайте заголовок столбца "КОЛИЧЕСТВО"

17. В B2 положить следующую формулу:

    =SUMIF(Sheet1!$A:$A,$A2,Sheet1!$B:$B)
    

    Обязательно замените "Лист1" тем листом, который вы использовали для первых формул.

Вот результат!

ITEM    QUANTITY  
spoon   8  
fork    5  
knife   1  
0       0  <-- Go ahead and delete this row if you want!  
spoon   8  
bottle  2

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

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

Answer 2

Используйте COUNTIF . Например, в строке 9 введите: =COUNTIF(A1:A7, "star") а затем вы увидите количество вхождений.

Также вы можете использовать отличный онлайн-инструмент: http://string-functions.com/countsubstrings.aspx (в первом поле введите желаемое слово, например, fork , а затем вставьте текст ниже)

(источник)