Как в таблице excel пронумеровать строки

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

Как в таблице excel пронумеровать строки

Как в таблице excel пронумеровать строки

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

Если вы думаете, что один из способов это простой ввод серийного номера вручную, то вы правы!

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

Представьте, что у вас есть сотни или тысячи строк, для которых нужно ввести серийный номер. Ручной ввод в этом случаи будет утомительным — и совершенно ненужным.

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

Удалить

Для того чтобы удалить, например, пустую строку с листа Эксель, кликаем по ней правой кнопкой мыши и выбираем «Удалить» .

После этого откроется уже знакомое диалоговое окно, в котором ставим маркер в поле «строку» и нажимаем «ОК» .

Еще один способ: выделяем любую ячейку из рядка, который будем удалять. Потом на вкладке «Главная» кликаем по кнопочке «Удалить» и выбираем из меню «Удалить строки с листа» .

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

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

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

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

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

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

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

Дополнительные сведения

Excel начинает с шириной по умолчанию 8 символов и переводит его в заданное количество пикселов, в зависимости от шрифта обычного стиля. Затем он округляет это число до ближайшего кратного 8 пикселя, что ускоряет прокрутку между столбцами и строками. Ширина пикселя хранится внутри Excel для размещения данных на экране. Число, отображаемое в диалоговом окне Ширина столбца, — это ширина, которая перепреобразуется в единицы символов (на основе обычного шрифта) для отображения.

Ширина столбца 8,43 означает, что 8,43 знаков шрифта по умолчанию соответствуют ячейке. Шрифтом по умолчанию для листа является шрифт, назначенный стилю “обычный”. В Excel 2000 используется заводской шрифт по умолчанию Arial 10.

Изменение DPI для принтера может повлиять на метрики шрифтов и изменять ширину столбцов. Microsoft Office Excel 2007 использует тему по умолчанию с именем Office. Эта тема по умолчанию имеет Камбриа как шрифт заголовка по умолчанию, а Calibri 11 — шрифт основного текста по умолчанию.

Чтобы определить шрифт по умолчанию для листа, выполните следующие действия:

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

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

  1. В меню Формат выберите стиль.
  2. Выберите команду Изменить.
  3. На вкладке Шрифт выберите требуемый шрифт, начертание и размер.
  4. Нажмите кнопку “ОК”.

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

  1. На вкладке Макет страницы в группе темы нажмите кнопку шрифты.
  2. Нажмите кнопку создать новые шрифты темы.
  3. В полях Шрифт заголовка и Шрифт основного текста выберите нужные шрифты. для использования.
  4. В поле имя введите Office для замены шаблона по умолчанию.
  5. Нажмите кнопку Сохранить.

Если шрифт по умолчанию — непропорциональный шрифт (фиксированная ширина), например Courier, 8,43 символов любого типа (числа или буквы), подходящие в ячейке со значением ширины столбца 8,43, так как все Курьеровые символы имеют одинаковую ширину. Если шрифт является пропорциональным, например Arial, 8,43 целых чисел (таких как 0, 1, 2 и т. д.), подпадают под ячейку со значением ширины столбца 8,43. Это связано с тем, что номера имеют фиксированное пространство с самыми пропорциональными шрифтами. Тем не менее, так как буквы не зафиксированы с пропорциональными шрифтами, дополнительные символы “я” подходят и меньше “w” символов.

При изменении ширины столбца на дробное число может быть задано разное значение ширины столбца в зависимости от шрифта, используемого в стиле Normal. Например, если вы попытаетесь изменить ширину столбца на 8,5, то для столбца задается значение 8,57 или 65 пикселя. Эта проблема возникает из-за преобразования символов шрифтов в пикселные единицы. Не удается отобразить дробные единицы. Таким образом, ширина столбца округляет до ближайшего числа, которое приводит к целому пикселной единице.

Пример поведения ширины столбцов

В новой книге Excel выберите ячейка a1.

В меню Формат выберите пункт столбец, а затем щелкните элемент ширина.

В поле Ширина столбца введите 10 (75 пикселей в ширину), а затем нажмите кнопку ОК.

В меню Формат выберите стиль, а затем убедитесь, что шрифт по умолчанию имеет значение Arial 10.

В ячейке a1 введите 1234567890.

Все буквы полностью соответствуют ячейке, а ширина столбца — 10 (75 пикселей в ширину).

В меню Формат выберите стиль.

Выберите команду Изменить.

На вкладке Шрифт измените шрифт на Courier New, а затем дважды нажмите кнопку ОК

Обратите внимание, что поле Ширина столбца автоматически обновляется в соответствии с новым шрифтом, а номер в ячейке по-прежнему заполняется, несмотря на то, что ширина столбца по-прежнему 10, но увеличена до 85 пикселов

Поведение ширины столбцов в Excel 2007

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

  1. В первом столбце щелкните элемент , чтобы выбрать столбец, а затем щелкните правой кнопкой мыши и выберите пункт Ширина столбца.
  2. Введите нужную ширину столбца.
  3. Нажмите кнопку ОК.

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

Функции ссылок и массивов Excel

Функция ДВССЫЛ в Microsoft Excel

В данной статье рассмотрены некоторые функции по работе со ссылками и массивами:

Читайте также:  Функция ОСТАТ в Excel для работы с остатками товаров на складе

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

  • ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
  • диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
  • номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
  • интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
    • ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
    • ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

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

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

    Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

    Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям.

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

    В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье».

    Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук».

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

    Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(” “;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

    Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения “*” & “иван” & “*” могут подойти строки Иван, Иванов, диван и т.д.

    Также данная функция может искать значения в массивах – =ВПР(1;<2;”Два”:1;”Один”>;2;ЛОЖЬ) – результат выполнения строка «Два».

    Функция ГПР

    Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

    Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

    Функция СТРОКА

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

    Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

    =СТРОКА(D4) – результат 4.
    =СТРОКА() – функция вернет номер строки, в которой она расположена.

    Функция СТОЛБЕЦ

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

    Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

    =СТОЛБЕЦ(C4) – формула вернет значение 3.
    =СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

    Функция АДРЕС

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

    Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

    • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
    • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
    • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
      • 1 – значение по умолчанию, когда закреплены все индексы;
      • 2 – закрепление индекса строки;
      • 3 – закрепление индекса столбца;
      • 4 – адрес без закреплений.
      • ИСТИНА – формат ссылок «A1»;
      • ЛОЖЬ – формат ссылок «R1C1».

      =АДРЕС(1;1) – возвращает $A$1.
      =АДРЕС(1;1;4) – возвращает A1.
      =АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
      =АДРЕС(1;1;4;ЛОЖЬ;”Лист1″) – результат выполнения функции Лист1!R[1]C[1].

      Функция ДВССЫЛ

      Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

      Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

      • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, “C3”, “R3C3” или “D8:D9”.
      • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
        • ИСТИНА – стиль A1. Является значением по умолчанию;
        • ЛОЖЬ – стиль R1C1.

        =ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
        =ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.

        =ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
        =ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.

        Вложение функции ДВССЫЛ со ссылкой на диапазон:

        Функция ЧСТРОК

        Возвращает число строк в указанном диапазоне или массиве.

        Синтаксис: =ЧСТРОК(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.

        Пример использования:
        =ЧСТРОК(D1:D8) – функция возвращает результат 8.
        =ЧСТРОК(<1:2:3:4:5>) – функция определят, что в массиве 5 строк.

        Функция ЧИСЛСТОЛБ

        Возвращает число столбцов в указанном диапазоне или массиве.
        Синтаксис: =ЧИСЛСТОЛБ(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.Пример использования:

        =ЧИСЛСТОЛБ(A5:D5) – результат функции 4.

        =ЧИСЛСТОЛБ(<1;2;3;4;5>) – функция определят, что в массиве 5 столбцов.

        Функция СМЕЩ

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

        Синтаксис: =СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]), где

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

        Если в результате смещения диапазон стал выходить на пределы листа, то функция возвращает ошибку #ССЫЛКА!.

        Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.

        Теперь сместим диапазон на один столбец влево, т.е. на -1.

        Снова изменим ссылку, а именно расширим до 4 столбцов. После этого возвращаемая ссылка станет A3:D12. Результат на изображении.

        Функция ПОИСКПОЗ

        Возвращает позицию элемента, заданного по значению, в диапазоне либо массиве.

        Синтаксис: =ПОИСКПОЗ(искомое_значение; массив; [тип_сопоставления]), где:

        • искомое_значение – обязательный аргумент. Значение элемента, который необходимо найти в массиве.
        • Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
        • тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее способ поиска элемента:
          • 1 – значение по умолчанию. Если совпадений не найдено, то возвращается позиция ближайшего меньшего по значению к искомому элементу. Массив или диапазон должен быть отсортирован от меньшего к большему или от А до Я.
          • 0 – функция ищет точное совпадение. Если не найдено, то возвращается ошибка #Н/Д.
          • -1 – Если совпадений не найдено, то возвращается позиция ближайшего большего по значению к искомому элементу. Массив или диапазон должен быть отсортирован по убыванию.

          элемента не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.
          =ПОИСКПОЗ(“е”; <“а”;”б”;”в”;”г”;”д”>; 0) – возвращается ошибка, т.к.

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

          Для текстовых значений функция допускает использование подстановочных символов «*» и «?».

          :Новые статьи:

          Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

          У Вас недостаточно прав для комментирования.

          ЧАСТОТА Функция Excel

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

          Формула ЧАСТОТЫ в Excel

          Ниже представлена ​​формула ЧАСТОТЫ в Excel.

          Формула частоты в Excel

          Аргументы, используемые для формулы ЧАСТОТА в Excel.

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

          Объяснение функции ЧАСТОТА в Excel

          Частота возвращает массив значений, и поэтому ее необходимо ввести как формулу массива, то есть нажать CTRL + Shift + Enter (или Command + Shift + Enter для Mac). Ячейки, в которые требуется вывод, эти ячейки должны быть выбраны в первую очередь, а затем вводится формула ЧАСТОТА в excel, после чего она вводится как формула массива.

          Выберите ячейки à Введите формулу à Нажмите CTRL + Shift + Enter.

          Возврат

          ЧАСТОТА Функция в Excel возвращает частотное распределение data_array в bins_array интервалы. Результат всегда на единицу больше, чем количество элементов в bins_array. Дополнительный элемент в возвращаемом массиве соответствует количеству значений, превышающих самый высокий элемент bins_array. Предположим, что bins_array содержит три элемента <2, 4, 6>, функция вернет четыре элемента < 6>.

          Если данные_массив не содержит значений, функция ЧАСТОТА Excel возвращает массив нулей. Если bins_array не содержит значений, функция ЧАСТОТА Excel возвращает общее количество элементов, указанных в data_array.

          ЧАСТОТА в Excel – широко используемая функция в статистике. Иногда требуется понять частотное распределение заданных данных, а не только данных. Например, возраст людей в популяции сильно различается и поэтому отображается в виде частот. Точно так же оценки, полученные каждым учеником в классе, объединяются по частотам, чтобы понять общую успеваемость класса.

          ЧАСТОТА в Excel – Иллюстрация

          Предположим, у вас есть числа, для которых вы хотите рассчитать частоту. Цифры <1, 3, 2, 4, 6, 2, 3, 4, 5>приведены в B3: B11.

          ЧАСТОТА Функция Рисунок 1

          Номера должны быть разбиты на интервалы: <2, 4, 6>, указанные в D3: D5.

          ЧАСТОТА Функция Рисунок 2

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

          = ЧАСТОТА (B3: B11; D3: B5)

          ЧАСТОТА Функция Рисунок 3

          и нажмите CTRL + Shift + Enter.

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

          Он вернет частоту.

          ЧАСТОТА Функция Рисунок 4

          Данный выход <3, 4, 2, 0>соответствует интервалу < 6>.

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

          ЧАСТОТА Функция Рисунок 5

          ЧАСТОТА Функция Рисунок 6

          Как использовать функцию ЧАСТОТА в Excel?

          Функция ЧАСТОТА в Excel очень проста и удобна в использовании. Давайте разберемся с работой FREQUENCY in excel на некоторых примерах.

          Пример # 1

          Предположим, вы провели опрос и собрали данные о росте, как показано ниже.

          Пример функции FREQUENCy 1

          Теперь вы хотите рассчитать частоту роста в следующих интервалах:

          Интервалы <155, 160, 165, 170>приведены в E4: E7.

          Чтобы вычислить частоту, сначала выберите пять последовательных ячеек (4 + 1).

          Пример функции FREQUENCy 1-1

          Затем введите следующий синтаксис:

          = ЧАСТОТА (B4: B14; E4: E7)

          Пример функции FREQUENCy 1-2

          и нажмите CTRL + Shift + Enter.

          Он вернет частоту.

          Пример функции 1-3

          Пример # 2

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

          Пример функции 2

          Теперь все те, кто потерпел неудачу (по одному или нескольким предметам), будут считаться «провалившимися». Теперь вам нужно знать количество студентов, которые проиграли.

          Чтобы определить это, вы можете использовать следующий синтаксис:

          = СУММ (- (ЧАСТОТА (B4: B9; B4: B9)> 0))

          Пример функции 2-1

          Пример функции 2-2

          Давайте подробно рассмотрим синтаксис:

          ЧАСТОТА (B4: B9, B4: B9) вычислит частоту данных B4: B9 с использованием интервала B4: B9. Он вернет

          FREQUENCY (B4: B9, B4: B9)> 0 проверяет, больше ли полученная частота нуля. Он возвращает логическое ИСТИНА, если оно больше нуля, иначе ЛОЖЬ. Он вернет

          SUM (- (FREQUENCY (..)> 0)) затем суммирует ИСТИНА и возвращает количество уникальных значений.

          Пример # 3

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

          Пример функции 3

          Теперь вы хотите узнать, в какие промежутки времени покупатели чаще всего посещали магазин. Это поможет вам эффективно спланировать рабочее время сотрудников. Магазин открывается в 11:00 и закрывается в 20:00.

          Давайте сначала определимся с временным интервалом. Мы можем использовать следующие интервалы для простоты:

          • 11:00 УТРА
          • 12:00 УТРА
          • 1:00 ВЕЧЕРА
          • 14:00
          • 3:00 ВЕЧЕРА
          • 4:00 ВЕЧЕРА
          • 5:00 ВЕЧЕРА
          • 18:00
          • 7:00 ВЕЧЕРА
          • 8:00 ВЕЧЕРА

          Теперь выберите ячейки в частотной таблице, чтобы получить. G4: в данном случае G13. Поскольку магазин закрывается в 20:00, мы не выбираем ячейку для> 20:00, так как во всех случаях она будет равна нулю.

          Пример функции 3-1

          Теперь введите следующий синтаксис:

          = ЧАСТОТА (B4: C39, G4: G13)

          и нажмите CTRL + Shift + Enter.

          Пример функции 3-2

          Он вернет частоту посещения магазина покупателем. В этом случае большинство посещений приходилось на период с 17:00 до 18:00.

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

          Пример 2. На листе Excel находится таблица, первый столбец которой (id) находится в столбце A:A. Определить число пустых строк между началом листа и шапкой таблицы.

          Вид части листа с данными:

          Для определения искомого значения используем формулу:

          С помощью функции ПОИСКПОЗ определяем номер строки, в которой находится значение названия первого столбца таблицы. Функция ИНДЕКС возвращает ссылку на ячейку, в которой было определено это значение. Поскольку эта функция возвращает данные ссылочного типа, в результате вычислений выражение типа A1:ИНДЕКС принимает вид A1:A10. Поскольку нас интересуют только пустые строки, вычитаем 1 из значения, найденного функцией ЧСТРОК.

          Формула 5: СЧЕТЕСЛИ и СЧЕТЕСЛИМН

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

          Синтаксис

          Чтобы ввести эту функцию, необходимо указать следующую формулу:

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

          Что означают приведенные аргументы?

          1. Диапазон – это совокупность ячеек, среди которой должен осуществляться подсчет.
          2. Критерий – условие, учитываемое при выборе ячеек.

          Например, этом примере программа посчитала число ключевых запросов, где количество переходов в поисковых системах превышает сто тысяч. В итоге, формула вернула число 3, что означает, наличие трех таких ключевиков.

          ТОП 15 формул в Excel

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

          =СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

          И аналогично предыдущему случаю «Диапазон условия 1» и «условие 1» – это обязательные аргументы, другие же можно не использовать, если нет такой необходимости. Максимально функция предусматривает возможность применять до 127 диапазонов вместе с условиями.

          Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

          Пусть имеется также исходная таблица. Создадим структуру с использованием встроенного средства EXCEL — Промежуточные итоги .

          Скроем строки с Товар2 , нажав на соответствующую кнопку «минус» в структуре.

          Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все неотображаемые строки структурой независимо от используемого значения кода номер_функции и, в нашем случае, подсчитывает сумму только товара Товар1 . Этот результат аналогичен ситуации с автофильтром.

          Функция ПОИСКПОЗ

          Возвращает позицию элемента, заданного по значению, в диапазоне либо массиве.

          Синтаксис: =ПОИСКПОЗ(искомое_значение; массив; [тип_сопоставления]), где:

          • искомое_значение – обязательный аргумент. Значение элемента, который необходимо найти в массиве.
          • Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
          • тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее способ поиска элемента:
            • 1 – значение по умолчанию. Если совпадений не найдено, то возвращается позиция ближайшего меньшего по значению к искомому элементу. Массив или диапазон должен быть отсортирован от меньшего к большему или от А до Я.
            • 0 – функция ищет точное совпадение. Если не найдено, то возвращается ошибка #Н/Д.
            • -1 – Если совпадений не найдено, то возвращается позиция ближайшего большего по значению к искомому элементу. Массив или диапазон должен быть отсортирован по убыванию.

            Пример использования:
            =ПОИСКПОЗ(«Г»; ) – функция возвращает результат 4.

            Где введены числа

            Функция СЧЁТ – подсчитывает блоки, заполненные только числовыми значениями. Выделяем Н1 , ставим «=» , пишем функцию «СЧЁТ» . В качестве аргумента функции укажите нужный диапазон (F1:G10) . Если диапазонов несколько, разделите их «;» – (F1:G10;B3:C8) .

            Всего заполнено 20 блоков. Тот, в котором записан текст, был не учтен, но посчитаны те, которые заполнены датой и временем.

            Работа функции ВПР по нескольким критериям

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

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

            1. – Дата сдачи выручки в кассу.
            2. – Фамилия торгового представителя.

            Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:

            1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
            2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
            3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
            4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

            Результат поиска в таблице по двум условиям:

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

            Разбор принципа действия формулы для функции ВПР с несколькими условиями:

            Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

            Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

            1. элемент – Дата.
            2. элемент – Фамилия.
            3. элемент – Выручка.

            А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений («»).

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

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

            Способ 1: указатель в строке состояния

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

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

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

            Способ 2: использование функции

            Но, вышеуказанный способ не позволяет зафиксировать результаты подсчета в конкретной области на листе. К тому же, он предоставляет возможность посчитать только те строки, в которых присутствуют значения, а в некоторых случаях нужно произвести подсчет всех элементов в совокупности, включая и пустые. В этом случае на помощь придет функция ЧСТРОК. Её синтаксис выглядит следующим образом:

            Её можно вбить в любую пустую ячейку на листе, а в качестве аргумента «Массив» подставить координаты диапазона, в котором нужно произвести подсчет.

            Для вывода результата на экран достаточно будет нажать кнопку Enter.

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

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

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

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

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

            Способ 3: применение фильтра и условного форматирования

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

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

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

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

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

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

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

Ссылка на основную публикацию