Вече научихме как да броим клетки, които съдържат определен текст, използвайки функцията COUNTIF. В тази статия ще научим как да броим колко пъти дадена дума се появява в Excel диапазон. С други думи, ние ще преброим колко пъти една дума се е появила в диапазон на Excel.
Обща формула
= SUMPRODUCT (LEN (обхват) -LEN (SUBSTITUTE (диапазон, дума, "")))/LEN (дума)
Диапазон: Диапазонът, в който се опитвате да преброите конкретната дума.
Дума: Думата, която искате да преброите.
Да вземем пример и да разберем как работи.
Пример: Пребройте „щастлива“ дума в диапазона на Excel.
Тук имаме няколко изречения в различни клетки. Трябва да преброим появата на дума „щастлив“ в този диапазон.
Напишете тази формула в клетка D2.
= СУМПРОДУКТ (LEN (A2: A4) -LEN (ЗАМЕСТНИК (A2: A4, D1, "")))/LEN (D1)
Използването на тази функция excel брои колко пъти конкретната дума „щастлив“ се появява в обхвата А2: А4. Тази формула на Excel връща броя като 4.
Как работи?
Идеята е да се получи сума от броя на знаците на дадена дума в диапазона и след това да се раздели на дължината на думата. Например, ако Happy се среща 4 пъти в диапазон, общата му дължина е 20 (4*5) в диапазона. Ако разделим 20 на 5, получаваме 4. Кой е броят на думите в обхвата.
Нека го съборим отвътре.
LEN (A2: A4): това връща броя на знаците във всяка клетка като масив {49; 27; 34}.
Следващ LEN (ЗАМЕСТНИК (A2: A4, D1, "")): Функцията за заместване възстановява думата в D1 с „“ във всяка клетка от диапазон A2: A4. Тогава функцията Len връща броя на знаците от тези заместени изречения в масив {39; 22; 29}.
LEN (D1): това връща дължината на думата в D1 която 5 (щастлив).
Сега формулата е опростена до СУМПРОДУКТ ({49; 27; 34} - {39; 22; 29})/5. След изваждане на масиви, SUMPRODUCT има СУМПРОДУКТ ({10; 5; 5})/5. Функцията добавя масива и получаваме 20/5. Което ни дава резултата 4.
Преброяване без регистрация
Тъй като SUBSTITUTE е чувствителен към регистър, горната формула ще игнорира всяка дума, която няма същия регистър, т.е. „Happy“. За да накараме горната формула да игнорира регистъра, трябва да променим регистъра на всяка дума в регистър на думата, която търсим. Ето как се прави.
= SUMPRODUCT (LEN (A2: A4) -LEN (SUBSTITUTE (UPPER (A2: A4), UPPER (D1), "")))/LEN (D1)
Сега случаят вече няма значение. Тази функция ще брои всяка дума в D1, независимо от регистъра.
Възможни грешки:
Ще бъде отчетена част от думата на друга дума: В този пример, ако имахме дума „щастие“ (само например знам, че няма такава дума), тя също би била отчетена. За да избегнете това, бихте могли да обградите думите с точки, “” & D1 & “”. Но когато думата се появи първа или последна в изречението, това също ще се провали.
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност: По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel: Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel: Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel: Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.