Подсчет заполненных ячеек в microsoft excel

Как выполнить подсчёт ячеек со значением

Здравствуйте, дорогие читатели.

Перед началом данной темы я бы хотел вам посоветовать отличный обучающий продукт по теме экселя, по названием « Неизвестный Excel » , там всё качественно и понятно изложено. Рекомендую.

Ну а теперь вернёмся к теме.

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

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

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel

Функция СЧЁТЕСЛИ в Excel используется для подсчета количества ячеек в рассматриваемом диапазоне, содержащиеся данные в которых соответствуют критерию, переданному в качестве второго аргумента данной функции, и возвращает соответствующее числовое значение.

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Создание пользовательской функции

Ниже приведены действия по созданию функции UDF для подсчета цвета ячеек.

Откройте Microsoft Excel, а затем нажмите клавиши ALT + F11, чтобы открыть окно редактора Visual Basic (VBE).

В меню Вставка выберите модуль , чтобы создать модуль. Затем введите следующий скрипт:

Закройте окно VBE, чтобы вернуться в Excel.

Чтобы протестировать UDF, создайте примеры данных, которые содержат столбец ячеек, в различных цветах.

В ячейке D3 Запишите функцию:

В аргументе “range_data” выберите ячейку C2 для C51.

В аргументе “критерии” выберите элемент F1.

Нажмите клавишу ВВОД. Результат в ячейке F2 — 6. Это означает, что число ячеек, затененных синим цветом, равно 6.

Для тестирования можно использовать другие цвета. Замените цвет в ячейке F1 на любой цвет из данных, используя домашнюю > заливку.

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

Шаг 1: сохранение книги

а. Выберите файл, а затем Сохранить как. (При необходимости нажмите кнопку Обзор ).

б. Выберите надстройку Excel (. xlam) в виде формата и присвойте файлу имя, например каунткколор.

Вы можете сохранить файл надстройки в любом месте. Но для того чтобы она была указана в качестве надстройки в программе Excel, сохраните ее в расположении по умолчанию. В Windows 7 расположением по умолчанию для любой версии Microsoft Excel является: К:усерсраддиниаппдатароамингмикрософтаддинс

Шаг 2: Установка надстройки

а. Откройте Microsoft Excel на компьютере, на котором необходимо установить надстройку.

б. Откройте диалоговое окно надстройки, выбрав надстройки Excel для Excel 2013 и более поздних версий на вкладке разработчик . (надстройки в Excel 2010.)

в. В диалоговом окне надстройки нажмите кнопку Обзор.

г. Перейдите к расположению файла, в котором сохраняется файл надстройки (например, на USB-диске или в облачной папке). Выберите файл и нажмите кнопку Открыть.

д. В диалоговом окне надстройки убедитесь, что флажок Надстройка снят. После этого нажмите кнопку ОК.

Набор UDF Color Cell Color установлен и готов к использованию. Вы сможете получить доступ к этой функции в любое время, поместив курсор в любую ячейку листа и введя текст:

Заявление об отказе от контента решений сообщества

КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НЕ СОДЕРЖАТ НИКАКИХ ПРЕДСТАВЛЕНИЙ О ПРИГОДНОСТИ, НАДЕЖНОСТИ ИЛИ ТОЧНОСТИ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ. ВСЯ ТАКАЯ ИНФОРМАЦИЯ И СВЯЗАННАЯ С НЕЙ ГРАФИКА ПРЕДОСТАВЛЯЮТСЯ БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ. КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НАСТОЯЩИМ СНИМАТЬИ ВСЕ ГАРАНТИИ И УСЛОВИЯ, ОТНОСЯЩИЕСЯ К ЭТОЙ ИНФОРМАЦИИ И СВЯЗАННЫМ ГРАФИЧЕСКИМ ГРАФИКАМ, ВКЛЮЧАЯ ВСЕ ПОДРАЗУМЕВАЕМЫЕ ГАРАНТИИ И УСЛОВИЯ ДЛЯ ОБЕСПЕЧЕНИЯ СООТВЕТСТВИЯ ТРЕБОВАНИЯМ, ПРИГОДНОСТИ ДЛЯ ОПРЕДЕЛЕННЫХ ЦЕЛЕЙ, ВОРКМАНЛИКЕ УСИЛИЯ, ДОЛЖНОСТИ И НЕНАРУШЕНИЯ ПРАВ. ВЫ ЯВНО СОГЛАСНЫ, ЧТО В СЛУЧАЕ ОТСУТСТВИЯ У КОРПОРАЦИИ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКОВ НЕ НЕСЕТ ОТВЕТСТВЕННОСТИ ЗА ЛЮБЫЕ ПРЯМЫЕ, НЕПРЯМЫЕ, ПУНИТИВЕ, СЛУЧАЙНЫЙ, ОСОБЫЙ, КОСВЕННЫЙ УЩЕРБ ИЛИ ЛЮБОЙ УЩЕРБ, ВКЛЮЧАЯ, БЕЗ ОГРАНИЧЕНИЙ, УЩЕРБ ОТ ПОТЕРИ ИСПОЛЬЗОВАНИЯ, ДАННЫХ ИЛИ ПРИБЫЛИ, КОТОРЫЕ СВЯЗАНЫ С ИСПОЛЬЗОВАНИЕМ ИЛИ НЕВОЗМОЖНОСТЬЮ ИСПОЛЬЗОВАТЬ ИНФОРМАЦИЮ И СВЯЗАННУЮ С НИМИ ГРАФИКУ, В ЗАВИСИМОСТИ ОТ ДОГОВОРА, НАРУШЕНИЯМ, НЕБРЕЖНОСТЬЮ, ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТИ ИЛИ ИНЫМ СПОСОБОМ, ДАЖЕ ЕСЛИ У КОРПОРАЦИИ МАЙКРОСОФТ ИЛИ ЕЕ ПОСТАВЩИКА БЫЛО РЕКОМЕНДОВАНО ВЕРОЯТНОСТЬ УЩЕРБА.

Как посчитать количество заполненных и пустых ячеек в Excel

​ последняя ячейка, в​ пустых ячеек из​ пустой ячейкой. Рассмотрим​ переход в окно​«OK»​ массив ячеек, а​ тоже может быть​ конкретный элемент листа​Всего у оператора может​ и т.д.). Пустые​ числа, смотрите в​функции «Автосумма» в Excel​ строк после фильтра.​После этого в заглавиях​Урок:​ нажать кнопку​

Функция СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ для подсчета ячеек в Excel

​ в расчет. Поэтому​ которой есть 1​ диапазона B2:E2:​ все эти функции​

​ аргументов функции​.​ в нашем случае​ представлено до 255​ с возможным его​ насчитываться до 255​ элементы при подсчете​ статье «Преобразовать текст​. Выделяем ячейку, в​ Смотрите об этом​ столбцов появляется значок​Мастер функций в Экселе​Enter​ сразу выделяем полностью​ или 0.​Как видно из выше​

Принцип счета ячеек функциями СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ

​СЧЁТЕСЛИ​Оператор производит вычисления и​ на колонку.​ полей, но, как​ сохранением там. Но,​ аргументов общей группы​ будут игнорироваться.​ в число Excel».​ которой будет стоять​ способе статью «Порядковый​ фильтра. Кликаем по​Но бывают случаи, когда​.​ конкретный столбец, а​

​Это выведет количество​ приведенных примеров, каждая​ практическом примере.​.​ выдает результат на​Аргумент​ и в прошлый​ к сожалению, функция​«Значение»​В некоторых случаях индикатор​В некоторых случаях перед​ цифра количества строк.​ номер строк по​ нему в том​ нужно подсчитать не​Причем подсчитываться будут даже​ затем, зажав кнопку​

​ удовлетворяющих запросу строк.​ из функций обладает​Ниже на рисунке представлены​В поле​ экран. Как видим,​«Критерий»​ раз, нам понадобится​СЧЁТЗ​. В качестве аргументов​ количества значений может​ пользователем ставится задача​ Нажимаем стрелку у​ порядку после фильтра​ столбце, где было​

​ все строки диапазона,​ полностью пустые строки​Ctrl​Ну, это самый​ своим специальным предназначением​ разные методы подсчета​«Диапазон»​ в выделенной колонке​содержит заданное условие.​ всего одно из​

exceltable.com>

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel

Функция СЧЁТЕСЛИ в Excel используется для подсчета количества ячеек в рассматриваемом диапазоне, содержащиеся данные в которых соответствуют критерию, переданному в качестве второго аргумента данной функции, и возвращает соответствующее числовое значение.

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Отображать только то, что вы хотите увидеть

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

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

Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Office 365.

Ограничить количество строк в Excel с помощью VBA

Вы можете использовать Visual Basic для приложений (VBA), чтобы временно ограничить диапазон используемых строк и столбцов в свойстве Область прокрутки рабочего листа.

Изменение области прокрутки является временной мерой; он сбрасывается при каждом закрытии и повторном открытии книги.

В этом примере вы измените свойства листа, чтобы ограничить количество строк до 30 и количество столбцов до 26 .

1. Открыть пустой файл Excel.

2. Щелкните правой кнопкой мыши вкладку листа в правом нижнем углу экрана для Лист 1 .

3. Нажмите Показать код в меню, чтобы открыть окно редактора Visual Basic для приложений (VBA) .

4. Найдите окно Свойства листа в левом нижнем углу окна редактора VBA.

5. Найдите свойство Область прокрутки в списке свойств листа.

6. Нажмите на пустое поле справа от области прокрутки .

7. Введите диапазон a1: z30 в поле.

8. Сохранить лист.

9. Нажмите «Файл»> «Закрыть» и вернитесь в Microsoft Excel.

Теперь проверьте свою рабочую таблицу, чтобы убедиться, что ваши изменения вступили в силу. Если вы попытаетесь прокрутить строку 30 или столбец Z, электронная таблица должна вернуть вас к выбранному диапазону. Вы не сможете редактировать ячейки за пределами этого диапазона.

Снятие ограничений прокрутки

Самый простой способ снять ограничения прокрутки – сохранить, закрыть и снова открыть книгу. В качестве альтернативы, используйте шаги со 2 по 4 выше, чтобы открыть Свойства листа в окне VBA editor и удалить диапазон, указанный для прокрутки. Область свойство.

Изображение отображает введенный диапазон как $ A $ 1: $ Z $ 30 . При сохранении книги редактор VBA добавляет знаки доллара, чтобы сделать ссылки на ячейки в диапазоне абсолютными.

Скрыть строки и столбцы в Excel

Альтернативный метод ограничения рабочей области рабочего листа – скрыть неиспользуемые строки и столбцы; они останутся скрытыми даже после того, как вы закроете документ.

Вот как скрыть строки и столбцы за пределами диапазона A1: Z30 :

1. Нажмите заголовок строки для строки 31 , чтобы выбрать всю строку.

2. Нажмите и удерживайте клавиши Shift и Ctrl на клавиатуре.

3. Нажмите и отпустите клавишу Стрелка вниз на клавиатуре, чтобы выбрать все строки от 31 строки до нижней листа.

4. Нажмите правой кнопкой мыши в заголовках строк , чтобы открыть контекстное меню .

5. Выберите Скрыть в меню, чтобы скрыть выбранные столбцы.

6. Нажмите на заголовок столбца для столбца AA и повторите шаги со второго по пятый, чтобы скрыть все столбцы после столбца Z .

   Нажмите и отпустите кнопку со стрелкой вправо вместо стрелки вниз, чтобы скрыть столбцы вправо.

7. Сохранить книгу; столбцы и строки вне диапазона от A1 до Z30 будут скрыты, пока вы их не отобразите.

Показать строки и столбцы в Excel

Если вы передумали или допустили ошибку, вы можете показать строки и столбцы и начать все сначала.

Чтобы отобразить строку 31 и выше и столбец Z и выше:

1. Нажмите на заголовок строки для строки 30 – или последней видимой строки на листе – чтобы выбрать всю строку.

2. Затем, щелкнув правой кнопкой мыши, прокрутите вниз до скрытого раздела.

3. Нажмите Главная вкладка на ленте .

4. В разделе Ячейки нажмите Формат > Скрыть и показать > Показать строки , чтобы восстановить скрытые строки.

   Вы также можете щелкнуть правой кнопкой мыши заголовок строки и выбрать «Показать» в раскрывающемся меню.

5. Нажмите на заголовок столбца для столбца AA – или последнего видимого столбца – и повторите шаги два-четыре выше, чтобы отобразить все столбцы.

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel

Функция СЧЁТЕСЛИ в Excel используется для подсчета количества ячеек в рассматриваемом диапазоне, содержащиеся данные в которых соответствуют критерию, переданному в качестве второго аргумента данной функции, и возвращает соответствующее числовое значение.

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Поиск и выделение ячеек, соответствующих определенным условиям

С помощью команды “перейти ” можно быстро найти и выделить все ячейки, содержащие данные определенного типа, например формулы. Кроме того, чтобы найти только те ячейки, которые соответствуют определенным условиям (например, последнюю ячейку в лист, содержащую данные или форматирование, используйте команду Перейти ).

Выполните указанные ниже действия.

Для начала выполните одно из указанных ниже действий.

Чтобы найти определенные ячейки на всем листе, щелкните любую ячейку.

Чтобы найти определенные ячейки в определенной области, выберите нужные диапазон, строки или столбцы. Дополнительные сведения можно найти в разделе выделение ячеек, диапазонов, строк и столбцов на листе.

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

На вкладке Главная нажмите кнопку найти & выберите > Перейти к (в группе Редактирование ).

Сочетание клавиш: Нажмите клавиши CTRL + G.

Нажмите кнопку Дополнительный.

В диалоговом окне выделить группу ячеек выберите один из указанных ниже вариантов.

Ячейки, содержащие примечания.

Ячейки, содержащие константы.

Ячейки, содержащие формулы.

Примечание: С помощью флажков под формулами определяется тип формулы.

текущая область, например весь список.

массив целиком, если активная ячейка содержится в массиве.

Графические объекты, включая диаграммы и кнопки, на листе и в текстовых полях.

Различия между строками

Все ячейки, которые отличаются от активной ячейки в выбранной строке. В выделенной области всегда есть одна активная ячейка — это диапазон, строка или столбец. Нажимая клавишу ВВОД или TAB, вы можете изменить расположение активной ячейки, которое по умолчанию является первой ячейкой в строке.

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

Различия между столбцами

Все ячейки, которые отличаются от активной ячейки в выбранном столбце. В выделенном фрагменте всегда есть активная ячейка, независимо от того, является ли это диапазоном, строкой или столбцом. Нажимая клавишу ВВОД или TAB, вы можете изменить расположение активной ячейки, которая по умолчанию является первой ячейкой в столбце.

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

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

Щелкните только прямые , чтобы найти только ячейки, на которые ссылается формула.

Щелкните все уровни , чтобы найти все ячейки, на которые прямо или косвенно ссылаются ячейки в выделенном фрагменте.

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

Щелкните только прямые , чтобы найти только ячейки с формулами, которые непосредственно ссылаются на активную ячейку.

Щелкните все уровни , чтобы найти все ячейки, которые прямо или косвенно ссылаются на активную ячейку.

Последняя ячейка на листе, содержащая данные или форматирование.

Только видимые ячейки

Только ячейки, видимые в диапазоне, который пересекается со скрытыми строками и столбцами.

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

Нажмите кнопку все , чтобы найти все ячейки, к которым применены условные форматы.

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

Применены только ячейки, которые содержат правила проверки данных. Выполните одно из указанных ниже действий.

Нажмите кнопку все , чтобы найти все ячейки, к которым применена проверка данных.

Щелкните один из них, чтобы найти ячейки с одинаковой проверкой данных в выделенной ячейке.

Метод 3: условное форматирование и использование фильтра

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

1. Выделяем диапазон ячеек, данные которых станут критерием выбора строк.
2. Во вкладке “Главная” находим раздел “Стили”, среди инструментов находим и кликаем по кнопке “Условное форматирование”.
3. В появившемся перечне раскрываем пункт “Правила выделения ячеек”, который содержит список правил, определяющих условия форматирования. В нашем примере выделенный диапазон заполнен текстовыми данными, соответственно – выбираем строку “Текст содержит…”.
4. Далее задаем конкретные условия форматирования – для каких значений ячеек они будут окрашиваться в определенный цвет. В левой части окна указываем букву A, а в правой части оставляем установку по умолчанию (при желании – выбираем другую цветовую схему). Наша цель – чтобы все ячейки с буквой А подкрашивались в красных тонах. Нажимаем ОК.
5. Если ячейки с буквой А выделены цветом, значит настройки условного форматирования сделаны корректно.
6. Чтобы посчитать, сколько строк содержат “подкрашенные” ячейки, используем инструмент “Фильтр”.
7. Если выделение снято, опять выделяем требуемый диапазон ячеек. Во вкладке “Главная”, находим инструмент “Фильтр” (значок в виде воронки, группа инструментов “Редактирование”) и кликаем по нему.
8. На верхней ячейке выделенного столбца появится значок пока что неактивного фильтра в виде кнопки со стрелкой вниз:
   • кликаем на него;
   • ищем строку “Фильтр по цвету”, щелкаем по этому пункту;
   • выбираем цвет, использованный для условного форматирования ранее.
9. В результате работы фильтра в таблице останутся только строки с окрашенными ячейками в выбранном столбце. Теперь можно просто выделить их, чтобы определить итоговое количество в строке состояния. Это и есть количество строк, которое мы хотели выяснить согласно поставленной задаче.

Правила использования функции ЧСТРОК в Excel

Функция ЧСТРОК имеет следующую синтаксическую запись:

=ЧСТРОК(массив)

массив – обязательный для заполнения, принимает константу массива или ссылку на диапазон ячеек, для которого производится подсчет количества строк.

Примечания:

1. Если в качестве аргумента функции передано числовое значение, оно будет интерпретировано как константа массива с одним элементом, поэтому функция ЧСТРОК вернет значение 1. Например, результат выполнения =ЧСТРОК(5) будет 1.
2. Если аргумент функции указан в виде логических или текстовых данных, рассматриваемая функция вернет код ошибки #ЗНАЧ!
3. При использовании констант массивов для разделения строк используют знак «:». Например, константа массива {1;2:4;6:1:7:0} содержит 3 строки.

Указатель в строке состояния

Самый простой способ решить поставленную задачу в выделенном диапазоне – это посмотреть количество в строке состояния. Для этого просто выделяем нужный диапазон

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

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

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

Но бывают и ситуации, когда вы выделяете заполненные ячейки в строках, а отображение количества на панели состояния так и не появляется. Это означает, что данная функция просто отключена. Для её включения кликаем правой кнопкой мыши по панели состояния и в появившемся меню устанавливаем галочку напротив значения «Количество». Теперь численность выделенных строк будет отображаться.

Применение фильтра и условного форматирования

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

1. Выделяем диапазон, по которому будет производиться проверка на выполнение условия.

Переходим во вкладку «Главная». На ленте в блоке инструментов «Стили» жмем на кнопку «Условное форматирование». Выбираем пункт «Правила выделения ячеек». Далее открывается пункт различных правил. Для нашего примера мы выбираем пункт «Больше…», хотя для других случаев выбор может быть остановлен и на иной позиции.

Открывается окно, в котором задается условие. В левом поле укажем число, ячейки, включающие в себя значение больше которого, окрасятся определенным цветом. В правом поле существует возможность этот цвет выбрать, но можно и оставить его по умолчанию. После того, как установка условия завершена, жмем на кнопку «OK».

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

После этого в заглавиях столбцов появляется значок фильтра. Кликаем по нему в том столбце, где было проведено форматирование. В открывшемся меню выбираем пункт «Фильтр по цвету». Далее кликаем по тому цвету, которым залиты отформатированные ячейки, удовлетворяющие условию.

Как видим, не отмеченные цветом ячейки после данных действий были спрятаны. Просто выделяем оставшийся диапазон ячеек и смотрим на показатель «Количество» в строке состояния, как и при решении проблемы первым способом. Именно это число и будет указывать на численность строк, которые удовлетворяют конкретному условию.

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

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Как посчитать промежуточные итоги

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

1. При создании шапки столбца нельзя вписывать в ней несколько строк. Одновременно с этим шапка должна быть расположена на первой строке рабочей таблицы.
2. Невозможно получить промежуточные итоги в тех столбцах, внутри которых находятся пустые ячейки. Даже при наличии одной пустой клетки во всей таблице, расчет произведен не будет.
3. Рабочий документ должен иметь стандартный диапазон без форматирования.

Сам процесс расчета промежуточных итогов состоит из нескольких действий:

1. В первую очередь нужно распределить данные в первом столбце так, чтобы они распределились на группы одинакового типа.
2. Левой кнопкой мыши выбрать любую произвольную ячейку рабочей таблицы.
3. Перейти во вкладку “Данные” на основной странице с инструментами.
4. В разделе “Структура” нажать на функцию “Промежуточные итоги”.
5. После осуществления данных действий на экране появится окно, в котором необходимо прописать параметры для дальнейшего расчета.

В параметре “Операция” необходимо выбрать раздел “Сумма” (есть возможность выбора других математических действий). В следующем параметре указать те столбцы, для которых будут высчитываться промежуточные итоги. Для сохранения указанных параметров необходимо нажать кнопку “ОК”. После выполнения описанных выше действий, между каждой группой ячеек появится одна промежуточная, в которой будет указан результат, полученный после осуществления расчета.

Как удалить промежуточные итоги

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

1. Зайти во вкладку “Данные” на главной странице с инструментами. Нажать на функцию “Промежуточный итог”.
2. В появившемся окне необходимо отметить галочкой пункт “Размер”, нажать на кнопку “Убрать все”.
3. После этого все добавленные данные вместе с дополнительными ячейками будут удалены.