Evolcom.ru

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

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

Автоматическая нумерация Excel

Автоматическая нумерация в Excel подразумевает присвоение порядковых номеров или номеров списку или данным. Некоторые задачи в Excel являются довольно распространенными. Правильное выполнение автоматической нумерации в Excel может сэкономить много времени. Ручной ввод серийных номеров для сотен или тысяч строк является утомительной и трудоемкой задачей. Поэтому автоматическая нумерация в Excel — это простой способ использования.

Методы нумерации строк в Excel

Автоматическая нумерация строк в Excel зависит от типа данных, используемых в Excel. Например.

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

Используются следующие методы

  • Использование ручки заливки
  • Использование серии заливок
  • Использование функции CountA
  • Использование функции ряда
  • Использование промежуточного итога для фильтрованных данных
  • Создание электронной таблицы Excel
  • Добавление единицы к номеру строки выше.
1. Заполнить ручку Метод —

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

Следующие шаги:

  • Введите 1 в ячейку A2 и 2 в ячейку A3, чтобы шаблон был логичным.

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

  • Перетащите символ «+» вручную в последнюю ячейку диапазона или дважды щелкните значок «плюс», т.е. метку заполнения, и число автоматически появится в последовательности.
Заметка:-
  • Номер строки будет обновляться при добавлении/удалении строки (строк)
  • Определяет схему ряда из двух заполненных ячеек и заполняет оставшиеся ячейки.
  • Этот метод не работает с пустым рядом.
  • Номера перечисляются последовательно, но формат римских цифр и список алфавита игнорируются.
  • Вставленный серийный номер является статическим значением.
2. Метод заполнения серии —

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

Шаги, которые необходимо выполнить:.

  • В ячейку A2 введите 1 -> перейдите на вкладку «Главная страница».

  • Выберите раскрывающееся меню «Заполнение» -> Серия

  • Открывается диалог сериала.

  • Нажмите кнопку «Столбцы» в разделе «Серия» и введите число 9 в поле «Значение остановки: введите». Нажмите OK. Отображается серийный номер.

Заполните первую клетку и выполните следующие действия.

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

3. Метод функции CountA —

Функция CountA используется для нумерации только тех строк, которые не являются пустыми в диапазоне.

Действия, которые необходимо предпринять:

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

Читайте так же:
Как повторить строки при прокрутке листа в Excel?

= ЕСЛИ (ISBLANK (B5), ”“, COUNTA ($ B $ 5: B5))

Затем ячейка заполняется числом 1. Затем перетащите маркер заливки (+) на последнюю ячейку в области столбца.

  • Число в ячейке является динамическим. Если строка удаляется, существующие порядковые номера автоматически обновляются по порядку.
  • Пустые строки не учитываются.
4. Метод рядных функций —

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

Следуйте нижеприведенной инструкции.

Вы должны вычесть 1 из функции ROW (), чтобы определить правильный номер ряда. Поскольку row () вычисляет значение 2, но мы хотим, чтобы значение первой строки диапазона было 1, поэтому вычитается 1. Следовательно, формула будет иметь вид,

= СТРОКА () — 1

Если строка начинается с номера 5, формула будет изменена соответствующим образом, т.е. = ROW () — 4

  • Номер строки обновляется при добавлении/удалении строки или строк.
  • Не ссылается ни на одну ячейку в формуле и автоматически корректирует номер строки.
  • Отображает номер строки, даже если строка пуста.
5. СУБТОТАЛЬНО для отфильтрованных данных —

Функция СУММЕСЛИ в Excel позволяет последовательно пронумеровать набор отфильтрованных данных, скопированный в другую таблицу данных.

Шаги, которые необходимо выполнить:.

Это набор данных, который мы будем фильтровать в соответствии с оценками по математике, превышающими или равными 20.

Теперь скопируем эти отфильтрованные данные на другой лист и удалим значения серии «Roll no».

Затем выделите первую ячейку диапазона столбцов и используйте функцию «СУММЕСЛИ», где первый аргумент равен 3, обозначенный «COUNT A».

= СУБТОТАЛЬНО (3, $ B $ 2: B2 )

В результате в ячейке будет стоять значение 1.

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

  • Функция SUB-TOTAL полезна для перестановки количества строк в фильтрованном наборе данных, последовательно скопированном на другой лист.
  • Серийные номера автоматически корректируются при добавлении/удалении рядов.
6) Создав таблицу Excel —

Таблица Excel используется для представления табличных данных.

Шаги, которые необходимо выполнить:.

Сначала выделите весь набор данных -> перейдите на вкладку Вставка -> нажмите на значок таблицы (CTRL + T) в группе таблицы

Появится диалоговое окно «Создать таблицу». Введите диапазон или имя для таблицы

Нажмите на кнопку ОК.

Табличные данные преобразуются в электронную таблицу Excel (таблица 3).

В ячейку А2 введите формулу

Формула выглядит следующим образом

= Строка () — строка (таблица (#header))

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

  • Нумерация строк будет автоматически корректироваться и обновляться при добавлении и удалении строк.
7) Добавив один к предыдущему номеру строки —

Следующие линии увеличиваются на 1.

Читайте так же:
Как предотвратить удаление строк или столбцов в общей незащищенной книге?

Действия, которые необходимо предпринять:

Как показано на прилагаемом снимке экрана, введите 1 в ячейку A2 первого ряда.

  • Введите = A2 + 1 в ячейку A3

  • Выберите A3 и перетащите маркер заливки (+) на последнюю ячейку диапазона. Это автоматически заполнит оставшиеся ячейки.
  • Значение ячейки относительно значения предыдущей ячейки.
  • Не обновляет автоматически серийный номер при добавлении/удалении строк.

Что нужно помнить об автоматической нумерации в Excel

  • Лучшая форма автоматической нумерации в Excel зависит от типа набора данных, который вы хотите включить.
  • Автоматическая нумерация в Excel не является встроенной функцией.
  • Убедитесь, что опция «Заполнить» включена для автоматической нумерации в Excel.

Вывод

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

Шаблон Excel для автоматической нумерации можно скачать здесь — шаблон Excel для автоматической нумерации

Рекомендуемые статьи

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

Запрос на выборку данных (формулы) в EXCEL

Суть запроса SELECT заключается в выборе строк, удовлетворяющих определенным критериям, из исходной таблицы (аналогично использованию стандартного фильтра Filter). Используя формулы массива, выберите значения из исходной таблицы. Вместо использования фильтра (CTRL+SHIFT+L или Данные/Сортировка и фильтр/Фильтр) выбранные строки будут помещены в отдельную таблицу.

В этой статье мы рассмотрим наиболее распространенные запросы, такие как: выборка строк таблицы, значения из числового столбца которых попадают в заданный диапазон (интервал); выборка строк, дата которых равна определенному периоду; задачи с 2 текстовыми критериями и другие. Начнем с простого запроса.

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Предположим, имеется исходная таблица, содержащая список товаров и цен (см. файл примера, лист Первый критерий — Количество ).

В отдельной таблице нужно отобразить только те записи (строки) из таблицы Source с ценой выше 25.

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

Отобразятся записи, соответствующие условиям отбора.

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

Читайте так же:
Как повторить значение ячейки x раз в Excel?

Поместите критерий (минимальная цена) в ячейку E6 таблицы фильтрованных данных в диапазоне D10:E19.

Введите следующую формулу в Строку формул следующим образом: Выберите диапазон D11:D19 (столбец «Товар») и введите формулу массива:

Вместо ENTER нажмите CTRL+SHIFT+ENTER.

Сделайте то же самое для диапазона E11:E19 и введите ту же формулу матрицы:

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

Чтобы показать динамичность результирующего отчета (пример запроса), введите в E6 значение 65. В новую таблицу будет добавлена еще одна запись из исходной таблицы, соответствующая новым критериям.

Если в исходную таблицу добавляется новый товар с ценой от 25 до 65, в новую таблицу будет добавлена новая запись.

Кроме того, файл примера содержит формулы массива с обработкой ошибок, когда столбец Price содержит ошибочное значение, например #DEL/0! Обработку ошибок можно посмотреть на листе Обработка ошибок.

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

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Предположим, что имеется исходная таблица, содержащая список товаров и даты поставки (см. файл примера, лист Один критерий — Дата).

Для выбора строк используйте формулы массива, аналогичные формулам в задании 1 (вместо критерия =$B$12:$B$20)*(STRIKE($B$12:$B$20)-STRIKE($B$11);$J$12-STRIKE(A12)+STRIKE($B$11)+1)))

Примечание: После ввода формулы нажмите CTRL+SHIFT+ENTER вместо ENTER. Эта комбинация клавиш используется для ввода матричной формулы.

Формула массива копируется вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были предоставлены в указанном диапазоне дат. Ячейки 2-4 будут содержать ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4. Диапазон дат) скрыты с помощью Условного форматирования .

Введите ту же формулу для дат в колонке E.

В ячейке J12 подсчитывается количество строк в исходной таблице, удовлетворяющих критериям:

Решение2 : Для выбора строк можно использовать матричные формулы, аналогичные формулам в задаче 2 (т.е. матричные формулы, возвращающие несколько значений):

Чтобы ввести первую формулу, выберите диапазон ячеек G12:G20 . После ввода формулы нажмите CTRL+SHIFT+ENTER вместо ENTER.

Когда столбец Dat сортируется, формулы массива не нужны.

Сначала вычислите первую и последнюю позиции строк, которые соответствуют критериям. Затем выведите строки с помощью функции HUMMESH().

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

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

Предположим, имеется исходная таблица со списком товаров и датами поставок (см. файл примера, лист Первый критерий — Дата (не позже) ).

Читайте так же:
Как показать стрелки для отображения взаимосвязи между формулами и ячейками в Excel?

Формула таблицы используется для выбора строк с датой, которая не является более ранней (включая саму дату):

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7 C15;И($B$7>=B15;$B$7 =$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12)) -СТРОКА($B$12))

Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

Примечание: Случай, когда список является несортированным, описан в статье Найти дату (ЧИСЛО), наиболее близкую к заданной с условием в MS EXCEL. Несортированный список .

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Таблица со списком товаров и цен показана в файле примера (лист Один критерий — Текст).

Задача 3 решается аналогично задачам 1 и 2. Более подробную информацию можно найти в статье Поиск значений TEXT в Microsoft Excel с выводом их в отдельный список. Часть 1. Типичный поиск .

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Предположим, что имеется исходная таблица со списком товаров и цен (см. файл примера, страница 2 критерии — текст (AND) ).

Для выбора строк используется формула массива:

Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) устанавливает два условия (товар и месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел <1:2:3:4:5:6:7:8:9>, т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Предположим, что имеется исходная таблица, содержащая список товаров и цен (см. файл примера, страница 2 критерия — текст (O)).

В отличие от задания 7, вы должны выбрать строки с двумя типами товаров (условие OR).

Для выбора строк используется формула массива:

= ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Если установить $E$6 в ($A$11:$A$19)+($E$7 в ($A$11:$A$19), будут выбраны только товары в желтых ячейках (Товары2 и Товары3). Знак + (дополнение) используется для задания условия ИЛИ (должен выполняться хотя бы 1 критерий).

Вышеуказанное выражение вернет массив <0:0:0:0:1:1:1:0:0>. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел <1:2:3:4:5:6:7:8:9>, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив <0:0:0:0:5:6:7:0:0>.

Давайте воспользуемся функцией СЧЕТ(), чтобы вывести 3 значения из записей 5 (строка 15 электронной таблицы), 6 (16) и 7 (17), то есть значения для Товара2, Товара2 и Товара3. Для этого используется выражение ACCOUNT($A$11:$A$19;$E$6)+ACCOUNT($A$11:$A$19;$E$7)-CROW($A$11:A11)+1, которое последовательно (начиная с 11 строки) даст числа 3; 2; 1; 0; -1; -2; . Формула OVERLOAD(. ;3) возвращает число 5, OVERLOAD(. ;2) возвращает число 6, OVERLOAD(. ;1) возвращает число 7, а OVERLOAD(. ;0) и последующие возвращают ошибку, которую мы скрываем с помощью условного форматирования.

Читайте так же:
Как переместить активный лист в конец или в начало текущей книги в Excel?

Наконец, используя функцию INDEX(), мы последовательно выведем значения из каждого элемента: = INDEX(A$11:A$19;5) возвращает элемент2, = INDEX(A$11:A$19;6) возвращает элемент2, = INDEX(A$11:A$19;7) возвращает элемент3.

10. Отбор значений с учетом повторов

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

Это лучшие статьи этого раздела:

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

Давайте начнем с таблицы сторон.

Предположим, что нас интересует, сколько и каких партий товара поставляется по цене от 1000р. до 2000р. (критерий 1). Причем, должно быть не менее 3 партий с одинаковой ценой (критерий 2).

Решение — формула массива:

Данная формула возвращает номера строк, удовлетворяющие обоим критериям.

Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на рабочем листе «10.Критерии — количество повторений» определено условное форматирование для визуального определения строк, удовлетворяющих критериям, и для скрытия ячеек, в которых матричная формула возвращает ошибку #NUMBER!

11. Используем значение критерия (Любой) или (Все)

Этот фильтр выводит все значения для всех столбцов в MS Excel. Другими словами, выпадающий список значений критерия содержит специальное значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL , Отчет №3).

В файле-образце на листе «11. Критерий Любой или (Все)» Этот вариант критерия реализован.

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

Остальные формулы аналогичны описанным выше.

12. Актуальная цена

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

Следующие формулы необходимы для создания списка продуктов с текущими ценами:

2) определить последнюю (максимальную) дату каждого элемента, используя матричную формулу =MAX((Table1[item]=E8)*Table1[date])

Чтобы отобразить текущую цену, введите =SMEMSLIMN(Table1[price];Table1[item];E8;Table1[date];F8).

Для элементов не должно быть повторяющихся дат, иначе цены будут суммированы (если последняя дата повторяется).

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