Poldet1.ru

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

Как пронумеровать каждую вторую строку в Excel?

Суммирование каждой 2-й, 3-й. N-й ячейки

Предположим, у нас есть такая таблица с данными о доходах и выполнении планов по нескольким городам:

sum-every-N-row1.png

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

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

sum-every-N-row2.png

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

Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию

Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF) :

sum-every-N-row3.png

Первый аргумент этой функции — диапазон проверяемых ячеек, второй — критерий отбора (слово "Выручка"), третий — диапазон ячеек с числами для суммирования.

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

Способ 2. Формула массива для суммирования каждой 2-й, 3-й . N-й строки

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

Читайте так же:
Как распечатать данные по центру страницы?

sum-every-N-row4.png

Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:

  • Функция ROW выдает номер строки для каждой ячейки в диапазоне B2:B15.
  • Функция MOD вычисляет остаток от каждого числа ряда, деленного на 2.
  • Функция IF проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратный 2), то выводит содержимое следующей ячейки, иначе — ничего.
  • Наконец, функция SUM суммирует набор значений, выведенных IF, т.е. складывает все 2 числа в диапазоне.
  • Эта формула должна быть введена как формула массива, т.е. после ее ввода нажмите Ctrl+Alt+Enter вместо Enter. Вам не нужно набирать скобки на клавиатуре, они будут добавлены в формулу автоматически.

Для ввода, отладки и общего понимания таких формул можно использовать следующий прием: если выделить раздел сложной формулы и нажать F9, Excel выполнит вычисление непосредственно в строке формул и отобразит результат. Например, если мы выберем функцию STRING(B2:B15) и нажмем F9, мы увидим массив номеров строк для каждой ячейки нашего диапазона:

sum-every-N-row5.png

Как только вы выделите фрагмент ESTAT(B2:B15;2) и нажмете F9, вы увидите массив результатов функции ESTAT. остатков от деления номеров строк на 2:

sum-every-N-row6.png

Наконец, если вы выделите раздел IF(FTA(B2:B15);2)=0;B2:B15) и нажмете F9, вы увидите, что на самом деле дает функция TOTAL в нашей формуле:

sum-every-N-row7.png

В этом случае значение FALSE интерпретируется Excel как ноль, и мы получаем сумму всех остальных чисел в нашем столбце.

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

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

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

sum-every-N-row8.png

Способ 3. Функция БДСУММ и таблица с условием

Формулы массива из предыдущего способа — штука красивая, но имеют слабое место — быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой — функцией БДСУММ (DSUM) . Перед использованием эта функция требует небольшой доработки, а именно — создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) — не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM) :

sum-every-N-row10.png

Способ 4. Суммирование каждой 2-й, 3-й. N-й строки

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

sum-every-N-row11.png

Поскольку функция SUMPRODUCT автоматически преобразует свои аргументы в массивы, в этом случае даже не нужно нажимать Ctrl+Shift+Enter.

Заполнение ячеек последовательностью чисел

Этот метод полезен, если таблица небольшая.

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

  • Чтобы ввести начальное значение в первой ячейке;

Нумерация ячеек в вертикальном направлении

  • Во второй ячейке впишите следующее значение;

Нумерация ячеек в горизонтальном направлении

  • То необходимо выбрать обе ячейки.

Перетащите маркер выделения на нужное количество ячеек (для этого переместите поле внизу выделения в нужном направлении).

Обратите внимание: если в первой ячейке вы введете цифру 2, а во второй — цифру 4, то нумерация будет производиться по геометрической прогрессии: 2,4,6,8,10… Если вы будете перемещать маркер выделения снизу вверх или справа налево, то нумерация будет производиться в отрицательную сторону.

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

Нумерация строк в отрицательном направлении

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

настройка роутера dir 320 ростелеком

Войдите в конфигурацию маршрутизатора D LINK? Вы можете найти его здесь.

Способ 2 — автоматическая последовательная нумерация

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

На следующем этапе точно так же стяните все вниз и заполните диапазон.

пособ 2 — автоматическая последовательная нумерация

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

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

Имеется простая заполненная таблица с несколькими строками и столбцами. Левая колонка с серийным номером заполняется с помощью Excel в полуавтоматическом режиме.

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

Как нумеровать ряды в Excel

Создаем образец заполнения

Первое, что нужно сделать, — создать шаблон. В первую ячейку вводим начальное значение, во вторую — последующее. Следующие ячейки будут пронумерованы в соответствии с разницей между ними, рассчитанной Excel. В первую ячейку вводим число 1, во вторую — число 2 в порядке нумерации 1,2,3,4,5 и так далее.

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

Нумеруем остальные ячейки

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

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

таблица Microsoft Excel 2010

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

Читайте так же:
Как разбить данные на несколько листов на основе столбца в Excel?

таблица Microsoft Excel 2010

Электронные таблицы Microsoft Excel 2010

В этом случае выбранные вами цифры появятся во всех ячейках.

таблица Microsoft Excel 2010

Электронную таблицу Microsoft Excel 2010

2 Как в Экселе пронумеровать строки c помощью формулы

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

  • Первая ячейка, как и в прошлом варианте, должна содержать начальное число. Указываем его (например, «1») — и переходим к следующей ячейке.
  • Теперь понадобится функция, которая будет прибавлять к каждому последующему значению единицу (или другой необходимый шаг нумерации). Она выглядит так: «=[ячейка с первым значением] + 1». В нашем случае это «=А1+1». Для создания формулы можно использовать кнопку “СУММ” из верхнего меню — она появится, когда вы поставите в ячейке знак “=”.
  • Кликаем по крестику в углу ячейки с формулой и выделяем диапазон заполнения данных. Строки пронумеруются автоматически.

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