Как сделать чтобы в таблице excel складывались строчки?

Excel поможет считать быстрее калькулятора

Excel одна из самых известных программ Microsoft. Область применения Excel не ограничивается построением элементарных таблиц.

Сложение в Excel

Изучение программы следует начать с примера сложения. Рассмотрим, как можно складывать в excel:

  • Для этого возьмём простой пример сложения чисел 6 и 5.

Берётся ячейка и в неё записывается пример«=6+5»

  • После нажать клавишу Enter.
  • Сумма«11»,отразится в той же ячейке. А строка формул покажет само равенство«=6+5».

Второй способ как в excel сложить числа:

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

Нужно найти итоговое значение прибыли за 3 месяца за работы.

  • Следует выделить задуманные значения и нажать на значок ∑ автосумма в excel на вкладке ФОРМУЛЫ.

Результат отразится в пустой ячейке в столбце Итого.

Чтобы найти значение остальных данных, нужно копировать формулу«=СУММ(C2:E2)», и поставить её в остальные ячейки столбика Итого. Таким способом можно посчитать в экселе сумму столбца автоматически. Точно также, можно в excel посчитать сумму времени.

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

Работа с функциями Excel

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

Нужно подсчитать сумму ячеек где 1кг фруктов стоит меньше 100. Вручную этот процесс займёт время, ноExcel предлагает воспользоваться замечательной формулой–СУММЕСЛИМН. Что означает: найти сумму данных, если совпадает множество значений.

Задать алгоритм можно через вкладку Формулы, выбрать список математических функций и кликнуть мышкой по СУММЕСЛИМН. В ячейку нужно задать эту функцию и указать диапазон расчётов. Получим итоговое число 50. А в строке формул видим значение СУММЕСЛИМН (С2:С;В2:В5;« 90»).

  • Где,(В2:В5) столбец для проверки заданных критериев,«>90» условие отбора.
  • Может потребоваться изменить значения, и выбрать дополнительные критерии. Например:В той же таблице требуется посчитать наименование фруктов со стоимостью больше 90 и определить их количество на складе по весу менее 20 кг.

    После выборки формула изменится. Она примет вид«=СЧЁТЕСЛИМН (В2:В6;«>90»;С2:С6;«

    А2–значение которое нужно заменить, $D$2:$E$5 –означает полное выделение таблицы, «$»—знак, который ограничивает копирование не нужной информации во втором столбике D2:Е5.

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

    Вертикальный просмотр ведётся только в первой таблице по всем позициям столбика. В этом случае, поиск слова «яблоки» пройдёт по столбику D.

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

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

    1. СУММ–используют для нахождения суммарного значения столбцов и ячеек
    2. ЕСЛИ–используют для выявления сравнения нескольких показателей. Определяется значением Больше и Меньше.
    3. ПРОСМОТР–используют для нахождения нужного значения или для выборки определенного столбца.
    4. ДАТА–используется для возвращения числа дней между задуманными датами
    5. ВПР–используют как вертикальный просмотр в поиске значений.
    6. СЦЕПИТЬ–используют для соединения нескольких столбцов в один.
    7. РУБЛЬ–используют для преобразования числа в текст, используя денежный эквивалент рубль.
    8. СОВПАД–используют вовремя нахождения одинаковых текстовых значений
    9. СТРОЧН–используют для преобразования всех заглавных букв в строчные
    10. ПОВТОР–используют при повторе текста нужное число раз.

    Статья содержит основные этапы работы с таблицами в excel. Рассмотрены методы сложения и выборки по заданным критериям.

    Развертывание, свертывание и отображение сведений в сводной таблице или сводной диаграмме

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

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

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

    Читать еще:  Как сделать группировку в сводной таблице excel?

    Развертывание и свертывание уровней в сводной таблице

    В сводной таблице выполните одно из указанных ниже действий.

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

    Примечание: Если кнопки развертывания и свертывания не отображаются, см. раздел Отображение и скрытие кнопок развертывания и свертывания в сводной таблице в этой статье.

    Дважды щелкните элемент, который нужно развернуть или свернуть.

    Щелкните правой кнопкой мыши элемент, выберите команду Развернуть/свернуть и выполните одно из следующих действий.

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

    Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.

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

    Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.

    Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до » «.

    Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до » «.

    Развертывание и свертывание уровней в сводной диаграмме

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

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

    Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.

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

    Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.

    Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до » «.

    Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до » «.

    Отображение и скрытие кнопок развертывания и свертывания в сводной таблице

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

    В Excel 2016 и Excel 2013: на вкладке Анализ в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.

    В Excel 2010: на вкладке Параметры в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.

    В Excel 2007: на вкладке Параметры в группе Показать или скрыть щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.

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

    Отображение и скрытие сведений для поля значений в отчете сводной таблицы

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

    Отображение сведений поля значений

    В сводной таблице выполните одно из указанных ниже действий.

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

    Дважды щелкните поле в области значений сводной таблицы.

    Данные, на которых основано поле значений, будут помещены на новый лист.

    Скрытие сведений поля значений

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

    Отключение и включение параметра отображения сведений поля значений

    Щелкните в любом месте сводной таблицы.

    На вкладке Параметры или Анализ (в зависимости от используемой версии Excel) ленты в группе Сводная таблица нажмите кнопку Параметры.

    В диалоговом окне Параметры сводной таблицы откройте вкладку Данные.

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

    Примечание: Этот параметр недоступен для источника данных OLAP.

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

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

    Постановка задачи

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

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

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

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

    Читать еще:  Как сделать таблицу в excel ютуб?

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

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

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

    Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF) , которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.

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

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

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

    • Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
    • Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
    • Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
    • И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
    • Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.

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

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

    И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:

    Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.

    Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.

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

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

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

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

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

    Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.

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

    Постановка задачи

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

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

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

    Читать еще:  Как сделать чтобы excel открывал xlsx файлы?

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

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

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

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

    Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF) , которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.

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

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

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

    • Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
    • Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
    • Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
    • И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
    • Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.

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

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

    И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:

    Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.

    Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.

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

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

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

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

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

    Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.

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