Evolcom.ru

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

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

Excel. Суммирование значений в четных (или кратных) строках (столбцах)

В третьем столбце одного из недавних рабочих проектов мне нужно было просуммировать значения. Данный пример будет рассмотрен позже из-за его сложности, но мы начнем с более простого случая нахождения суммы значений для всех четных/нечетных строк. Исходные данные изображены на рис. Для получения дополнительной информации обратитесь к листу Excel «Пример1».

Рисунок 1 Данные о прибытии и отправлении [1].

Скачать записку в формате Word или pdf, примеры в формате Excel

Метод 1: Перечислить четные строки. Это проще для понимания (на мой взгляд), но требует дополнительной колонки для промежуточных расчетов. В столбце E мы вводим формулу =EACH(STRING()), которая возвращает TRUE, если строка четная, и FALSE, если нечетная (Рисунок 2).

Рисунок 2: Определение четности строчного номера

Затем необходимо применить две концептуальные формулы суммирования (рисунок 3)

Рисунок 3: Использование условной функции суммирования

Формула SUMMESLY суммирует только те значения из диапазона D3:D46 (Sum_range), для которых значение в столбце E равно FALSE. Аналогичная формула используется для расчета общего расхода в ячейке H4 путем умножения =СУММЕСЛИ(E3:E46;TRUE;D3:D46).

Дополнение от 20.09.15: Еще проще использовать формулу =SUM(C:C; «Доход» ;D:D), которая суммирует только те значения из столбца D, для которых значение в столбце C — это доход.

2-й способ. Использование формулы массива. Если вы не знакомы с формулами массива, настоятельно рекомендую пройти по ссылке и почитать подробнее. На мой взгляд, работу формул массива кратко можно сформулировать так: формулы массива сначала выполняют действия, прописанные в них, над элементами массива, а потом суммируют эти действия для всех элементов массива (что-то наподобие формулы СУММПРОИЗВ – сумма произведений). Для того чтобы ввести в ячейку формулу массива, надо набрать формулу, а затем одновременно нажать CTRL+SHIFT+ВВОД. Это приведет к тому, что вокруг формулы (см. строку формул на рис. 4) появятся фигурные скобки: < и >. Не пытайтесь ввести скобки с клавиатуры; формула массива не возникнет! После редактирования формулы массива (или если вы случайно встали курсором в ячейку с такой формулой), повторно нажмите CTRL+SHIFT+ВВОД.

Читайте так же:
Как повторить строку заголовка каждую n-ю строку в Excel?

Рисунок 4. Использование формулы массива для суммирования значений в нечетных строках.

Давайте посмотрим, как формула массива работает в нашем случае (рисунок 4)

  1. СТРОКА(D3) – определяет номер строки для ячейки D3;
  2. ЕНЕЧЁТ(СТРОКА(D3)) – определяет, является ли номер строки, содержащей ячейку D3, нечетным; если нечетный, возвращает значение ИСТИНА (или единица), если четный – ЛОЖЬ (или ноль);
  3. ЕНЕЧЁТ(СТРОКА(D3))*D3 – умножает значение, хранящееся в ячейке D3 (536) на значение, полученное на предыдущем шаге;
  4. <=СУММ(ЕНЕЧЁТ(СТРОКА(D3:D46))*(D3:D46))>– суммирует в диапазоне D3:D46, все значения, полученные на предыдущем шаге, то есть, ЕНЕЧЁТ(СТРОКА(D3))*D3 + ЕНЕЧЁТ(СТРОКА(D4))*D4 + … + ЕНЕЧЁТ(СТРОКА(D46))*D46

Для вычисления суммарного расхода в ячейке Е49 используется формула <=СУММ(ЕЧЁТН(СТРОКА(D3:D46))*(D3:D46))>, которая находит сумму значений в диапазоне D3:D46 только для четных строк.

Из книги Билла Джалена «Excel Gurus Expand Your Horizons: Do the Impossible with Microsoft Excel» (рис. 4a). Используйте формулу для вычисления значений в нечетных строках: =SUMPROV(REMAIN(ROW(A2:A99);2);(C2:C99)). Чтобы подсчитать половину значений в четных рядах, используйте формулу: =SUMPROV(REMAIN(LINE(A2:A99);2;(C2:C99)))

Узнайте подробнее, как это работает:

  1. STROCK(A2) — определяет номер строки для ячейки A2;
  2. OSTAT(STROCK(A2;2) — определяет остаточное значение от деления значения, полученного на предыдущем шаге, на два; известно, что OSTAT() будет равен нулю для четных строк и единице для нечетных;
  3. OSTAT(STRIKE(A2;2)+1) — прибавляет единицу к предыдущему результату; теперь значение равно 1 для четных строк и 0 для нечетных
  4. Затем значение 1 или 0 умножается на значение в ячейке C2; т.е. значения в четных строках в столбце C равны друг другу, а значения в нечетных строках равны нулю;
  5. SUMPROIZE() суммирует только значения в столбце C в четных строках.

Рис. 4а. Функция СУММПРОИЗВ для суммирования значений в четных строках

Рисунок 4a. Функция SUMMPROIZV для суммирования значений в четных строках

Формулы (см. файл Excel, лист «Рисунок 4a») также будут использоваться для сложения значений четных строк:

=SUMPROV(OSTAT(ROW(2:99);2)+1 ;(D2:D99)); Для подсчета номера строки не обязательно указывать буквы;

=SUMPROIZ( — — (OSTAT(STRIKE(C2:C99);2)=0);(E2:E99)); если строка четная, то OSTAT()=0, поэтому эта часть формулы возвращает TRUE, в то время как операция минус — минус возвращает 1;

=SUMPROV(OSTAT(STRIKE(A1:A98);2);(F2:F99)); Вы просто перенесли часть формулы, определяющую 1/0, на строку выше.

Вернемся к более общей задаче — суммированию строк (столбцов), кратных n (рис. 5; см. также лист Excel-файла «Пример2»). Вам нужно найти итог по запасам, который представляет собой сумму по строке 23 для всех столбцов с заголовком «Запасы».

Рисунок 5. Суммирование значений в каждом третьем столбце

Рассмотрим, как работает формула массива во втором примере (Рисунок 5):

  1. СТОЛБЕЦ(B23) – определяет номер столбца для ячейки В23;
  2. ОСТАТ(СТОЛБЕЦ(B23);3) – определяет, остаток от деления номера столбца на 3;
  3. ЕСЛИ(ОСТАТ(СТОЛБЕЦ(B23);3)=2;1;0) – если остаток равен двум, функция ЕСЛИ возвращает значение 1; если не равен – 0;
  4. ЕСЛИ(ОСТАТ(СТОЛБЕЦ(B23);3)=2;1;0)*B23 – умножает значение, хранящееся в ячейке В23 (1) на значение, полученное на предыдущем шаге;
  5. <=СУММ(ЕСЛИ(ОСТАТ(СТОЛБЕЦ(B23:BX23);3)=2;1;0)*B23:BX23)>– суммирует в диапазоне В23:ВХ23, все значения, полученные на предыдущем шаге, то есть, ЕСЛИ(ОСТАТ(СТОЛБЕЦ(B23);3)=2;1;0)*B23 + ЕСЛИ(ОСТАТ(СТОЛБЕЦ(С23);3)=2;1;0)*С23 + ЕСЛИ(ОСТАТ(СТОЛБЕЦ(D23);3)=2;1;0)*D23 + … + ЕСЛИ(ОСТАТ(СТОЛБЕЦ(23);3)=2;1;0)*23.

Метод 2, также от Билла Джалена. Используйте формулу для сложения значений каждой третьей строки: =SUMPROV( — — (REST(LINE(A2:A148);3)=2) ;(C2:C148))).

Рис. 5а. Функция СУММПРОИЗВ для суммирования значений в каждой третьей строке

Рисунок 5a. Функция SUMMPROIZV для суммирования значений в каждой третьей строке

Вот как выглядит эта формула:

  1. STROCK(A2) — определяет номер строки для ячейки A2;
  2. OSTAT(STROCK(A2);3) — определяет остаток от деления значения, полученного на предыдущем шаге, на три;
  3. OSTAT(STROCK(A2); 3)=2 — возвращает TRUE, если остаток от деления равен 2, и FALSE, если он равен 0 или 1;
  4. Операция Minus преобразует булевы значения TRUE/FALSE в числовые значения 1/0;
  5. SUMPROIZE() суммирует только значения в столбце C для строк, которые вернули 1 на предыдущем шаге.

[1] Данные очень хорошо разработаны для визуализации, но очень плохо разработаны для дальнейшей обработки. Если возможно, оформите исходные данные в формате, пригодном для дальнейшей обработки в электронной таблице. Рекомендую отличную книгу на эту тему: Bill Jelen, Michael Alexander «Crosstabs in Microsoft Excel 2010». Если бы данные были разработаны соответствующим образом, то никаких уловок не потребовалось бы. Данные могли быть обработаны с помощью стандартных электронных таблиц.

Как четные и нечетные числа выделить разным цветом в Excel

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

Как найти четные числа в Excel

Набор четных и нечетных чисел, которые должны быть автоматически выделены разными цветами:

Числа.

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

  1. Выделите диапазон ячеек A1:A8 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». Создать правило.
  2. Ниже выберите: «Использовать формулу для определения форматируемых ячеек». Формула.
  3. Чтобы найти четное число в Excel ниже введите формулу: =ОСТАТ(A1;2)=0 и нажмите на кнопку «Формат», чтобы задать зеленый цвет заливки ячеек. И нажмите ОК на всех открытых окнах. Зеленый формат.
  4. Чтобы додать второе условие, не снимая выделения с диапазона A1:A8, снова выбираем инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек». ОСТАТ.
  5. В поле ввода введите формулу: =ОСТАТ(A1;2)<>0 и нажмите на кнопку «Формат», чтобы задать синий цвет заливки ячеек. И нажмите ОК на всех открытых окнах. Красный.
  6. К одному и тому же диапазону должно быть применено 2 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»

Две формулы отличаются только операторами сравнения перед значением 0. Закройте окно Диспетчер правил, нажав кнопку OK.

Пример.

Поэтому ячейки с непарными номерами заполнены синим цветом, а ячейки с парными номерами — зеленым.

Функция ОСТАТ в Excel для поиска четных и нечетных чисел

Функция =ОСТАТ() возвращает остаток от деления первого аргумента на второй. В первом аргументе мы указываем относительную ссылку, так как данные берутся из каждой ячейки выделенного диапазона. В первом правиле условного форматирования мы указываем оператор «равно» =0. Так как любое парное число, разделенное на 2 (второй оператор) имеет остаток от деления 0. Если в ячейке находится парное число формула возвращает значение ИСТИНА и присваивается соответствующий формат. В формуле второго правила мы используем оператор «неравно» <>0. Таким образом выделяем синим цветом нечетные числа в Excel. То есть принцип работы второго правила действует обратно пропорционально первому правилу.

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