Adelitusn.ru

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

Трюк №69. Как в Excel 2010 просуммировать каждую вторую, третью или n-ую строку или ячейку

Трюк №69. Как в Excel 2010 просуммировать каждую вторую, третью или n-ую строку или ячейку

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

В Excel не предусмотрено стандартной функции, которая может суммировать каждую n-ю ячейку или строку. Однако можно выполнить эту задачу несколькими различными способами. Все эти подходы основаны на функциях СТРОКА (ROW) и ОСТАТ (MOD).

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

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

Вот другая формула, представляющая собой чуть лучший выбор: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) , в русской версии Excel =СУММПРОИЗВ((ОСТАТ(СТРОКА($А$1:$А$500);2)=0)*($А$1:$А$500)) .

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

К счастью, есть лучший способ, представляющий собой не только более эффективное, но и намного более гибкое решение. Он требует применения функции БДСУММ (DSUM). В этом примере мы использовали диапазон А1:А500 как диапазон, в котором необходимо суммировать каждую n-ю ячейку.

В ячейку Е1 введите слово Criteria. В ячейку Е2 введите следующую формулу: =MOD(ROW(A2)-$C$2-1;$C$2)=0 , в русской версии Excel =ОСТАТ(СТРОКА(А2)-$С$2-1;$С$2)=0 . Выделите ячейку С2 и выберите команду Данные → Проверка (Data → Validation).

В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. В ячейке С1 введите текст SUM every…. В любой ячейке, кроме строки 1, введите следующую формулу: =DSUM($A:$A;1;$E$1:$E$2) , в русской версии Excel =БДСУММ($А:$А;1;$Е$1:$Е$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?

Я пытаюсь выяснить, как «сгруппировать» значения в электронной таблице Excel.

Приведенный ниже пример (это инвентарный лист, который я использую в моем ресторане 🙂

Читайте так же:
Sleep as Android

Образец таблицы

Справа от таблицы есть столбец «Стоимость проданных товаров».

Я хотел бы иметь возможность составить общий показатель COGS для каждой категории.

Это возможно в Excel? Более того, я бы хотел сделать это на отдельном листе, чтобы не повредить первый лист (на рисунке).

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

Вы также можете сделать это с помощью функции Промежуточный итог.

  1. Перейдите на вкладку «Данные» на панели инструментов Excel.
  2. Нажмите кнопку Сортировка и сортируйте по столбцу вашей категории
  3. Нажмите кнопку «Промежуточный итог» и заполните соответствующее диалоговое окно, затем нажмите «ОК».

При каждом изменении в: Категория

Использовать функцию: сумма

Добавьте промежуточный итог к: Стоимость проданных товаров

Я предполагаю, что вы ищете общий COGS для учетной записи, например, бакалейной, например, за неделю 1.

sumif это самый простой способ получить эти данные. пример взят из вашей картинки:

Я поставил знаки вопроса, так как не вижу весь лист. Вы должны выбрать весь столбец D, где у вас есть данные учетной записи. D2 через D ?, То же самое с недельным столбцом использования J2 через J ?.

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

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

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

  • Перейдите на вкладку « Данные » (или нажмите Alt-A на клавиатуре).
  • Нажмите Сортировка (или нажмите S на клавиатуре)
  • Щелкните стрелку раскрывающегося списка рядом с «Сортировать по» в главной области всплывающего окна.
  • Выберите «Категория», так как это то, что вы хотите сгруппировать по.
  • Нажмите ОК!

Промежуточный итог

В Excel 2013 будет опция «группировка».

  1. Выберите свой стол.
  2. Перейдите на вкладку «Данные».
  3. Нажмите Структура> Группировка.

Смотрите скриншот ниже, к сожалению, это немецкий — но значки помогут вам его найти.

Gliederung> Группа

Чтобы сделать это с помощью сводной таблицы :

  1. Выберите все данные (включая метки столбцов)
  2. Вставить> Сводная таблица
  3. Перетащите поле в измерение (строку или столбец) сводной таблицы, чтобы сгруппировать его
  4. Перетащите поле в область «значений», чтобы вычислить метрику об этом; в этом случае сум.

введите описание изображения здесь

Я делаю это все время с VBA. Я почти уверен, что использовал тот же метод, начиная с Office 95 ‘, с небольшими изменениями, внесенными для размещения столбцов. Это может быть сделано с меньшим количеством строк, если вы не определяете переменные. Это может быть сделано быстрее, если у вас есть много строк для прохождения или больше вещей, с которыми вам нужно определить свою группу.

Я сталкивался с ситуациями, когда «группа» основана на 2-5 ячейках. Этот пример рассматривает только один столбец, но его можно легко развернуть, если кто-то найдет время поиграть с ним.

Читайте так же:
Скачать Smart Poster на русском языке

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

1) Выберите диапазон данных.
2) Скопировать диапазон в буфер обмена.
3) Открыть новую базу данных Access.
4) Создайте новую таблицу или пасту.
5) Создать представление, SQL View.
6) Создайте запрос «ВЫБЕРИТЕ ячейку, СУММА (данные) КАК ОБЩАЯ ОТ ЯЧЕЙКИ ГРУППЫ BY BY»

Почему эксель неправильно считает сумму столбца

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

Основные причины неисправности

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

Изменяем формат ячеек

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

Чтобы проверить, действительно ли дело в формате, следует перейти во вкладку «Главная». Предварительно, необходимо выбрать непроверенную ячейку. В этой вкладке находится информация о формате.

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

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

В открытом окне находится полный список форматов с описанием и настройками. Достаточно выбрать нужный и нажать на «ОК».

Отключаем режим «Показать формулы»

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

Для отключения функции «Показать формулы», следует перейти в соответствующий раздел «Формулы». Здесь находится окно «Зависимости». Именно в нем расположена требуемая команда. Чтобы отобразить список всех зависимостей, следует кликнуть на стрелочке. Из перечня необходимо выбрать «Показать» и отключить данный режим, если он активен.

Ошибки в синтаксисе

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

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

Читайте так же:
Сообщение "На компьютере недостаточно памяти" в Windows 8/8.1

Включаем пересчет формулы

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

В открытом окне необходимо перейти во вкладку «Формулы». Здесь находятся параметры вычислений. Достаточно установить флажок на пункте «Автоматически» и сохранить изменения, чтобы система начала проводить перерасчет.

Ошибка в формуле

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

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

Откроется окно, которое отображает саму формулу. Здесь, следует нажать на «Вычислить», чтобы провести проверку ошибки.

Другие ошибки

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

Формула не растягивается

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

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

Неверно считается сумма ячеек

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

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

Формула не считается автоматически

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

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

Бухгалтеры (и не только) знают одну «нехорошую» особенность Excel – «неумение» правильно суммировать. 🙂 Иногда это приводит к казусам в бухгалтерских документах, сформированных в Excel (рис. 1)

Рис. 1. Фрагмент счет-фактуры с «неверным» суммированием

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

Видно, что общий итог по налогу (значение в ячейке G7) и стоимости товаров (Н7) отличаются на копейку от суммы по строкам (G4:G6 и Н4:Н6, соответственно). Это ошибка является следствием округления. Дело в том, что значения только отображаются в формате с двумя десятичными знаками. Фактические значения в этих ячейках содержат больше десятичных знаков (рис. 2). Excel суммирует не отображаемые значения, а фактические.

Рис. 2. Тот же счет-фактура с большим числом знаков после запятой

Чтобы значение в ячейке G7 равнялось сумме отображаемых значений в ячейках G4:G6, можно применить формулу массива, проводящую округление значений до двух десятичных знаков перед суммированием: <=СУММ(ОКРУГЛ(G4:G6;2))>(рис. 3). [1]

Читайте так же:
Установка PIP для Python и базовые команды

Рис. 3. «Правильное» суммирование с использованием формулы массива

Чуть подробнее, как работает эта формула. Excel формирует виртуальный массив (в памяти компьютера), состоящий из трех элементов: ОКРУГЛ(G4;2), ОКРУГЛ(G5;2), ОКРУГЛ(G6;2), то есть значений в ячейках G4:G6, округленных до двух десятичных знаков, а затем суммирует эти три элемента. Вуаля! &#128578;

Ошибки округления можно также исключить, применив функцию ОКРУГЛ в каждой из ячеек диапазона G4:G6. Этот прием не требует применения формулы массива, однако требует многократного использования функции ОКРУГЛ. Вам судить, что проще!

[1] Идея подсмотрена в книге Джона Уокенбаха «MS Excel 2007. Библия пользователя». Если вы не использовали ранее формулы массива, рекомендую начать с заметки Excel. Введение в формулы массива .

Комментарии: 42 комментария

Для полноты картины можно упомянуть еще об одном варианте, в параметрах Excel указать «точность как на экране» (Файл-Параметры-Дополнительно-При пересчете этой книги: задать точность как на экране)

Вот не рекомендуют программисты такой способ

Очень осторожно с этой приблудой. Точность_как_на_экране применяется ко всем листам книги и после сохранения вернуть прежние значения не получится.

Спасибо ! Очень помогло! сэкономило кучу времени

Спасибо за статью. «соответсвенно» лучше исправить

а как быть, если в документе производится большое количество вычислений, и числа «завязаны» друг за друга. использование формул ОКРУГЛ и т.п. немного неудобно.
как можно отключить округление отображаемого числа? как сделать, чтобы ексель показывал то что есть? пример — число 12345.6789, с точностью после запятой 2, он не округлял 12345.68, а отображал 12345.67, но значение оставалось 12345.6789?

Александр, если Вы хотите, чтобы Excel отражал с точностью до двух знаков после запятой, а хранил число с максимальной точностью, просто задайте форматирование «два знака после запятой», и никакие дополнительные формулы не потребуются. Но… именно против этого и направлена статья, так как в бухгалтерских расчетах не допускается расхождение между суммой и слагаемыми…

Как быть, если это не помогло. В формате ячейки ставлю «2 знака после запятой» затем вписываю в нее число 1950,4787, то автоматически записывается 1950,48, остальное отбрасывается вообще.

>>остальное отбрасывается вообще
Не отбрасывается, а не отображается в ячейке. Формат — лишь визуальное представление значения Выделите ячейку и посмотрите в строке формул — там то число, которое находится в ячейке

>>В формате ячейки ставлю «2 знака после запятой».
А кто Вам мешает поставить 4?

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

Возможные варианты:
– в столбце используются значения 2-х видов: чистые числа и числа с рублями,
23
56руб
12
Ответ =35

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

Как в EXCEL сложить числа в ячейках по определённому условию

Как в EXCEL сложить числа в ячейках по определённому условию

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

Читайте так же:
Как отменить отправленный подарок в одноклассниках

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

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

Как в EXCEL сложить числа в ячейках по определённому условию

Для примера возьмём категорию расходов «Покупки в магазинах». Нам надо, чтобы EXCEL находил все затраты по данной категории в детальной статистике, суммировал расходы по данной категории и записывал полученную сумму в ячейку D10.

Сначала запишем готовую формулу, которую вставляем в ячейку D10, а потом начнём разбираться в деталях. Готовая формула выглядит следующим образом (только для нашей статьи):

=СУММЕСЛИ ( $G$5:$G$300 ;(» Покупки в магазинах «); $H$5:$H$300 )

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

  • СУММЕСЛИ – этим условием мы говорим, что в ячейку надо записывать сумму значений определённых ячеек, если они соответствуют определённым условиям;
  • $G$5:$G$300 – здесь мы указываем EXCEL, в каком столбце нам надо искать условие для выборки. В нашем случае поиск происходит в столбце G начиная со строки 5 и заканчивая строкой 300;
  • (« Покупки в магазинах ») – здесь мы указываем искомое условие и по этому условию будут суммироваться значения ячеек, которые мы указываем далее…;
  • $H$5:$H$300 – здесь мы указываем столбец, из которого будут браться числа для суммирования. В нашем случае значения берутся в столбце H начиная со строки 5 и заканчивая строкой 300.

Подводя итог можно сказать, что EXCEL суммирует только те значения из диапазона H5:H300, для которых соответствующие значения из диапазона G5:G300 равны «Покупки в магазинах» и записывает результат в ячейку D10.

Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию.

Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $. К примеру, в ячейке D10 у нас вписана формула:

=СУММЕСЛИ(G5:G300;(«Покупки в магазинах»);H5:H300)

Далее мы хотим выводить сумму обедов в ячейке D11. Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11. Благодаря этому формула будет вставлена в D11, но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:

=СУММЕСЛИ(G6:G301;(«Покупки в магазинах»);H6:H301)

Произошло смещение. Если мы скопируем формулу в D12, то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $. Такие особенности EXCEL.

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