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

Как быстро суммировать каждую вторую или n-ю строку / столбец в Excel?

Как быстро суммировать каждую вторую или n-ю строку / столбец в Excel?

Используйте команду Формула, чтобы добавить столбец или строку чисел в таблицу.

Выберите ячейку таблицы, в которой вы хотите получить результат.

На вкладке Работа с таблицами | Макет щелкните Формулы.

Кнопка "Формула"

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

Формула суммы в поле суммы

Функция =SUM(ABOVE) складывает числа в столбце над выделенной ячейкой.

С помощью функции =SUM(LEFT) числа в строке из выбранной ячейки суммируются.

При выделении ячейки функция =SUM(BELOW) суммирует числа в столбце под данной ячейкой.

Выделенная ячейка добавляется к строке справа от нее с помощью =SUM(RIGHT).

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

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

Другие формулы для таблиц

Word также предоставляет другие функции для таблиц. Рассмотрим AVERAGE и PRODUCT.

Щелкните ячейку таблицы, в которой вы хотите получить нужный результат.

Щелкните Формула на вкладке Макет раздела Работа с таблицами.

Кнопка "Формула"

В поле Формула удалите формулу SUMM, но не удаляйте знак равенства (=). Затем щелкните в поле In и выберите нужную функцию.

Поле "Формула" с меню функций

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

Добавьте числа в столбце над выбранной ячейкой в формулу, введя ABOVE, а затем нажмите OK.

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

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

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

Если вы хотите найти среднее значение чисел в строке слева от ячейки, нажмите кнопку AVERAGE и выберите LEFT:

Для того чтобы перемножить два числа, нажмите ПРОИЗВЕСТИ и введите макет ячейки таблицы следующим образом:

Чтобы формула включала определенный диапазон ячеек, необходимо выделить эти ячейки. Представьте себе электронную таблицу, в которой каждый столбец состоит из буквы, а каждая строка — из числа, подобно электронной таблице Microsoft Excel. Умножьте второй и третий столбцы второй строки на =PRODUCT(B2:C2).

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

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

На вкладке макета, следующей за вкладкой Table Builder, выберите формулу.

На вкладке "Макет" нажмите кнопку "Формула".

Проверьте данные в скобках, чтобы убедиться, что в Word есть правильные ячейки для подсчета суммы.

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

= SUM(ABOVE) складывает числа в столбце над выделенной ячейкой.

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

Используя функцию =SUM(LEFT), вы можете сложить числа в строке слева от выбранной ячейки.

Функция =SUM(BELOW) складывает числа в столбце под выбранной ячейкой.

В результате использования функции =SUM(RIGHT) числа в правой части выделения складываются вместе.

После изменения чисел, которые нужно сложить, выберите сумму и нажмите FN+F9, чтобы отобразить новые результаты.

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

Другие формулы для таблиц

Помимо таблиц, в Word есть и другие функции. Рассмотрим AVERAGE и PRODUCT.

В таблице выделите ячейку, в которой вы хотите получить результат.

На вкладке Дизайн рядом с вкладкой Построитель таблиц выберите команду Формула.

На вкладке "Макет" нажмите кнопку "Формула".

В поле Формула удалите формулу SUMM, но не удаляйте знак равенства (=). Затем щелкните в поле In и выберите нужную функцию.

В поле "Формула" выберите функцию из списка "Вставить функцию".

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

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

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

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

Включите числа в правой части выделенной ячейки в формулу, введя ПРЯМО.

Например, нажмите AVERAGE и введите LEFT, чтобы вычислить среднее значение чисел в строке, находящейся слева от ячейки:

Умножьте два числа на PRODUCT и введите макет ячейки для таблицы:

Чтобы включить в формулу определенный диапазон ячеек, необходимо выбрать эти ячейки. Представьте себе электронную таблицу, в которой каждый столбец состоит из буквы, а каждая строка — из числа, подобно электронной таблице Microsoft Excel. Например, вы можете перемножить числа во втором и третьем столбцах второй строки, набрав =PRODUCT(B2:C2).

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

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

Изображение команды "Открыть в настольном приложении"

Затем следуйте указаниям для классической версии Word. Формула будет сохранена, как только вы сохраните документ и откроете его в Word.

Как выделить каждую вторую строку или столбец в Excel цветом

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

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

Как автоматически выделить каждый четвертый столбец

Пример таблицы.

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

  1. Выделяем столбцы таблицы без заголовков. В данном случаи — это диапазон A2:I14.
  2. Активируйте инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:». Создать правило.
  3. В поле для ввода формулы вводим следующе значение: СТОЛБЕЦ.
  4. Щелкните на кнопку «Формат», чтобы задать оформление для столбцов. Граница.
  5. В появившемся окне «Формат ячеек» на вкладке «Граница» слева укажите тип линии и ее цвет, а справа активируйте новые вертикальные границы. Заливка.
  6. На вкладке «Заливка» укажите цвет фона ячеек и нажмите ОК на всех открытых окнах.

При построении формулы мы используем функцию =OSTAT(), которая возвращает остаток от деления первого аргумента на второй. Мы используем =Column() в первом аргументе функции FAT, которая возвращает номер текущего столбца. Поэтому мы используем в аргументе A2 относительную ссылку, а не абсолютную, чтобы получить все номера столбцов в выбранном диапазоне. Вторым аргументом функции ESTAT является число 4 (на него мы делим номер каждого столбца). После функций мы проверяем, равен ли результат после деления нулю, с помощью оператора «=». Как только результат = 0, форматирование завершено.

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

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

СТРОКА.

То есть вместо функции HUNDRED мы использовали функцию STRING и делили на 2 (каждую секунду).

Каждая вторая строка.

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

Суммировать выделенные ячейки в excel. Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

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

Excel не предоставляет стандартной функции для суммирования каждой n-й ячейки или строки. Однако выполнить эту задачу можно разными способами. Все эти подходы основаны на функциях ROW и OST.

Функция СТРОКА (ROW) возвращает номер строки для заданной ссылки на ячейку: ROW(reference), в русской версии Excel СТРОКА(ссылка).
Функция ОСТАТ (MOD) возвращает остаток от деления числа на делитель: MOD(number;divisor), в русской версии Excel ОСТАТ(число;делитель).

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

Поместите функцию ROW в функцию MOD (для передачи числового аргумента), разделите на 2 (для суммирования каждой второй ячейки) и проверьте, равен ли результат нулю. Если да, то ячейка суммируется. Существует множество способов использования этих функций — некоторые из них дают лучшие результаты, чем другие. Формула массива для суммирования каждой второй ячейки в диапазоне $A$1:$A$100 будет выглядеть так: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$A$1:$A$500;0)), в русской версии Excel =SUM(IF(ROW($A$1:$A$500);2)=0;$A$1:$A$500;0)).

Поскольку это формула массива, необходимо ввести ее, нажав сочетание клавиш Ctrl+Shift+Enter, Excel добавит фигурные скобки, чтобы она выглядела так: <=SUM(IF(MOD(ROW($A$1:$A$500),2)=0;$A$1:$A$500;0))>, в русской версии Excel: <=СУММ(ЕСЛИ(ОСТАТ(СТРОКА($А$1:$А$500);2)=0;$А$1:$А$500;0))>Нужно, чтобы Excel сам добавил фигурные скобки; если вы добавите их самостоятельно, формула не будет работать.

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

Вот другая формула, которая представляет собой немного лучший вариант: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) В русском Excel =SUMMPRODUCT((ROW($A$1:$A$500);2)=0)*($A$1:$A$500))) .

Однако в результате эта формула вернет значение #VALUE. (#VALUE! ) ошибку, если какие-либо ячейки в диапазоне содержат текст, а не числа. Помимо того, что это формула, она также замедляет работу Excel, если используется слишком много раз или ссылается на большое количество чисел.

К счастью, существует лучший способ, который не только более эффективен, но и гораздо более гибок. Он требует использования функции DSUM. Для суммирования каждой n-й ячейки мы использовали диапазон A1:A500.

В ячейку E1 введите слово Критерии. В ячейку E2 можно ввести формулу =МОД(ROW(A2)-$C$2-1;$C$2)=0, в русской версии Excel =ОСТАТ(ROW(A2)-$C$2-1;$C$2)=0. Для проверки данных выделите ячейку C2 и нажмите кнопку Данные → Проверка.

В поле Разрешить выберите Список, а в поле Источник введите 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Убедитесь, что флажок Разрешить список (в ячейке) установлен, а затем нажмите OK. В ячейке C1 должен появиться текст SUM every. Чтобы получить формулу в русской версии Excel, введите =DSUM($A:$A;1;$E$1:$E$2) .

В ячейке непосредственно над той, где вы ввели функцию БДСУММ (DSUM), введите текст =»Summing Every» & $С$2 & CHOOSE($C$2;»st»;»nd»;»rd»;»th»;»th»;»th»;»th»;»th»;»th»;»th») & «Cell» . Теперь осталось только выбрать нужное число в ячейке С2, а остальное сделает функция БДСУММ (DSUM).

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

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

Ранее я описал, как использовать пользовательскую функцию для поиска . К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «довести до совершенства» эту функцию. Но за два года, прошедшие с момента публикации этого поста, я не смог написать удобоваримый код ни самостоятельно, ни с помощью информации из Интернета….. (Дополнение от 29 марта 2017 года. Спустя еще пять лет мне все же удалось написать код; см. конец поста). А недавно я наткнулся на идею в книге Д. Хоули, Р. Хоули «Excel 2007. Трюки», который позволяет обойтись без кода.

Предположим, что имеется список чисел от 1 до 100 в диапазоне A1:A100 (рис. 1; см. также лист Excel «SUMMESLI». Диапазон имеет условное форматирование, которое отмечает ячейки, содержащие числа больше 10 и меньше или равные 20.

Рис. 1. Условное форматирование выделяет значения от 10 до 20 в этом ряду чисел

Скачать справку в , примеры в формате

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

Чтобы сложить диапазон ячеек, отвечающих одинаковым критериям, используйте функцию СУММЕРКИ (рис. 2).

Рисунок 2. Сумма клеток, удовлетворяющих одному условию

S UMMESLYMN может использоваться при наличии ряда условий (рисунок 3).

Рисунок 3: Сумма ячеек для клеток, удовлетворяющих нескольким условиям

Вы можете использовать COUNTERSOLI для подсчета количества ячеек, которые отвечают тем же критериям.

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

В Excel можно также задать несколько условий. Эта функция входит в набор функций базы данных Excel и называется BDSUMM. Используйте тот же набор чисел (Рисунок 4; см. также лист Excel «BDSUMM» для тестирования).

Рис. 4. Использование функций базы данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите Макросы ->Макросы ; в окне Макрос , выделите строку СумЦветУсл , и нажмите Выполнить . Запуститься макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

Применение . летучие истинные

Dim SumColor As Double

Dim UserRange As Range

Dim CriterionRange As Range

Set UserRange = Application.InputBox(_

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

Prompt:=»Выберите диапазон суммирования», _

Название:=»Выбор местности», _.

Set CriterionRange = Application . InputBox (_

Prompt : = «Выберите критерий суммирования» , _

Название: = «Выбор критериев, _

По умолчанию : = ActiveCell . Адрес , _

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

Можно ли суммировать данные по нескольким критериям? ?

Решение: Способ 1:

В Датской версии

КАК ЭТО РАБОТАЕТ:

Согласно параметрам, указанным в ячейках I1:K2 (Продавец = Иванов ; Товары = Карандаши ; Месяц = Январь), BDSUMM извлекает и суммирует данные столбца Количество (аргумент «Поле» = F1) по критериям, указанным в ячейках A1:G16 (Продавец = Иванов ; Товары = Карандаши ; Месяц = январь).

Список критериев должен быть включен на листе.

ПРИМЕЧАНИЯ : Количество критериев суммирования ограничено оперативной памятью.

ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel

В английском варианте:

ВОТ КАК ЭТО ПРОИСХОДИТ:

На основе предоставленных вами критериев SUMPROIZE создает массивы значений TRUE и FALSE в память Excel.

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

Очевидно, что если, например, D2=Карандаши, то значение будет True, а если D3=Папки, то False (поскольку критерием выбора товара в нашем примере являются Карандаши).

Зная о том что значение ИСТИНА всегда равно 1, а ЛОЖЬ всегда равно 0 мы продолжаем работать с массивами как с числами 0 и 1.
Перемножив полученные значения массивов между собой последовательно, мы получим ОДИН массив из нолей и единиц. Там где выполнялись все три критерия отбора, (ИВАНОВ, КАРАНДАШИ, ЯНВАРЬ ) т.е. все условия принимали значения ИСТИНА получаем 1 (1*1*1 = 1), если же хотя-бы одно условие не выполнялось — получим 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0*1*1 = 0).

Теперь просто умножьте этот массив на массив, содержащий данные, которые вы хотите суммировать (диапазон F2:F16 ), а затем просуммируйте данные, которые не умножаются на 0.

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

Я думаю, все это имеет смысл 🙂

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

ПОДПИСАТЬСЯ: Все версии Excel

Метод 3: матричная формула

В английском варианте:

Р АБОТАЕТ ТАК ЖЕ, КАК И СПОСОБ №2. Есть только два отличия — доступ к этой формуле осуществляется при нажатии Ctrl+Shift+Enter, а не просто Enter, и первый и 0-й массивы не умножаются на диапазон суммирования, а выбираются с помощью функции IF.

При использовании формул массивов время пересчета резко возрастает.

ПРИМЕЧАНИЯ : Количество обрабатываемых массивов ограничено 255.

ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel

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