Flatik.ru

Перейти на главную страницу

Поиск по ключевым словам:

страница 1
Excel. Подсчет числа ячеек, содержащих символ (букву)

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

Шаг 1. Определим, содержится ли искомый символ в ячейке (рис. 1).

1. содержит ли ячейка символ.bmp

Рис. 1. Содержится ли искомый символ в выбранной ячейке

НАЙТИ($C$1;A1) – ищет символ, хранящийся в $C$1, в строке А1; если находит, возвращает позицию этого символа в строке; если не находит, то возвращает ошибку #ЗНАЧ!

Шаг 2. Избавляемся от ошибочных значений (рис. 2).



2. замена ошибочных значений нулем.bmp

Рис. 2. Замена ошибочных значений нулями

ЕСЛИОШИБКА(НАЙТИ($C$1;A1);0) – возвращает 0, если значение функции НАЙТИ($C$1;A1) выдает ошибку, в противном случае возвращает само значение функции НАЙТИ($C$1;A1)

Шаг. 3. Заменяем номер позиции на единицу



3. приведение всех положительных значений к единице.bmp

Рис. 3. Приведение всех положительных значений к единице

=ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0) – преобразование, позволяющее для любого значения позиции (не ошибки) получить 1; номер позиции, возвращаемый функцией НАЙТИ($C$1;A1), делится на 1000 и округляется до ближайшего целого (если быть точным, то ячейка Excel максимально может содержать 32 767 символов, так что «для надежности» можно заменить 1000 на 32 767 ; в этом случае, вы избежите ошибки при любом содержимом ячеек).

В качестве альтернативы можно использовать функцию ЕСЛИ:

=ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1)>0;1);0) – если значение функции НАЙТИ($C$1;A1) больше нуля, то функция ЕСЛИ заменяем это значение на единицу. Заметьте, что третий аргумент в функции ЕСЛИ отсутствует, так как функция НАЙТИ($C$1;A1) не может вернуть значение меньше или равно 0 (функция НАЙТИ($C$1;A1) возвращает только положительные значения или ошибку #ЗНАЧ!).

Шаг. 4. Суммируем все единички. Напомню, что единица соответствует ячейке, содержащей искомый символ, а ноль – ячейке, не содержащей искомый символ (рис. 4).



4. суммирование ячеек, содержащих искомый символ.bmp

Рис. 4. Суммирование ячеек, содержащих искомый символ

В ячейке С2 я использовал формулу массива:

{=СУММ(ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1:A14)/1000;1);0))}

Функция последовательно вычисляет значения =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0), =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A2)/1000;1);0) … и так вплоть до =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A14)/1000;1);0)

Результат вычисления (0 или 1) заносится в память, формируя виртуальный массив {1,1,1,0,1,1,0,1,1,0,0,0,1}. Функция СУММ просто суммирует все единицы.



Альтернативная функция: =СУММ(ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1:A14)>0;1);0))

P.S. Не вводите фигурные скобки в строку формул, а наберите всю формулу без фигурных скобок и нажмите одновременно Ctrl+Shift+Enter

Excel. Подсчет числа ячеек, содержащих символ (букву)

У меня получилось решить задачу без использования кода vba на основе формулы массива. Если вы не использовали ранее такие формулы, рекомендую начать с заметки Excel. Введение в фор

18.82kb.

10 10 2014
1 стр.


Практическая работа №15 «Microsoft Excel 2007. Абсолютная и относительная адресация» Относительные ссылки

До сих пор мы использовали относительную адресацию ячеек. При автозаполнении в каждой следующей записи в формуле изменялись имена ячеек. Такие имена ячеек или, точнее сказать, ссыл

38.26kb.

10 10 2014
1 стр.


Microsoft Excel 2003 1 Запуск Excel 1

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

204.02kb.

14 12 2014
1 стр.


Microsoft Excel Общие сведения

Программа Microsoft Excel является одной из важных составных частей пакета программ Microsoft Office. Назначение программы Microsoft Excel (или просто Excel) – и создание и обработ

131.77kb.

14 12 2014
1 стр.


Программа по математике 5 9 класс арифметика числа и вычисления. Натуральные числа. Десятичная система счисления. Арифметические действия с натуральными числами. Свойства арифметических действий. Степень с натуральным показателем

Делители и кратные числа. Признаки делимости. Простые числа. Разложение числа на простые множители

99.33kb.

17 12 2014
1 стр.


Символизм

Символ содержит в себе перспективу безгра­ничного развертывания смыслов. "Символ окно в бесконечность" (Ф. Сологуб). Кроме того, символ является и полноценным образом, его мож­но в

82.73kb.

10 10 2014
1 стр.


Формулировка задания: ms excel. Создание таблиц. Ввод формул

Цель работы: Знакомство с основными навыками работы в электронных таблицах: ввод данных в ячейки таблицы, форматирование ячеек таблицы, ввод простых формул, создание обрамления, ре

359.96kb.

14 12 2014
4 стр.


Название города

Первую цифру 2 замени на 14-ю букву алфавита, вторую цифру 2 замени на 13-ю букву алфавита. Подумай и замени последнюю оставшуюся цифру на букву так, чтобы получилось всем известно

25.87kb.

13 10 2014
1 стр.