Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать количество ячеек больше или меньше 0 (нуля) в Excel?

Подсчет количества значений в столбце в Excel

Любой пользователь, работающий с Excel, наверняка знает, как вычислить сумму значений в ячейках электронной таблицы Excel. Функция Sum, которая очень популярна в последних версиях Excel, вероятно, используется гораздо чаще других. Однако иногда перед пользователем может стоять несколько иная задача — проверка количества значений с определенными параметрами в заданном столбце. Не их сумма, а простой ответ на вопрос — сколько раз N-ое значение встречается в выбранном диапазоне? Существует несколько методов решения этой проблемы в Excel.

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

Метод 1: отображение количества значений в строке состояния

Пожалуй, нет более простого способа работы с текстовыми и числовыми данными. Условия, однако, не совместимы с ним.

Воспользоваться этим методом крайне просто: выделяем интересующий массив данных (любым удобным способом). Результат сразу появится в строке состояния (Количество). В расчете участвуют все ячейки, за исключением пустых.

Отображение количества значений в строке состояния

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

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

В строке состояния не всегда присутствует индикатор «Количество» по умолчанию, но это можно легко исправить:

Отображение количества значений в строке состояния

  1. Щелкните правой кнопкой мыши в строке состояния.
  2. В открывшемся списке обратите внимание на строку «Количество». Если рядом с ним нет флажка, он не включен в строку состояния. Щелкните строку, чтобы добавить ее.
  3. Все готово, отныне этот индикатор будет добавлен в строку состояния программы.

Метод 2: применение функции СЧЕТЗ

Данная функция позволяет сделать то же самое, но с одним существенным отличием — полученный результат будет фиксированным. В результате остаются и другие недостатки, в том числе: С условиями Count3 не работает.

В функции Count3 подсчитываются все заполненные ячейки в диапазоне (пустые ячейки не подсчитываются). Формула функции может выглядеть по-разному:

  • =СЧЕТЗ(ячейка1;ячейка2;…ячейкаN)
  • =СЧЕТЗ(ячейка1:ячейкаN)

В первом случае функция подсчитает все ячейки в списке. Во втором случае она определит количество непустых ячеек в диапазоне от ячейки 1 до ячейки N. Обратите внимание, что количество аргументов функции ограничено 255.

Попробуйте этот пример с использованием функции Count3:

  1. Выбираем ячейку, где по итогу будет выведен результат подсчета.
  2. Переходим во вкладку “Формулы” и нажимаем кнопку “Вставить функцию”.Применение функции СЧЕТЗТакже можно кликнуть по значку «Вставить функцию» рядом со строкой формул.Применение функции СЧЕТЗ
  3. В открывшемся меню (Мастер функций) выбираем категорию «Статистические», далее ищем в перечне нужную функцию СЧЕТ3, выбираем ее и нажимаем OK, чтобы приступить к ее настройке.Применение функции СЧЕТЗ

Метод 3: использование функции СЧЕТ

Count подходит, если вам приходится работать только с числами. Эта функция игнорирует текст в ячейках. Count, однако, практически идентична CountZ в ранее рассмотренном методе.

Вот как выражается функция подсчета:

  • =СЧЕТ(ячейка1;ячейка2;…ячейкаN)
  • =СЧЕТ(ячейка1:ячейкаN)

Как и в случае с рассмотренным выше алгоритмом:

  1. Выбираем ячейку, где будет сохранен и отображен результат подсчета значений.
  2. Заходим в Мастер функций любым удобным способом, выбираем в категории “Статистические” необходимую строку СЧЕТ и щелкаем OK.Использование функции СЧЕТ
  3. В «Аргументах функции» задаем диапазон ячеек или перечисляем их. Далее жмем OK.Использование функции СЧЕТ
  4. В выбранной ячейке будет выведен результат. Функция СЧЕТ проигнорирует все ячейки с пустым содержанием или с текстовыми значениями. Таким образом, будет произведен подсчет исключительно тех ячеек, которые содержат числовые данные.Использование функции СЧЕТ

Метод 4: оператор СЧЕТЕСЛИ

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

Синтаксис COUNTERSLY типичен для всех условных выражений:

=СЧЕТЕСЛИ(диапазон;критерий)

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

Критерий — это конкретное условие, соответствие, которое ищет функция. Используя кавычки, условие можно задать как точное соответствие введенному числу или тексту, или как математическое сравнение, используя знаки «не равно» (*>), «больше чем» («>») и «меньше чем» («<"). Кроме того, можно добавить условия "больше или равно" или "меньше или равно" ("=>/=<").

Рассмотрим применение функции СЧЕТ:

  1. Давайте, к примеру, определим, сколько раз в столбце с видами спорта встречается слово «бег». Переходим в ячейку, куда нужно вывести итоговый результат.
  2. Одним из двух описанных выше способов входим в Мастер функций. В списке статистических функций выбираем СЧЕТЕСЛИ и кликаем ОК.Оператор СЧЕТЕСЛИ
  3. Окно аргументов несколько отличается от тех, что мы видели при работе с СЧЕТЗ и СЧЕТ. Заполняем аргументы и кликаем OK.
    • В поле «Диапазон» указываем область таблицы, которая будет участвовать в подсчете.
    • В поле «Критерий» указываем условие. Нам нужно определить частоту встречаемости ячеек, содержащих значение “бег”, следовательно пишем это слово в кавычках. Кликаем ОК.
    • Оператор СЧЕТЕСЛИ
  4. Функция СЧЕТЕСЛИ посчитает и отобразит в выбранной ячейке количество совпадений с заданным словом. В нашем случае их 16.Оператор СЧЕТЕСЛИ

Для лучшего понимания функции СЧЁТ попробуем изменить условие:

  1. Давайте теперь определим сколько раз в этом же столбце встречаются любые другие значения, кроме слова «бег».
  2. Выбираем ячейку, заходим в Мастер функций, находим оператор СЧЕТЕСЛИ, жмем ОК.
  3. В поле «Диапазон» вводим координаты того же столбца, что и в примере выше. В поле «Критерий» добавляем знак не равно («<>») перед словом «бег».Оператор СЧЕТЕСЛИ
  4. После нажатия кнопки OK мы получаем число, которое сообщает нам, сколько в выбранном диапазоне (столбце) ячеек, не содержащих слово «бег». На этот раз количество равно 17.Оператор СЧЕТЕСЛИ

Наконец, мы можем проанализировать работу на основе числовых условий, содержащих знаки «больше» («>») или «меньше» («<"). Проверьте, как часто в колонке "Продано" встречается значение больше 350.

  1. Выполняем уже привычные шаги по вставке функции СЧЕТЕСЛИ в нужную результирующую ячейку.
  2. В поле диапазон указываем нужный интервал ячеек столбца. Задаем условие “>350” в поле “Критерий” и жмем OK.Оператор СЧЕТЕСЛИ
  3. В заранее выбранной ячейке получим итог – 10 ячеек содержат значения больше числа 350.Оператор СЧЕТЕСЛИ

Метод 5: использование оператора СЧЕТЕСЛИМН

S CETESLIMN аналогичен SCETESLIMN, за исключением того, что позволяет ввести несколько диапазонов с одинаковыми или разными условиями.

Например, следует подсчитать количество предметов, которые были проданы более чем за 300 штук, и предметов стоимостью более 6000 рублей.

Давайте посмотрим, как это сделать с помощью функции WHATSLYMN:

  1. В Мастере функций уже хорошо знакомым способом находим оператор СЧЕТЕСЛИМН, который находится все в той же категории “Статические” и вставляем в ячейку для вывода результата, нажав кнопку OK.Использование оператора СЧЕТЕСЛИМН
  2. Кажется, что окно настроек функции не отличается от СЧЕТЕСЛИ, но как только мы введем данные первого условия, появятся поля для ввода второго.
    • В поле «Диапазон 1» вводим координаты столбца, содержащего данные по продажам в шт. В поле «Условие 1» согласно нашей задаче пишем “>300”.
    • В «Диапазоне 2» указываем координатами столбца, который содержит данные по ценам. В качестве «Условия 2», соответственно, указываем “>6000”.Использование оператора СЧЕТЕСЛИМН
  3. Нажимаем OK и получаем в итоговой ячейке число, сообщающее нам, сколько раз в выбранных диапазонах встретились ячейки с заданными нами параметрами. В нашем примере число равно 14.Использование оператора СЧЕТЕСЛИМН

Метод 6: функция СЧИТАТЬПУСТОТЫ

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

Эта функция имеет очень простой синтаксис:

=СЧИТАТЬПУСТОТЫ(диапазон)

Процедура практически не отличается от описанной выше:

  1. Выбираем ячейку, куда хотим вывести итоговый результат по подсчету количества пустых ячеек.
  2. Заходим в Мастер функций, среди статистических операторов выбираем “СЧИТАТЬПУСТОТЫ” и нажимаем ОК.Функция СЧИТАТЬПУСТОТЫ
  3. В окне «Аргументы функции» указываем нужный диапазон ячеек и кликаем по кнопку OK.Функция СЧИТАТЬПУСТОТЫ
  4. В заранее выбранной нами ячейке отобразится результат. Будут учтены исключительно пустые ячейки и проигнорированы все остальные.Функция СЧИТАТЬПУСТОТЫ

Заключение

Microsoft Excel — это чрезвычайно мощный инструмент, который может решать множество задач различной сложности. Даже в самой простой задаче, такой как подсчет количества значений, он может значительно облегчить жизнь пользователя, причем несколькими различными способами. Метод подсчета количества значений, упомянутый в этой статье, зависит от целей и задач пользователя.

ЕГО ВЕЛИЧЕСТВО, ОБОЖАЕМЫЙ EXCEL

Не у всех групп пользователей есть возможность комментировать блоги.

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

ЕГО ВЕЛИЧЕСТВО, ОБОЖАЕМЫЙ EXCEL

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

Если просмотреть учебники, методические материалы, статьи, электронные ресурсы, посвященные этому приложению, то в подавляющем большинстве случаев народу бывает достаточно умения создать нечто, напоминающее накладную (цена-количество-стоимость-итоговая_сумма), построить диаграмму. На втором этапе интереса выясняется, что кроме суммы есть еще целый ряд интересных функций, в том числе и логические, которые позволяют вести автоматический анализ данных. Потом оказывается, что понятие "абсолютная ссылка" не имеет никакого отношения к Колыме, зато позволяет автоматизировать изменение исходных данных, не боясь пропустить ошибку в расчетах. И дальше лавинообразно: сортировка, автофильтр, промежуточные итоги, обработка массивов данных, формы, макросы….

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

СЧЕТЕСЛИ и Ко
Очень удобная функция, когда требуется не просто подсчитать, сколько штук, а – сколько особых штук. Ну например, в списке всех товаров определить, сколько раз встречается товар "холодильник", или в таблице со списком работников – сколько человек с должностью "мастер", или сколько человек отработали за месяц менее 10 дней (если есть, разумеется, столбец с такими данными). Можно даже сделать ссылку на ячейку, в которой находится некоторое число, допустим, пороговое значение, и подсчитать, сколько значений из документа превышают этот порог. Или не превышают. Или равны ему.

  • СЧЁТЕСЛИ(А2:А100;"А*") — подсчитает количество сделок с товарами, название которых начинается с буквы "А" (апельсин, абрикос, ананас, авокадо)
  • СЧЁТЕСЛИ(А2:А100;". ") — подсчитает количество сделок с товарами, наименование или шифр которых состоит из 3-х символов
  • СЧЁТЕСЛИ(А2:А100;"??-?? ") — подсчитает количество сделок с товарами, шифр которых состоит из 4-х символов через тире, допустим, ГЗ-23 или ОП-44
  • СЧЁТЕСЛИ(B2:B100;">10.10.2011") — подсчитает количество сделок, совершенных позже 10-го октября 2011 года

ДВА В ОДНОМ
Есть число и есть единица его измерения, например, 2 кг, или 7 км, или 12 л, или 15 шт. Если это число нужно на что-нибудь умножать или с чем-нибудь складывать, то есть выполнять с ним действия, то приходится число заносить в один столбец, а единицу измерения – в другой. Потому что 2 кг, занесенное в одну ячейку, считается текстом и ни на что не умножается и ни с чем не складывается. И не очень это удобно.
НО! Если выделить нужный диапазон, открыть Формат-Ячейки, выбрать "Все форматы", в них – "Основной" и после слова Основной через пробел вписать ваши "кг", "км" или "л" в кавычках , то можно вносить в ячейку число, получать в ячейке, например, 2 кг и спокойненько использовать эту ячейку для вычислений.

БЫСТРО, БЫСТРЕЕ, ЕЩЕ БЫСТРЕЕ
Сейчас преподавателям вуза редко преподносят цветы. То ли традиция забылась, то ли нас учителями не считают, то ли мы не достойны – Бог весть, да и я смущаюсь получать букеты, автоматически прикидывая их стоимость и понимая с сожалением, что через пару дней эту красоту придется выбросить. Хотя на моем подоконнике до сих пор растет в горшке и цветет прекрасное нечто, подаренное однажды на экзамене и до сих пор напоминающее мне о моих студентах, спасибо им. Но однажды я получила огромный букет от заочников со словами: "Спасибо Вам за волшебную клавишу CTRL!". Увидев, что я не понимаю, о чем речь, мне пояснили, что в группе много практиков-бухгалтеров, а я показала несколько задач, которые пришлись ну просто "в тему" и несколько приемов быстрой работы, от которых народ пришел в восторг.

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

* Чтобы просуммировать данные строки или столбца, мы используем функцию АВТОСУММА. Вместо поиска кнопки в меню нажмите Alt и знак "равно" (=) одновременно. Если автоматически определенный диапазон суммирования вас устраивает – жмем Enter (две секунды на проблему).

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

* Список работников в документе расположен по вертикали. В новом документе требуется расположить его по горизонтали. Вместо того чтобы копировать каждую ячейку, закажите так называемое транспонирование. Для этого выделите список, скопируйте в буфер (в Excel-2003 Правка-Копировать), установите маркер на первую ячейку будущего диапазона, закажите специальную вставку (в Excel-2003 Правка-Специальная_Вставка) и поставьте "птичку" – транспонировать. Жмите ОК. В Excel‑2007 и старше все можно найти в контекстном меню (правая кнопка мыши).

* Заголовок колонки длинный. Ширина колонки мала. Введите заголовок, нажимая Alt+Enter через равные промежутки времени (т.е. Alt и Enter одновременно).

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

* После заполнения ячеек вдоль линии оказывается, что некоторые ячейки наполовину пусты (слишком широкий столбец), а в некоторых ячейках текст выходит за границы (слишком узкий). И снова нет необходимости тратить время и настраивать каждую колонку отдельно. Выделите столбцы (на краю!) и дважды щелкните на границе любых двух столбцов (на краю!). Excel сам отрегулирует ширину колонок так, чтобы текст помещался по ширине колонки и не более.

* Быстрое копирование и перемещение связано с буксировкой. Тянуть следует за край выделенного диапазона ячеек. Ну и есть варианты: при простой буксировке получается перемещение; при нажатой Ctrl – копирование; при нажатой Shift вы сможете не просто переместить диапазон, а вставить его в другое место документа, раздвинув имеющиеся там ячейки (место вставки указывает пунктирная линия с засечками); при нажатых Ctrl+Shift происходит вставка копии. Кстати, буксировать можно не только на текущем листе, но и на любой другой, если тянуть выделенный диапазон на ярлычок этого самого листа (правда при этом придется держать нажатой клавишу Alt).

* Очень удобно использовать ту же волшебную клавишу Ctrl при работе с листами. Ярлык листа перемещается путем перетаскивания его влево и вправо. Удерживая Ctrl, вы создаете копию листа. Также при нажатии Ctrl можно последовательно щелкнуть несколько ярлыков. Это позволит сгруппировать листы, или, другими словами, выделить их одновременно. После этого вы можете работать на одном листе, поскольку все, что вы делаете, будет автоматически записываться на всех листах, входящих в группу. Не забудьте щелкнуть правой кнопкой мыши, прежде чем разгруппировать все ярлыки в конце.

* Таблица готова, и вы вдруг обнаруживаете, что забыли ввести некоторые данные, например, забыли их. Вам нужны пустые ячейки в середине документа. Поместите маркер в ячейку, после которой вам нужны пустые ячейки. Наведите курсор мыши на маркер автозаполнения (как будто вы собираетесь его скопировать) и перетащите его вниз, удерживая клавишу Shift.

* Многие люди знакомы с техникой автозаполнения. Это происходит, когда две ячейки заполнены, например, числами 1 и 2, выделены и скопированы путем перетаскивания в соседние ячейки. Получается последовательность 1,2,3,4,5… А если вы хотите 1,2,1,2,2,2,1,2? Заполните ячейки числами 1 и 2, выделите две ячейки и перетащите их, как в копии, но с помощью правой кнопки мыши. Отпустите мышь, и появится меню, выберите в нем COPY. Или еще быстрее — копируйте, перетаскивая левой кнопкой мыши, удерживая нажатой клавишу Ctrl. Кстати, количество первоначально заполненных ячеек может быть любым. Предположим, что в документе повторяются коды товаров 105,211,135,419. Заполните ячейки, выделите, перетащите с нажатой клавишей Ctrl, получите повторяющийся список.

* O, TIN, KPP, BIC, OKPO! Как часто они начинаются с нуля! А Excel, который работает в основном с числами, естественно, отбрасывает первый ноль, поскольку он считается несущественным. Преобразуйте свой BIC в текст. Для этого нажмите апостроф перед цифрой. Это верхняя запятая. На клавиатуре она находится на той же клавише, что и русская буква Е, но в английской раскладке. Число «013», записанное в ячейке, не теряет свой ноль и апостроф не виден.

* Теперь о быстром форматировании. Клавиши быстрого доступа, которые облегчают нашу жизнь и ускоряют работу, называются клавишами быстрого доступа и требуют от нас запоминания. Это умственное усилие, это работа. Многие предпочитают делать это долго и через меню — так проще. Но, возможно, вам стоит повесить перед глазами листок с двумя-тремя фокусами. Посмотрите первый раз, пока не запомните его хорошо, а затем можете заменить лист другим полезным.

Итак, если нужно быстренько сделать ячейки жирными, наклонными, с двумя знаками после запятой и прочее – выделите эти ячейки, а потом нажимайте:
o Ctrl+B(англ) – жирный
o Ctrl+I – курсив

При этом нет необходимости переключаться на английский шрифт. Ctrl+I и Ctrl+Sh также могут работать. Его легче запомнить тем, кто немного знает английский, так как B означает Bold, а I — Italic.

o Сtrl+Shift+! – два знака после запятой
o Сtrl+Shift+% – увеличит числа в 100 раз и припишет знак %
o Сtrl+Shift+*– мгновенно выделит диапазон всех ячеек таблицы
o Ctr+Shift+стрелка(влево-вправо-вверх или вниз) выделит все ячейки соответственно влево, вправо, вверх или вниз от текущей, то есть от той, на которой расположен маркер.

А нажав F4, вы можете повторить последнее действие в Excel. А нажав Ctrl+Shift+$, можно вставить в ячейку текущую дату (если система, т.е. часы компьютера, отображает ее правильно, иначе подставьте ее!) И вызвать сохранение — F12, и Ctrl+C — копировать, и Ctrl+V — вставить.

голоса
Рейтинг статьи
Читайте так же:
Как показать стрелку выпадающего списка в Excel?
Ссылка на основную публикацию
Adblock
detector