Mini-ats102.ru

ООО “Мультилайн”
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Второй подход – использовать функцию СЧЁТЕСЛИ, которая более широко применяется и может легче запомниться:

функция СЧЁТЕСЛИ в Excel

Первый и второй способы дали одинаковые результаты – 21 значение в массиве. Однако, если пересчитать вручную – их окажется только 20. Это и есть та особенность, о которой я говорил выше. В ячейке E5 содержится формула, которая результатом возвращает пустую строку. Запомните, функции СЧЁТЗ и СЧЁТЕСЛИ считают подобные ячейки непустыми.

формула в ячейке Excel

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

Как правильно должна быть заполнена таблица в Excel? Мы знаем, что форма отчетов, так называемой иерархией (особенно в 1С). Отчет по форме иерархией — это форма, когда определенный признак не заполнен в каждой строке таблицы. Такая форма мешает правильной обработке данных в Excel, т.к. программа не видит данные для каждой строки. Посмотреть пример можно на картинке в столбце С, Город. Если делать сводную таблицу с отбором по столбцу С, данные по городу МСК в строках 3 и 4 не попадут в отчет. Необходимо преобразовать данные столбца C в вид столбца Е или D. Так как заполнить пустые ячейки в таблице Excel, чтобы программа воспринимала их правильно?

заполнить пустые ячейки в таблице

Возьмите за правило:

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

Есть несколько несложных способов, как заполнить ячейки сплошными данными

Как заполнить пустые ячейки в таблице? Простая формула

заполнить пустые ячейки в таблице 1

Простой функцией ЕСЛИ, можно творить страшные вещи. В данном случае формула достаточно простая, но очень эффективная.

Суть в следующем, в соседнем столбце (D), мы проверяем пустая ли ячейка в соседнем столбце (C2);

  • если ячейка пустая, то подставляем данные из предыдущей ячейки этого столбца D1;
  • если ячейка заполнена, то вставляем С2 в нашем случае МСК.
Читайте так же:
Где хранится автосохранение word

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

2. Как заполнить пустые ячейки без сложных формул

Есть один хитрый способ обойтись без формул. Может быть понятно не сразу, поэтому напишу по пунктам.

1.Выделяем весь столбец, где не заполнены все ячейки. Я скопировал столбец С в столбец Е для примера.

заполнить пустые ячейки в таблице 2

2. Заходим в меню Главная — Найти и Выделить — Выделение группы ячеек.

заполнить пустые ячейки в таблице 3

3. В открывшемся окне выбираем «пустые ячейки». Таким образом, мы выберем все пустые ячейки в столбце Е, но только в нужном диапазоне.

заполнить пустые ячейки в таблице 4

4. Теперь не торопитесь! Вы выделили все пустые ячейки и теперь для них нужно задать формулу заполненной ячейки (E2 в нашем случае) — это можно сделать записав ее в строку формул

заполнить пустые ячейки в таблице 5

5. Чтобы задать формулу для всех выделенных пустых ячеек жмем обязательно Ctrl + Enter, всё, данные заполнились.

Для обоих столбцов D и E, остались формулы в ячейках. Скопируйте этот столбец и вставьте данные как значения .

Описание функции

Функция =ПОСЛЕДНЕЕВСТРОКЕ(ЯЧЕЙКА) имеет один аргумент.

  • ЯЧЕЙКА — ссылка на любую ячейку из строки, в которой необходимо найти последнее непустое значение.

Пример

Определение значения последней непустой ячейки в строке.

Найдем номер строки последней заполненной ячейки в столбце и списке. По номеру строки найдем и само значение.

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

Выделение диапазонов несколькими цветами по условию

Другой вариант: нам необходимо в одном столбце выделить разные диапазоны разным цветом.

Например, меньше 30 один диапазон и более 40 – другой диапазон. Выделяем столбец и идем через пункт «условное форматирование» в «правила выделения ячеек» — «меньше». В строке «форматировать ячейки, которые меньше…» ставим значение 30 и выбираем цвет красный текста и выделения ячеек.

Читайте так же:
Как быстро посчитать сумму в excel

Нажимаем ОК. Теперь повторяем все тоже самое, но выбираем уже пункт не «меньше», а «больше».

В строке «форматировать ячейки, которые больше…» ставим значение 40. Цвет выбираем любой другой, например желтый. В результате применения параметров получим следующее.

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

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

Примеры использования функции СМЕЩ в Excel

Пример 1. Ищем последнюю заполненную ячейку в колонке

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

Функция OFFSET (СМЕЩ) в Excel

=OFFSET(A1,COUNT(A:A)-1,0) – английская версия

=СМЕЩ(A1;СЧЁТ(A:A)-1;0) – русская версия

Эта формула предполагает, что кроме указанных значений нет никаких других, и в этой колонке нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и соответствующим образом смещает ячейку “A1”.

Например, в указанном примере есть 8 значений, поэтому функция COUNT(A:A) или СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку “A1” на 7, чтобы получить последнее значение.

Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных

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

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

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

Читайте так же:
Можно ли выпивать после лечения зуба

Как сделать такой список:

  • Выберите ячейку, в которой вы хотите создать выпадающий список;
  • Нажмите на вкладку Data => Data Tools => Data Validation;
  • В диалоговом окне Data Validation, в разделе Настройки выберите List из выпадающего списка;
  • В параметрах Source укажите формулу =OFFSET(A1,0,0,COUNT(A:A),1) или =СМЕЩ(A1;0;0;СЧЁТ(A:A);1)
  • Нажмите ОК

Как эта формула работает:

Первые три аргумента функции OFFSET (СМЕЩ) A1, 0, 0. Это означает что начальное значение в ячейке “A1”, которое не смещается ни по строкам и по колонкам (0, 0);
Четвертый аргумент функции указывает на высоту, и здесь функция COUNT (СЧЁТ) возвращает суммарное количество ячеек в диапазоне данных для выпадающего списка. Главное условие – отсутствие пустых ячеек в диапазоне.
Пятый аргумент функции “1”, обозначает ширину диапазона данных, которая в нашем случае равна одной колонке.

Подсчет ячеек

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

Способ 1: счетчик на строке состояния

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

Подсчет ячеек в строке состояния в Microsoft Excel

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

Читайте так же:
Можно ли бытовой пылесос подключать к штроборезу

Включение счетчика в Microsoft Excel

Способ 2: функция СЧЁТЗ

Подсчитать количество заполненных ячеек можно при помощи функции СЧЁТЗ. Она отличается от предыдущего способа тем, что позволяет зафиксировать подсчет определенного диапазона в отдельной ячейке. То есть, для просмотра информации по нему область не нужно будет постоянно выделять.

    Выделяем область, в которую будет выводиться результат подсчета. Кликаем по значку «Вставить функцию».

Переход к вставке функции в Microsoft Excel

Переход к функции СЧЁТЗ в Microsoft Excel

Функция СЧЁТЗ в Microsoft Excel

Введение вручную функции СЧЁТЗ в Microsoft Excel

Резултьтат подсчета функции СЧЁТЗ в Microsoft Excel

Способ 3: функция СЧЁТ

Кроме того, для подсчета заполненных ячеек в Экселе существует ещё функция счет. В отличие от предыдущей формулы, она считает только ячейки заполненные числовыми данными.

    Как и в предыдущем случае, выделяем ячейку, куда будут выводиться данные и таким же способом запускаем Мастер функций. В нём выбираем оператора с наименованием «СЧЁТ». Жмем на кнопку «OK».

Переход к функции СЧЁТ в Microsoft Excel

Функция СЧЁТ в Microsoft Excel

Для ручного введения формулы придерживаемся следующего синтаксиса:

Введение вручную функции СЧЁТ в Microsoft Excel

Резултьтат подсчета функции СЧЁТ в Microsoft Excel

Способ 4: функция СЧЁТЕСЛИ

Данная функция позволяет подсчитать не просто количество ячеек, заполненных числовыми выражениями, но только такие из них, которые соответствуют определенному условию. Например, если задать условие «>50», то будут учитываться только такие ячейки, в которых содержится значение больше числа 50. Также можно задавать значения «<» (меньше), «< >» (не равно) и т.д.

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

Переход к функции СЧЁТЕСЛИ в Microsoft Excel

Функция СЧЁТЕСЛИ в Microsoft Excel

Для ручного ввода шаблон выглядит следующим образом:

Введение вручную функции СЧЁТЕСЛИ в Microsoft Excel

Резултьтат подсчета функции СЧЁТЕСЛИ в Microsoft Excel

Способ 5: функция СЧЁТЕСЛИМН

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

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

Переход к функции СЧЁТЕСЛИМН в Microsoft Excel

Функция СЧЁТЕСЛИМН в Microsoft Excel

Синтаксис у этой функции следующий:

Читайте так же:
Как в таблице excel убрать пустые строки

Введение вручную функции СЧЁТЕСЛИМН в Microsoft Excel

Резултьтат подсчета функции СЧЁТЕСЛИМН в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12447 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

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

  1. Для начала нужно задать имя диапазону ячеек, с которым планируем работать. Для этого выделяем его, затем кликаем по нему правой кнопкой мыши и в контекстном меню выбираем команду “Присвоить имя”.Присвоение имени диапазону ячеек в Эксель
  2. В окне создания имени в поле “Имя” указываем любое название (без пробелов). Остальные поля оставляем без изменений и нажимаем OK.Создание имени для диапазона ячеек в Эксель
  3. В любом свободном месте производим выделение соразмерного диапазона ячеек (для удобства, если возможно, лучше это сделать в тех же строках другого столбца). Затем также в контекстном меню, вызываемом щелчком правой кнопки мыши по нему, выбираем пункт “Присвоить имя”.Присвоение имени диапазону ячеек в Excel
  4. Как и для первого диапазона, задаем имя (в нашем случае – “Без_пустых_ячеек”).Создание имени для диапазона ячеек в Excel
  5. Встаем в самую верхнюю ячейку диапазона “Без_пустых_ячеек” (в нашем случае – это D2) и пишем в нее следующую формулу:

Формула для удаления пустых ячеек в Эксель

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector