КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет Вклады Сбербанк

Использование функции бс() в случае накопления вклада

Функция БС(ставка; кпер; плт; [пс]; [тип])

возвращает

будущую стоимость инвестиции

на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки. Например, если у Вас сейчас на банковском счете сумма ПС и вы ежемесячно дополнительно вносите одну и туже сумму ПЛТ, то функция вычислит сумму на Вашем банковском счете через Кпер месяцев. Теперь несколько замечаний:

  1. Предполагается, что капитализация процентов происходит также периодически с процентной ставкой равной величине СТАВКА;
  2. Процентная ставка указывается за период (если период равен месяцу, а задана годовая ставка =10%, то СТАВКА =10%/12);
  3. По умолчанию аргумент Тип=0, т.е. пополняющие счет вклады делаются в конце каждого периода. Если Тип=1, то пополняющие счет вклады делаются в начале каждого периода;
  4. Начальная сумма вклада ПС м.б. =0, но тогда суммы дополнительных взносов ПЛТ не должны быть  =0;
  5. Суммы дополнительных взносов м.б. =0, но тогда Начальная сумма вклада ПС не должна быть =0.

Примечание

.

Английский вариант функции: FV(rate, nper, pmt, [pv], [type]), т.е. Future Value – Будущая Стоимость.

Расчеты в

БС()

производятся по этой формуле:

Из формулы видно, будущая стоимость состоит из 2-х составляющих: будущая стоимость инвестиции ПС (вычисляется по формуле

сложных процентов

) и будущая стоимость периодических равновеликих взносов ПЛТ (вычисляется по формуле

аннуитета

).

Примечание

. При БС=0 (начальная инвестиция =0) Будущая стоимость не зависит от параметра Тип.

Вычислим Будущую стоимость в случае накопления вклада. Исходные данные приведены на рисунке ниже.

В результате расчетов получим следующий график накопления вклада (см.

файл примера Лист Накопление

).

Примечание

. Функцию

БС()

можно также использовать для вычисления баланса на конец периода (см.

файл примера Лист Накопление, столбец G

). Для этого используйте выражение = БС(ставка; кпер; плт; [пс]; [тип])/ (1 ставка*тип)

Примечание

. При Тип=1 (начисление процентов в начале периода), баланс на конец последнего периода не равен БС (как при Тип=0), т.к. учитывается начисление процентов на следующий день после окончания последнего периода! Т.е. к балансу на конец последнего периода прибавляется величина =БС(ставка; кпер; плт; [пс]; [тип])*ставка

Как посчитать накопительную сумму в excel?

На рисунке ниже Вы можете видеть таблицу Excel, в которой представлен объем продаж по дням за январь месяц 2022 года. В ячейке B20 отображается общая сумма продаж за весь месяц, вычисленная с помощью функции СУММ. Требуется посчитать объем продаж на каждую дату января относительно начала месяца, т.е. накопительную сумму.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

При подсчете накопительной суммы в Excel, необходимо, чтобы суммирование всегда начиналось с третьей строки (ячейки B3) и заканчивалось рассматриваемой строкой. Например, ячейка C3 должна содержать следующую формулу: =СУММ(B3), а если говорить точнее, то =СУММ(B3:B3), т.е. суммирование диапазона, состоящего из одной ячейки.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Ячейка C4 такую формулу: =СУММ(B3:B4)

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Ячейка C19 такую формулу:=СУММ(B3:B19)

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Поскольку значения в ячейках B20 и C19 совпадают, то можно быть уверенным, что накопительная сумма в Excel подсчитана верно. Как видите, все просто!

ВЫЧИСЛЯЕМ СРЕДНЕВЗВЕШЕННЫЕ ЗНАЧЕНИЯ ПРИ ПОМОЩИ СУММПРОИЗВ

Excel превратил вычисление среднего арифметического нескольких ячеек в очень простую задачу – просто используйте функцию СРЗНАЧ (AVERAGE). Но что делать, если некоторые значения имеют больший вес, чем другие? Например, на многих курсах тесты имеют больший вес, чем задания. Для таких случаев необходимо рассчитывать среднее взвешенное.

В Excel нет функции для расчёта средневзвешенного значения, зато есть функция, которая сделает за Вас большую часть работы: СУММПРОИЗВ (SUMPRODUCT). И даже, если Вы никогда не использовали эту функцию раньше, то к концу этой статьи будете работать с ней как профи. Метод, который мы используем, работает в любой версии Excel, а также в других электронных таблицах, таких как Google Sheets.

ПОДГОТАВЛИВАЕМ ТАБЛИЦУ

Если Вы собираетесь вычислять среднее взвешенное, Вам потребуется минимум два столбца. Первый столбец (в нашем примере – столбец B) содержит оценки для каждого задания или теста. Второй столбец (столбец C) содержит веса. Больший вес означает большее влияние задания или теста на итоговую оценку.

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ВВОДИМ ФОРМУЛУ

Теперь, когда наша таблица готова, мы добавляем формулу в ячейку B10 (подойдёт любая пустая ячейка). Как и с любой другой формулой в Excel, начинаем со знака равенства (=).

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

=СУММПРОИЗВ(
=SUMPRODUCT(

Далее, добавляем аргументы функции. СУММПРОИЗВ (SUMPRODUCT) может иметь несколько аргументов, но обычно используют два. В нашем примере, первым аргументом будет диапазон ячеек B2:B9, который содержит оценки.

=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9

Вторым аргументом будет диапазон ячеек C2:C9, в котором содержатся веса. Между этими аргументами должен стоять разделитель точка с запятой (запятая). Когда все будет готово, закрываем скобки:

=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)

Теперь добавим вторую часть нашей формулы, которая поделит результат вычисляемый функцией СУММПРОИЗВ(SUMPRODUCT) на сумму весов. Позже мы обсудим, почему это важно.

Чтобы выполнить операцию деления, продолжаем уже введённую формулу символом / (прямой слеш), а далее записываем функцию СУММ (SUM):

=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(

Для функции SUM (СУММ) мы укажем только один аргумент – диапазон ячеек C2:C9. Не забудьте после ввода аргумента закрыть скобки:

=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

Готово! После нажатия клавиши Enter, Excel рассчитает среднее взвешенное значение. В нашем примере итоговый результат будет равен 83,6.

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

Давайте разберем каждую часть формулы, начиная с функции СУММПРОИЗВ (SUMPRODUCT), чтобы понять, как она работает. Функция СУММПРОИЗВ (SUMPRODUCT) вычисляет произведение оценки каждого задания на его вес, а затем суммирует все полученные произведения. Другими словами, функция находит сумму произведений (sum of the products), отсюда она и получила своё название. Итак, для Задания 1 умножаем 85 на 5, а для Теста умножаем 83 на 25.

Если Вас удивляет, зачем перемножать значения в первой части, представьте, что чем больше вес у задания, тем большее число раз мы должны учитывать оценку за него. Например, Задание 2 посчитано 5 раз, а Итоговый экзамен– 45 раз. Вот почему Итоговый экзамен имеет большее влияние на итоговую оценку.

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

Если бы Вы могли заглянуть под капот функции СУММПРОИЗВ (SUMPRODUCT), то увидели, что на самом деле она считает вот что:

=(B2*C2) (B3*C3) (B4*C4) (B5*C5) (B6*C6) (B7*C7) (B8*C8) (B9*C9)

К счастью, нам не нужно писать такую длинную формулу, поскольку СУММПРОИЗВ (SUMPRODUCT) делает всё это автоматически.

Сама по себе функция СУММПРОИЗВ (SUMPRODUCT) возвращает нам огромное число – 10450. В этот момент включается вторая часть формулы: /СУММ(C2:C9) или /SUM(C2:C9), которая возвращает результат в нормальный диапазон оценок, давая ответ 83,6.

Вторая часть формулы очень важна, т.к. позволяет автоматически корректировать вычисления. Помните, что веса не обязаны складываться в сумму 100%? Все это благодаря второй части формулы. Например, если мы увеличиваем одно или несколько значений весов, вторая часть формулы просто выполнит деление на большее значение, вновь приводя к правильному ответу. Или же мы можем сделать веса намного меньше, например, указать такие значения как 0,5, 2,5, 3или 4,5, и формула по-прежнему будет работать правильно. Здорово, правда?

§

Весь курс: урок 1 | урок 2 | урок 3 | урок 4 | урок 5

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

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

ПРЕДСТАВЛЕНИЕ ДАТЫ В EXCEL

В Microsoft Excel дата представлена порядковым числом, которое представляет собой количество дней, прошедших с 0 января 1900 года. Эта несуществующая дата вполне условна и используется в Excel лишь как точка отсчета. Порядковое число 1 соответствует 1 января 1900 года, число 2 – 2 января 1900 года и т.д. Максимальная дата, которую поддерживает Excel – это 31 декабря 9999 года, что соответствует порядковому числу 2958465.

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

На рисунке ниже в ячейках B2 и B3 представлены две даты, а в ячейках C2 и C3 те же самые даты представлены в формате Числовой. Думаю, Вы уже догадались, что это их порядковые номера.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Чтобы посчитать количество дней между двумя датами, достаточно из конечной даты вычесть начальную, т.е. воспользоваться формулой: =B3-B2. Введя эту формулу в ячейку B4, получим следующий результат:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Отметим, что к ячейке B4 был применен формат отличный от Даты, поэтому значение представлено в днях. В противном случае, мы получили бы другую картину:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

ПРЕДСТАВЛЕНИЕ ВРЕМЕНИ В EXCEL

Время в Excel трактуется как дробная часть дня. Если говорить другими словами, то Excel принимает сутки за единицу, а все величины меньше суток (часы, минуты, секунды) – рассматривает как часть единицы. Например, 1 час – это 1/24 дня, 1 минута – это 1/1140 дня, а 1 секунда – это 1/86400 дня. Самая маленькая единица измерения времени в Microsoft Excel – одна тысячная доля секунды, т.е. 1 миллисекунда.

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

На рисунке ниже в столбце B представлены значения времени в формате Время, а в столбце C соответствующие им доли суток в формате Числовой.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если в ячейку B4 ввести формулу =B3-B2 и нажать Enter, то получим следующий результат:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Поскольку ячейка B4 изначально имела формат Общий, то после ввода формулы Excel автоматически поменял форматОбщий на Время. Если же применить к данной ячейке формат Числовой, то результат будет таким:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

В этом уроке Вы узнали о том, как Microsoft Excel хранит и обрабатывает временные данные. Как видите, в этом есть своя специфика и огромное преимущество, которое позволяет Excel так легко и просто совершать с датами и временем различные математические действия. В следующем уроке Вы узнаете, как вводить и форматировать даты и время в Excel. Всего Вам доброго и успехов в изучении Excel!

ВВОД И ФОРМАТИРОВАНИЕ ДАТ И ВРЕМЕНИ В EXCEL

Весь курс: урок 1 | урок 2 | урок 3 | урок 4 | урок 5

Думаю, из прошлого урока Вам уже известно, что даты и время в Excel хранятся в виде порядковых чисел, началом отсчета которых принято считать некое 0 января 1900 года. К счастью, в ячейках мы видим не эти числа, а привычные для нас даты и время, которые могут храниться в самых различных форматах. В этом уроке Вы узнаете, как вводить даты и время в Excel, чтобы получать требуемое форматирование.

ВВОД ДАТ И ВРЕМЕНИ В EXCEL

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

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

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Это не все возможные варианты, которые поддерживает Excel. Но даже этих вариантов Вам будет предостаточно.

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

ФОРМАТ ДАТЫ И ВРЕМЕНИ В EXCEL

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

Получить доступ ко всем возможным форматам Вы можете на вкладке Число диалогового окна Формат ячеек. В категории Дата приведены встроенные форматы дат:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

А в категории Время – встроенные форматы времени:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Откройте диалоговое окно Формат ячеек и перейдите на вкладку Число.

3. В категории Все форматы в поле Тип введите необходимый код числового формата. В нашем случае мы введем: ДДДД ДД.ММ.СС чч:мм:cc и нажмем ОК.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Как видите все достаточно просто!

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

§

Весь курс: урок 1 | урок 2 | урок 3 | урок 4 | урок 5

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

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

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

ГОД()

Возвращает значение года, соответствующее заданной дате. Год может быть целым числом в диапазоне от 1900 до 9999.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В ячейке A1 представлена дата в формате ДДДД ДД.ММ.ГГГГ чч:мм:cc. Это пользовательский (нестандартный) числовой формат представления даты и времени в Excel.

В качестве примера использования функции ГОД, можно привести формулу, которая вычисляет количество лет между двумя датами:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

МЕСЯЦ()

Возвращает номер месяца, соответствующий заданной дате. Месяц возвращается как целое число в диапазоне от 1 до 12.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ДЕНЬ()

Возвращает значение дня, соответствующее заданной дате. День возвращается как целое число в диапазоне от 1 до 31.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ЧАС()

Данная функция возвращает значение часа, соответствующее заданному времени. Час возвращается как целое число в диапазоне от 0 до 23.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

МИНУТЫ()

Возвращает количество минут, соответствующее заданному времени. Минуты возвращаются как целое число в диапазоне от 0 до 59.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

СЕКУНДЫ()

Возвращает количество секунд, соответствующее заданному времени. Секунды возвращаются как целое число в диапазоне от 0 до 59.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ДЕНЬНЕД()

Данная функция возвращает номер дня недели, соответствующий заданной дате. Номер дня недели по умолчанию возвращается как целое число в диапазоне от 1 (воскресенье) до 7 (суббота).

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если в качестве второго аргумента функции ДЕНЬНЕД подставить 2, то дням недели будут соответствовать номера от 1 (понедельник) до 7 (воскресенье). В итоге функция вернет нам значение 6:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

§

Весь курс: урок 1 | урок 2 | урок 3 | урок 4 | урок 5

Мы продолжаем изучать функции категории Дата и время в Excel. Если Вы не читали наш прошлый урок по функциям, советуем к нему обратиться. В этом уроке мы познакомимся с 4-мя очень полезными функциями, которые позволяют не просто отображать, но и создавать даты и время в Excel.

СЕГОДНЯ()

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ТДАТА()

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Результаты функций СЕГОДНЯ() и ТДАТА изменяются при каждом пересчете рабочего листа Excel.

В качестве примера можно привести формулу, которая вычисляет текущее время в Excel:

=ТДАТА()-СЕГОДНЯ() – в данном случае формула вернет десятичное число (долю суток). Чтобы увидеть результат, как на рисунке ниже, необходимо к ячейке A1 применить формат времени.

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ДАТА()

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

А чтобы преобразовать текстовую строку в дату, воспользуемся этой формулой:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ВРЕМЯ()

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Функция ВРЕМЯ очень похожа на ДАТА, поэтому заострять на ней особого внимания мы не будем.

Важно отметить, что функция ВРЕМЯ не может возвращать значение, превышающее 23:59:59. При достижении этого значения функция обнуляется.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Функции ДАТА и ВРЕМЯ можно использовать совместно:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В данном примере к ячейке D1 применен формат даты и времени.

§

Весь курс: урок 1 | урок 2 | урок 3 | урок 4 | урок 5

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

ДАТАМЕС()

Возвращает порядковый номер даты, которая отстает от заданной на определенное количество месяцев вперед или назад. ДАТАМЕС имеет всего два аргумента: начальная дата и число месяцев. Если второй аргумент (число месяцев) является положительным числом, то функция вернет будущую дату относительно начальной, а если отрицательным, – то прошедшую.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Как видите, функция ДАТАМЕС возвратила порядковые номера дат, при этом никакой автоматической смены числового формата не произошло. Чтобы увидеть сами даты, примените к ячейкам C2 и C3 формат Дата:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

КОНМЕСЯЦА()

Возвращает порядковый номер последнего дня месяца, который отстает от заданной даты на определенное количество месяцев вперед или назад. В плане синтаксиса, функция КОНМЕСЯЦА очень похожа на ДАТАМЕС и имеет точно такие же аргументы:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Чтобы увидеть сами даты, примените к ячейкам C2 и C3 формат Дата:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

РАБДЕНЬ()

Возвращает порядковый номер даты, которая отстает от заданной на определенное количество рабочих дней вперед или назад. РАБДЕНЬ имеет два обязательных аргумента: начальная дата и количество рабочих дней. Если второй аргумент (количество рабочих дней) является положительным числом, то функция вернет будущую дату относительно заданной, а если отрицательным – то прошедшую.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Чтобы увидеть сами даты, примените к ячейкам C2 и C3 формат Дата:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Стоит отметить, что нерабочими днями считаются выходные дни и дни, определенные как праздничные. Для функцииРАБДЕНЬ выходными считаются суббота и воскресенье. Программа Excel самостоятельно не может определить, какие дни считать праздниками, поэтому предлагает третий необязательный аргумент, где Вы сами можете все указать.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если в Вашем случае выходные дни отличны от классических субботы и воскресенья, то вы всегда можете воспользоваться функцией РАБДЕНЬ.МЕЖД. По сути это расширенный вариант функции РАБДЕНЬ, который позволяет настроить количество выходных в неделе и на какие дни они приходятся. Функция РАБДЕНЬ.МЕЖДвпервые появилась в Excel 2022.

ЧИСТРАБДНИ()

Возвращает количество рабочих дней между двумя датами. Праздничные и выходные дни в это количество не включаются. Функция ЧИСТРАБДНИ является как бы противоположностью функции РАБДЕНЬ, поэтому лишний раз описывать назначение аргументов функции не стоит.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Как видите, результатом вычисления функции оказались 63 рабочих дня. Если бы мы просто вычли из конечной даты начальную, то получили бы 89 дней.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если в Вашем случае выходные дни отличны от классических субботы и воскресенья, то Вы всегда можете воспользоваться функцией ЧИСТРАБДНИ.МЕЖД. По сути это расширенный вариант функции ЧИСТРАБДНИ, который позволяет настроить количество выходных в неделе и на какие дни они приходятся. ФункцияЧИСТРАБДНИ.МЕЖД впервые появилась в Excel 2022.

§

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

Функция ВПР (вертикальный просмотр) ищет значение в крайнем левом столбце исследуемого диапазона, а затем возвращает результат из ячейки, которая находится на пересечении найденной строки и заданного столбца.

ПРИМЕР 1

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

С помощью функции ВПР сделать это достаточно просто:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Из формулы видно, что первым аргументом функции ВПР является ячейка С1, где мы указываем искомый номер. Вторым выступает диапазон A1:B10, который показывает, где следует искать. И последний аргумент – это номер столбца, из которого необходимо возвратить результат. В нашем примере это второй столбец. Нажав Enter, мы получим нужный результат:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ПРИМЕР 2

Рассмотрим еще один пример. На рисунке ниже представлены те же 10 фамилий, что и раньше, вот только номера идут с пропусками.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Дело в том, что функция ВПР имеет еще и четвертый аргумент, который позволяет задавать так называемый интервальный просмотр. Он может иметь два значения: ИСТИНА и ЛОЖЬ. Причем, если аргумент опущен, то это равносильно истине.

В случае, когда четвертый аргумент имеет значение ИСТИНА, функция сначала ищет точное соответствие, а если такого нет, то ближайшее, которое меньше чем заданное. Именно поэтому функция ВПР возвратила фамилию “Панченко”. Если бы мы задали “008”, то формула также вернула бы “Панченко”.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если четвертый аргумент функции ВПР содержит значение ИСТИНА или опущен, то крайний левый столбец должен быть отсортирован в порядке возрастания. Если этого не сделать, функция ВПР может вернуть неправильный результат.

Для тех, кто любит создавать не вертикальные, а горизонтальные таблицы, в Excel существует аналог ВПР, но для горизонтального поиска.

ГОРИЗОНТАЛЬНЫЙ ВПР В EXCEL

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

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Как видите, все достаточно просто!

На этом наш урок завершен. Сегодня мы познакомились, наверное, с самым популярным инструментом Microsoft Excel –функцией ВПР и разобрали ее возможности на нескольких простых примерах. Надеюсь, что этот урок был для Вас полезным. Всего Вам доброго и успехов в изучении Excel.

§

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

ВАРИАНТЫ ЗАПИСИ ФУНКЦИИ ПРОСМОТР

Начнем с того, что функция ПРОСМОТР имеет две формы записи: векторная и массив. Вводя функцию на рабочий лист, Excel напоминает Вам об этом следующим образом:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ФОРМА МАССИВА

Форма массива очень похожа на функции ВПР и ГПР. Основная разница в том, что ГПР ищет значение в первой строке диапазона, ВПР в первом столбце, а функция ПРОСМОТР либо в первом столбце, либо в первой строке, в зависимости от размерности массива. Есть и другие отличия, но они менее существенны.

Данную форму записи мы подробно разбирать не будем, поскольку она давно устарела и оставлена в Excel только для совместимости с ранними версиями программы. Вместо нее рекомендуется использовать функции ВПР илиГПР.

ВЕКТОРНАЯ ФОРМА

Функция ПРОСМОТР (в векторной форме) просматривает диапазон, который состоит из одной строки или одного столбца. Находит в нем заданное значение и возвращает результат из соответствующей ячейки второго диапазона, который также состоит из одной строки или столбца.

Вот это да! Это ж надо такое понаписать… Чтобы стало понятней, рассмотрим небольшой пример.

ПРИМЕР 1

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Первым аргументом функции ПРОСМОТР является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является просматриваемым, его еще называют просматриваемый вектор. Из соответствующей ячейки диапазона A1:A7 функция ПРОСМОТР возвращает результат, такой диапазон также называют вектором результатов. Нажав Enter, убеждаемся, что все верно.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ПРИМЕР 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат.

ФУНКЦИИ ИНДЕКС И ПОИСКПОЗ В EXCEL НА ПРОСТЫХ ПРИМЕРАХ

Совместное использование функций ИНДЕКС и ПОИСКПОЗ в Excel – хорошая альтернатива ВПР, ГПР и ПРОСМОТР. Эта связка универсальна и обладает всеми возможностями этих функций. А в некоторых случаях, например, при двумерном поиске данных на листе, окажется просто незаменимой. В данном уроке мы последовательно разберем функции ПОИСКПОЗ и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.

Более подробно о функциях ВПР и ПРОСМОТР.

ФУНКЦИЯ ПОИСКПОЗ В EXCEL

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:

· – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

· 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

· -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.

ФУНКЦИЯ ИНДЕКС В EXCEL

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функцииИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

СОВМЕСТНОЕ ИСПОЛЬЗОВАНИЕ ПОИСКПОЗ И ИНДЕКС В EXCEL

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

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функцииПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функцииПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

§

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

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

СРЗНАЧ()

Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Данная функция может принимать до 255 аргументов и находить среднее сразу в нескольких несмежных диапазонах и ячейках:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4 15 11 22)/4 = 13

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4 15 11 22)/6 = 8,6(6).

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

СРЗНАЧЕСЛИ()

Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ. Следующая формула вычисляет среднее чисел, которые больше нуля:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее.

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

МАКС()

Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

МИН()

Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

НАИБОЛЬШИЙ()

Возвращает n-ое по величине значение из массива числовых данных. Например, на рисунке ниже мы нашли пятое по величине значение из списка.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Чтобы убедиться в этом, можно отсортировать числа в порядке возрастания:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

НАИМЕНЬШИЙ()

Возвращает n-ое наименьшее значение из массива числовых данных. Например, на рисунке ниже мы нашли четвертое наименьшее значение из списка.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если отсортировать числа в порядке возрастания, то все станет гораздо очевидней:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

МЕДИАНА()

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

Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если отсортировать значения в порядке возрастания, то все становится на много понятней:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

МОДА()

Возвращает наиболее часто встречающееся значение в массиве числовых данных.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости.

§

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Про математические функции СУММ и СУММЕСЛИ Вы можете прочитать в этом уроке.

ОКРУГЛ()

Математическая функция ОКРУГЛ позволяет округлять значение до требуемого количества десятичных знаков. Количество десятичных знаков Вы можете указать во втором аргументе. На рисунке ниже формула округляет значение до одного десятичного знака:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Такое число как 231,5 функция ОКРУГЛ округляет в сторону удаления от нуля:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если необходимо округлить число в сторону большего или меньшего по модулю значения, можно воспользоваться функциями ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ПРОИЗВЕД()

Математическая функция ПРОИЗВЕД вычисляет произведение всех своих аргументов.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Мы не будем подробно разбирать данную функцию, поскольку она очень похожа на функцию СУММ, разница лишь в назначении, одна суммирует, вторая перемножает. Более подробно о СУММ Вы можете прочитать в статьеСуммирование в Excel, используя функции СУММ и СУММЕСЛИ.

Читайте также:  «Вклад в будущее» банка ВТБ в марте 2022: проценты выгодные или нет

ABS()

Математическая функция ABS возвращает абсолютную величину числа, т.е. его модуль.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

На рисунке ниже в столбцах A и B представлены даты, причем какая из них начальная, а какая конечная неизвестно. Требуется посчитать количество дней между этими датами. Если просто вычесть из одной даты другую, то количество дней может оказаться отрицательным, что не совсем правильно:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Чтобы избежать этого, воспользуемся функцией ABS:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Нажав Enter, получим правильное количество дней:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

КОРЕНЬ()

Возвращает квадратный корень из числа. Число должно быть неотрицательным.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Извлечь квадратный корень в Excel можно и с помощью оператора возведения в степень:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

СТЕПЕНЬ()

Позволяет возвести число в заданную степень.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В Excel, помимо этой математической функции, можно использовать оператор возведения в степень:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

СЛУЧМЕЖДУ()

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ОБЗОР ОШИБОК, ВОЗНИКАЮЩИХ В ФОРМУЛАХ EXCEL

Ошибки в Excel возникают довольно часто. Вы, наверняка, замечали странные значения в ячейках, вместо ожидаемого результата, которые начинались со знака #. Это говорит о том, что формула возвращает ошибку. Чтобы избавиться от ошибки, Вы должны исправить ее причину, а они могут быть самыми разными.

НЕСООТВЕТСТВИЕ ОТКРЫВАЮЩИХ И ЗАКРЫВАЮЩИХ СКОБОК

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Например, на рисунке выше мы намеренно пропустили закрывающую скобку при вводе формулы. Если нажать клавишуEnter, Excel выдаст следующее предупреждение:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ЯЧЕЙКА ЗАПОЛНЕНА ЗНАКАМИ РЕШЕТКИ

Бывают случаи, когда ячейка в Excel полностью заполнена знаками решетки. Это означает один из двух вариантов:

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

…или изменить числовой формат ячейки.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В данном случае увеличение ширины столбца уже не поможет.

ОШИБКА #ДЕЛ/0!

Ошибка #ДЕЛ/0! возникает, когда в Excel происходит деление на ноль. Это может быть, как явное деление на ноль, так и деление на ячейку, которая содержит ноль или пуста.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ОШИБКА #Н/Д

Ошибка #Н/Д возникает, когда для формулы или функции недоступно какое-то значение. Приведем несколько случаев возникновения ошибки #Н/Д:

1. Функция поиска не находит соответствия. К примеру, функция ВПР при точном поиске вернет ошибку #Н/Д, если соответствий не найдено.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Формула прямо или косвенно обращается к ячейке, в которой отображается значение #Н/Д.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. При работе с массивами в Excel, когда аргументы массива имеют меньший размер, чем результирующий массив. В этом случае в незадействованных ячейках итогового массива отобразятся значения #Н/Д.

Например, на рисунке ниже видно, что результирующий массив C4:C11 больше, чем аргументы массива A4:A8 и B4:B8.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Нажав комбинацию клавиш Ctrl Shift Enter, получим следующий результат:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ОШИБКА #ИМЯ?

Ошибка #ИМЯ? возникает, когда в формуле присутствует имя, которое Excel не понимает.

1. Например, используется текст не заключенный в двойные кавычки:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Функция ссылается на имя диапазона, которое не существует или написано с опечаткой:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В данном примере имя диапазон не определено.

3. Адрес указан без разделяющего двоеточия:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

4. В имени функции допущена опечатка:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ОШИБКА #ПУСТО!

Ошибка #ПУСТО! возникает, когда задано пересечение двух диапазонов, не имеющих общих точек.

1. Например, =А1:А10 C5:E5 – это формула, использующая оператор пересечения, которая должна вернуть значение ячейки, находящейся на пересечении двух диапазонов. Поскольку диапазоны не имеют точек пересечения, формула вернет #ПУСТО!.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Также данная ошибка возникнет, если случайно опустить один из операторов в формуле. К примеру, формулу=А1*А2*А3 записать как =А1*А2 A3.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ОШИБКА #ЧИСЛО!

Ошибка #ЧИСЛО! возникает, когда проблема в формуле связана со значением.

1. Например, задано отрицательное значение там, где должно быть положительное. Яркий пример – квадратный корень из отрицательного числа.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. К тому же, ошибка #ЧИСЛО! возникает, когда возвращается слишком большое или слишком малое значение. Например, формула =1000^1000 вернет как раз эту ошибку.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Не забывайте, что Excel поддерживает числовые величины от -1Е-307 до 1Е 307.

3. Еще одним случаем возникновения ошибки #ЧИСЛО! является употребление функции, которая при вычислении использует метод итераций и не может вычислить результат. Ярким примером таких функций в Excel являютсяСТАВКА и ВСД.

ОШИБКА #ССЫЛКА!

Ошибка #ССЫЛКА! возникает в Excel, когда формула ссылается на ячейку, которая не существует или удалена.

1. Например, на рисунке ниже представлена формула, которая суммирует значения двух ячеек.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если удалить столбец B, формула вернет ошибку #ССЫЛКА!.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Еще пример. Формула в ячейке B2 ссылается на ячейку B1, т.е. на ячейку, расположенную выше на 1 строку.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если мы скопируем данную формулу в любую ячейку 1-й строки (например, ячейку D1), формула вернет ошибку#ССЫЛКА!, т.к. в ней будет присутствовать ссылка на несуществующую ячейку.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ОШИБКА #ЗНАЧ!

Ошибка #ЗНАЧ! одна из самых распространенных ошибок, встречающихся в Excel. Она возникает, когда значение одного из аргументов формулы или функции содержит недопустимые значения. Самые распространенные случаи возникновения ошибки #ЗНАЧ!:

1. Формула пытается применить стандартные математические операторы к тексту.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Аргумент функции должен иметь единственное значение, а вместо этого ему присваивают целый диапазон. На рисунке ниже в качестве искомого значения функции ВПР используется диапазон A6:A8.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ЗНАКОМСТВО С ИМЕНАМИ ЯЧЕЕК И ДИАПАЗОНОВ В EXCEL

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

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Такая формула будет вычислять правильный результат, но аргументы, используемые в ней, не совсем очевидны. Чтобы формула стала более понятной, необходимо назначить областям, содержащим данные, описательные имена. Например, назначим диапазону B2:В13 имя Продажи_по_месяцам, а ячейке В4 имя Комиссионные. Теперь нашу формулу можно записать в следующем виде:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

ПРОСТОЙ СПОСОБ ВЫДЕЛИТЬ ИМЕНОВАННЫЙ ДИАПАЗОН В EXCEL

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Диапазон будет выделен:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

КАК ВСТАВИТЬ ИМЯ ЯЧЕЙКИ ИЛИ ДИАПАЗОНА В ФОРМУЛУ

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

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Вставьте имя из раскрывающегося списка Использовать в формуле, который находится на вкладке Формулы.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

КАК ПРИСВОИТЬ ИМЯ ЯЧЕЙКЕ ИЛИ ДИАПАЗОНУ В EXCEL

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

ИСПОЛЬЗУЕМ ПОЛЕ ИМЯ

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

1. Выделите ячейку или диапазон, которым необходимо присвоить имя. В нашем случае это диапазон B2:B13.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Щелкните по полю Имя и введите необходимое имя, соблюдая правила, рассмотренные здесь. Пусть это будет имя Продажи_по_месяцам.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Нажмите клавишу Enter, и имя будет создано.

4. Если нажать на раскрывающийся список поля Имя, Вы сможете увидеть все имена, созданные в данной рабочей книге Excel. В нашем случае это всего лишь одно имя, которое мы только что создали.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

6. Как видите, если ячейке или диапазону, на которые ссылается формула, дать осмысленные имена, то формула станет гораздо понятнее.

ИСПОЛЬЗУЕМ ДИАЛОГОВОЕ ОКНО СОЗДАНИЕ ИМЕНИ

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

1. Выделите требуемую область (на данном этапе можно выделить любую область, в дальнейшем вы сможете ее перезадать). Мы выделим ячейку С3, а затем ее перезададим.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Откроется диалоговое окно Создание имени.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

4. В поле Имя введите требуемое имя. В нашем случае это имя Коэффициент. В ряде случаев Excel автоматически подставляет имя на основе данных в соседних ячейках. В нашем случае так и произошло. Если Excel этого не сделал или такое имя Вас не устраивает, введите требуемое Вам имя самостоятельно.

5. В раскрывающемся списке Область Вы можете указать область видимости создаваемого имени. Область видимости – это область, где вы сможете использовать созданное имя. Если вы укажете Книга, то сможете пользоваться именем по всей книге Excel (на всех листах), а если конкретный лист – то только в рамках данного листа. Как правило выбирают область видимости – Книга.

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

8. Если Вас все устраивает, смело жмите ОК. Имя будет создано.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

5 ПОЛЕЗНЫХ ПРАВИЛ И РЕКОМЕНДАЦИЙ ПО СОЗДАНИЮ ИМЕН ЯЧЕЕК И ДИАПАЗОНОВ В EXCEL

Грамотное использование имен ячеек и диапазонов в формулах Microsoft Excel значительно облегчает работу с ними. Однако, если задаваемые имена слишком сложные или малоинформативные, то вся польза и преимущества от их применения, может не оправдать себя. Присваивая имена ячейкам и диапазонам в Excel старайтесь придерживаться следующих 5 правил:

1. Создавайте максимально короткие имена, но при этом они должны быть запоминающимися и содержательными, т.е. отражать суть. Максимальная длина имени в Excel не должна превышать 255 символов. Например, имяnik23 короткое, но малосодержательное. Пройдет несколько месяцев, и Вы вряд ли вспомните, что оно обозначает. С другой стороны, имя Квартальный_отчет_за_прошедший_год – просто блещет информацией, но как Вы уже догадались, работать с таким именем очень сложно.

2. В именах ячеек и диапазонов не должно быть пробелов. Если имя состоит из нескольких слов, то можете воспользоваться символом подчеркивания. Например, Квартальный_Отчет.

3. Регистр символов не имеет значения. Точнее Excel хранит имя в том виде, в котором Вы его создавали, но при использовании имени в формуле Вы можете вводить его в любом регистре. Например, сохранив имя в видеКвартальный_Отчет, в формуле вы можете вводить просто – квартальный_отчет. Excel это имя распознает и сам преобразует в нужный вид.

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

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

ДИСПЕТЧЕР ИМЕН В EXCEL – ИНСТРУМЕНТЫ И ВОЗМОЖНОСТИ

Со временем в Вашей рабочей книге Excel будет появляться все больше новых имен и возникнет острая необходимость всем этим как-то управлять. К счастью Excel предлагает очень удобный способ управления именами – Диспетчер имен.

КАК ПОЛУЧИТЬ ДОСТУП К ДИСПЕТЧЕРУ ИМЕН

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Откроется диалоговое окно Диспетчер имен:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

КАКИЕ ЖЕ ВОЗМОЖНОСТИ ПРЕДОСТАВЛЯЕТ НАМ ЭТО ОКНО?

1. Полные данные о каждом имени, которое имеется в книге Excel. Если часть данных не помещается в рамки диалогового окна, то вы всегда можете изменить его размеры.

2. Возможность создать новое имя. Для этого необходимо щелкнуть по кнопке Создать.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Возможность редактировать любое имя из списка. Для этого выделите требуемое имя и нажмите кнопкуИзменить.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

4. Возможность удалить любое имя из списка. Для этого выделите нужное имя и нажмите кнопку Удалить.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

5. В случае, когда Ваша книга Excel содержит большое количество имен, Вы всегда можете воспользоваться встроенным в диалоговое окно фильтром. Для этого щелкните по кнопке Фильтр и из раскрывающегося списка выберите то, что Вам нужно.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

§

Excel позволяет присваивать имена не только ячейкам и диапазонам, но и константам. Константой могут выступать как текстовые, так и числовое значения. В этом уроке Вы узнаете, как назначить имя константе в Microsoft Excel.

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

В Excel существует еще один способ работать с такими величинами – присвоить им осмысленные имена. Согласитесь, что имена плБензина или плКеросина легче запомнить, чем значения 0,71 или 0,85. Особенно когда таких значений десятки, а то и сотни.

Имя константе, как и имя области, присваивается с помощью диалогового окна Создание имени. Разница лишь в том, что в поле Диапазон необходимо ввести величину константы.

Более подробно о присвоении имен с помощью диалогового окна Создание имени Вы можете узнать из урокаКак присвоить имя ячейке или диапазону в Excel.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Нажимаем ОК, имя будет создано. Теперь, если ввести в ячейку следующую формулу =плБензина, она возвратит значение константы.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ЗНАКОМСТВО С ФОРМУЛАМИ МАССИВА В EXCEL

Формула массива – это один из наиболее мощных инструментов Excel, но также и один из самых сложных. Массивом в Excel называют набор элементов, который может обрабатываться как одно целое, так и как набор, т.е. каждый элемент по отдельности. В этом уроке мы познакомимся с базовыми сведениями о массивах.

ОДНОМЕРНЫЕ И ДВУХМЕРНЫЕ МАССИВЫ

В Excel различают одномерные и двухмерные массивы. Одномерный массив, его еще называют вектором, может храниться в одной строке (горизонтальный массив) …

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

…или в одном столбце (вертикальный массив).

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Двухмерный массив хранится в диапазоне ячеек, его еще называют матрицей.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В отличие от языка программирования VBA, Excel не поддерживает трехмерные массивы.

РАЗМЕРНОСТЬ МАССИВА

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

Примеры приведены для русифицированной версии Excel.

1. Элементы в одномерных горизонтальных массивах разделяются точкой с запятой (;). Например, на рисунке ниже представлен одномерный горизонтальный массив, который состоит из 5 элементов:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если ввести его на рабочий лист Excel, получим следующий результат:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Чтобы вставить такой массив в Excel, необходимо сначала выделить строку, состоящую из 5 ячеек, затем ввести формулу массива и нажать комбинацию клавиш Ctrl Shift Enter.

2. Элементы в одномерном вертикальном массиве разделяются двоеточием (:). Например, на рисунке ниже представлен одномерный вертикальный массив, который состоит из 4 элементов.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если ввести его на рабочий лист, получим следующий результат:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. В двухмерных массивах для разделения элементов в строке используется точка с запятой, а для разделения строк – двоеточие. На рисунке ниже представлен массив, который состоит из 3 строк и 5 столбцов.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если ввести данный массив в Excel, результат будет таким:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

В качестве заполнителя отсутствующего элемента в массиве можно использовать значение #Н/Д (нет данных). К примеру, следующая запись будет синтаксически верной:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Введя данный массив на рабочий лист Excel, получим следующее:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ОДНОЯЧЕЕЧНЫЕ И МНОГОЯЧЕЕЧНЫЕ ФОРМУЛЫ МАССИВА

Excel поддерживает формулы массива двух видов: многоячеечные и одноячеечные. В первом случае формула занимает несколько ячеек, т.е. возвращает в качестве результата массив:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Во втором случае формула вводится в одну ячейку и возвращает только одно значение:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

МНОГОЯЧЕЕЧНЫЕ ФОРМУЛЫ МАССИВА В EXCEL

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

Читайте также:  Детский целевой вклад в Сбербанке России - как получить компенсацию в 2020 году

ПРИМЕНЕНИЕ МНОГОЯЧЕЕЧНОЙ ФОРМУЛЫ МАССИВА

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В данном примере диапазон D2:D6 содержит пять формул. Многоячеечная формула массива позволяет вычислить тот же самый результат, используя одну единственную формулу. Чтобы воспользоваться формулой массива выполните следующие действия:

1. Выделите диапазон ячеек, где должны отображаться результаты. В нашем случае это диапазон D2:D6.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Как и при вводе любой формулы в Excel, первым делом необходимо ввести знак равенства.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Выделите первый массив значений. В нашем случае это диапазон с ценами товаров B2:B6.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

4. Введите знак умножения и выделите второй массив значений. В нашем случае это диапазон с количеством товаров C2:C6.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

5. Если бы мы вводили обычную формулу в Excel, то закончили бы ввод нажатием клавиши Enter. Но поскольку это формула массива, то нужно нажать комбинацию клавиш Ctrl Shift Enter. Этим мы укажем Excel, что это не обычная формула, а формула массива, и он автоматически заключит ее в фигурные скобки.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Excel автоматически заключает формулу массива в фигурные скобки. Если Вы вставите скобки вручную, Excel воспримет это выражение как обычный текст.

6. Обратите внимание, что все ячейки диапазона D2:D6 содержат абсолютно одинаковое выражение. Фигурные скобки, в которые оно заключено, говорят о том, что это формула массива.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

7. Если бы мы при вводе формулы массива выделили меньший диапазон, например, D2:D4, то она вернула бы нам только первые 3 результата:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

8. А если больший диапазон, то в “лишних” ячейках оказалось бы значение #Н/Д (нет данных):

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Когда мы умножаем первый массив на второй, перемножаются их соответствующие элементы (B2 с C2, B3 с C3, B4 с C4 и т.д.). В итоге образуется новый массив, который содержит результаты вычислений. Поэтому, чтобы получить корректный результат, размерности всех трех массивов должны быть соответствующими.

ПРЕИМУЩЕСТВА МНОГОЯЧЕЕЧНЫХ ФОРМУЛ МАССИВА

Использовать в Excel единую многоячеечную формулу массива в большинстве случаев предпочтительнее, чем несколько отдельных формул. Рассмотрим основные преимущества, которые она предлагает:

1. Используя многоячеечную формулу массива, Вы на 100% уверены, что все формулы в рассчитываемом диапазоне введены правильно.

2. Формула массива в большей степени защищена от случайного изменения, поскольку редактировать можно только весь массив в целом. Если Вы попробуете изменить часть массива, у Вас ничего не получится. Например, при попытке удалить формулу из ячейки D4, Excel выдаст следующее предупреждение:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

МАССИВЫ КОНСТАНТ В EXCEL

В Microsoft Excel можно создавать массивы, которые не хранятся в диапазонах ячеек. Их принято называть массивами констант. В этом уроке Вы узнаете, что же такое массивы констант и как с ними работать в Excel.

КОРОТКО О МАССИВАХ КОНСТАНТ

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

={1;2;3;4;5;6}

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

=СУММ({1;2;3;4;5;6})

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

=МАКС({1;2;3;4;5;6} {7,8,9,10,11,12})

Массивы констант могут содержать числа, текст, логические значения и значения ошибки #Н/Д:

={12;”Текст”;ИСТИНА;ЛОЖЬ;#Н/Д}

У Вас может возникнуть резонный вопрос: Зачем нужен такой массив? Отвечу на него в виде примера.

§

На рисунке ниже приведен список студентов, которые получили определенные оценки:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

={“”;”Неудовл.”;”Удовл.”;”Хорошо”;”Отлино”}

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

Тогда формула, возвращающая нужный нам результат, будет выглядеть следующим образом:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В этом примере функция ИНДЕКС возвращает значение элемента из массива констант, положение которого задано порядковым номером (оценкой).

Данная формула не является формулой массива, хоть она и содержит массив. Поэтому при ее вводе достаточно нажать клавишу Enter.

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Но грамотнее будет использовать многоячеечную формулу массива. Выглядеть она будет следующим образом:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Не забывайте указывать знак равенства в поле Диапазон, иначе Excel воспримет массив как текстовую строку.

Теперь формула выглядит менее пугающей:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

РЕДАКТИРОВАНИЕ ФОРМУЛ МАССИВА В EXCEL

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

ПРАВИЛА РЕДАКТИРОВАНИЯ ФОРМУЛ МАССИВА

Когда формула массива помещена в одну ячейку, то ее редактирование в Excel обычно не представляет особой сложности. Здесь главное не забыть закончить редактирование комбинацией клавиш Ctrl Shift Enter.

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

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

2. Нельзя удалять ячейки, которые входят в формулу массива. Можно удалить только весь массив.

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

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

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

Как видите, все перечисленные выше правила подчеркивают, что массив – это одно целое. Если не выполнить, хотя бы одно из вышеперечисленных правил, Excel не даст отредактировать массив и выдаст следующее предупреждение:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ВЫДЕЛЕНИЕ МАССИВА В EXCEL

Если необходимо изменить формулу массива, то первое, что нужно сделать – это выделить диапазон, в котором содержится массив. В Excel существует, как минимум, 3 способа сделать это:

1. Выделить диапазон массива вручную, т.е. с помощью мыши. Это самый простой, но в ряде случаев абсолютно непригодный способ.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

А затем на вкладке Главная из раскрывающегося списка Найти и выделить выберите пункт Выделить группу ячеек.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Откроется диалоговое окно Выделить группу ячеек. Установите переключатель на пункт текущий массив и нажмите ОК.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Текущий массив будет выделен:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. При помощи комбинации клавиш Ctrl /. Для этого выберите любую ячейку массива и нажмите комбинацию.

КАК УДАЛИТЬ ФОРМУЛУ МАССИВА

Самое простое, что Вы можете сделать с массивом в Excel – это удалить его. Для этого достаточно выделить нужный массив и нажать клавишу Delete.

КАК ОТРЕДАКТИРОВАТЬ ФОРМУЛУ МАССИВА

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

1. Выделите диапазон массива любым из известных Вам способов. В нашем случае это диапазон C1:C12.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Перейдите в режим редактирования формулы, для этого щелкните по строке формул или нажмите клавишу F2. Excel удалит фигурные скобки вокруг формулы массива.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Внесите необходимые корректировки в формулу:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

4. А затем нажмите комбинацию клавиш Ctrl Shift Enter, чтобы сохранить изменения. Формула будет отредактирована.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ИЗМЕНЕНИЕ РАЗМЕРОВ ФОРМУЛЫ МАССИВА

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

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

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

ПРИМЕНЕНИЕ ФОРМУЛ МАССИВА В EXCEL

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

ПОДСЧЕТ КОЛИЧЕСТВА ЗНАКОВ В ДИАПАЗОНЕ ЯЧЕЕК

На рисунке ниже представлена формула, которая подсчитывает количество знаков, включая пробелы, в диапазоне A1:A10.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В данном примере функция ДЛСТР подсчитывает длину каждой текстовой строки из заданного диапазона, а функцияСУММ – суммирует эти значения.

НАИБОЛЬШИЕ И НАИМЕНЬШИЕ ЗНАЧЕНИЯ ДИАПАЗОНА В EXCEL

Следующая формула возвращает 3 наибольших значения диапазона A1:D6

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если необходимо найти наименьшие значения, просто замените функцию НАИБОЛЬШИЙ на НАИМЕНЬШИЙ.

ПОДСЧЕТ КОЛИЧЕСТВА ОТЛИЧИЙ ДВУХ ДИАПАЗОНОВ В EXCEL

Формула массива, представленная на рисунке ниже, позволяет подсчитать количество различий в двух диапазонах:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Данная формула сравнивает соответствующие значения двух диапазонов. Если они равны, функция ЕСЛИ возвращает ноль, а если не равны – единицу. В итоге получается массив, который состоит из нулей и единиц. Затем функцияСУММ суммирует значения данного массива и возвращает результат.

Необходимо, чтобы оба сравниваемых диапазона имели одинаковый размер и ориентацию.

ТРАНСПОНИРОВАНИЕ МАССИВА В EXCEL

Вспомним предыдущий пример и попробуем усложнить задачу. К примеру, требуется сравнить диапазоны в Excel, которые имеют одинаковый размер, но разную ориентацию – один горизонтальный, а другой вертикальный. В этом случае на помощь придет функция ТРАНСП, которая позволяет транспонировать массив. Теперь формула из прошлого примера, немножко усложнится:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Транспонировать массив в Excel – значит изменить его ориентацию, а точнее заменить строки столбцами, а столбцы строками.

СУММИРОВАНИЕ ОКРУГЛЕННЫХ ЗНАЧЕНИЙ В EXCEL

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если изменить форматирование в диапазоне D4:D8, то становится видно, что значения в этих ячейках не округлены, а всего лишь визуально отформатированы. Соответственно, мы не можем быть уверенны в том, что сумма в ячейке D9 является точной.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В Excel существует, как минимум, два способа исправить эту погрешность.

1. Ввести в ячейки D4:D8 уже округленные значения. Формула массива будет выглядеть следующим образом:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Использовать в ячейке D9 формулу массива, которая сначала округляет значения, а затем суммирует их.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

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

НАИБОЛЬШЕЕ ИЛИ НАИМЕНЬШЕЕ ЗНАЧЕНИЕ ПО УСЛОВИЮ

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Пусть в ячейке G3 мы будем задавать фамилию продавца, тогда формула массива будет выглядеть следующим образом:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

В данном случае функция ЕСЛИ сравнивает значения диапазона B3:B1234 c заданной фамилией. Если фамилии совпадают, то возвращается сумма продажи, а если нет – ЛОЖЬ. В итоге функция ЕСЛИ формирует одномерный вертикальный массив, который состоит из сумм продаж указанного продавца и значений ЛОЖЬ, всего 1231 позиция. Затем функция МАКС обрабатывает получившийся массив и возвращает из него максимальную продажу. В нашем случае это:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Если массив содержит логические значения, то функции МАКС и МИН их игнорируют.

Чтобы вывести минимальную продажу, воспользуемся этой формулой:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Данная формула позволяет вывести 5 наибольших продаж указанного продавца:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ПОДХОДЫ К РЕДАКТИРОВАНИЮ ФОРМУЛ МАССИВА В EXCEL

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

ПОДХОД №1

На рисунке ниже представлена формула массива, размер которой необходимо расширить с C3:C8 до C1:C10.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Чтобы проделать это, выполните следующие действия:

1. Выделите диапазон ячеек, которые будет содержать новый массив. Здесь главное, чтобы новый массив перекрывал собой старый, иначе Excel не даст закончить редактирование и выдаст предупреждение. В нашем случае это диапазон C1:C10.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Далее нужно удостовериться, что активной является ячейка, принадлежащая старому массиву. Это позволит сохранить структуру формулы, пусть и с “ложными” ссылками на ячейки. Поскольку в нашем случае активной является пустая ячейка, выделим подходящую. Допустим C3:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Сделать ячейку активной в выделенном диапазоне можно с помощью клавиши Enter.

3. Перейдем в режим редактирования формулы (клавиша F2). На данном этапе можно перезадать области, участвующие в формировании массива. В нашем случае мы перезададим обе области:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

4. Сохраним изменения в формуле, нажав комбинацию клавиш Ctrl Shift Enter. Формула массива будет обновлена.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

ПОДХОД №2

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

На рисунке ниже представлен массив, диапазон которого необходимо уменьшить с С1:С10 до C1:C5.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Чтобы проделать это, выполните следующие действия:

1. Выделите диапазон, содержащий формулу массива.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Нажмите клавишу F2, чтобы перейти в режим редактирования.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Нажмите комбинацию клавиш Ctrl Enter. Это действие вводит отдельную формулу в каждую из ячеек диапазона. Причем эти формулы не являются формулами массивов. Например, в ячейке С3 содержится следующая формула:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

4. Перезадайте диапазон, в котором будет находиться новый массив, и сделайте активной ячейку из старого массива. Это позволит сохранить формулу, пусть и с “ложными” ссылками. В нашем случае мы просто уменьшим размер диапазона до С1:C5:

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

6. Нажмите комбинацию клавиш Ctrl Shift Enter, чтобы сохранить изменения. Размер массива будет уменьшен.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

7. Удалите лишние формулы из диапазона C6:C10.

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

КАК НАСТРОИТЬ ЛЕНТУ В EXCEL 2022

Не всем пользователям Microsoft Excel удобно работать с предустановленными на Ленте вкладками. Иногда гораздо практичнее создать свою собственную вкладку с требуемым набором команд. В данном уроке мы покажем Вам как это можно сделать в Excel.

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

1. Щелкните по Ленте правой кнопкой мыши и из выпадающего меню выберите пункт Настройка ленты.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. В появившемся диалоговом окне Параметры Excel найдите и выберите Создать вкладку.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. Убедитесь, что выделена Новая группа. Выберите команду и нажмите Добавить. Вы также можете перетаскивать команды напрямую в группы.

4. Добавив все необходимые команды, нажмите OK. Вкладка будет создана, а команды добавлены на Ленту.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

РЕЖИМ СЕНСОРНОГО УПРАВЛЕНИЯ ЛЕНТОЙ В EXCEL 2022

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

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

1. Щелкните стрелку справа от Панели быстрого доступа и из раскрывающегося меню выберите пункт Режим сенсорного управления или мыши.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Команда Режим сенсорного управления или мыши появится на Панели быстрого доступа.

3. В раскрывающемся меню команды выберите пункт Сенсорное управление.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

4. Лента переключится в режим сенсорного управления, а размер иконок и расстояние между ними увеличится.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

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

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

§

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

ЧТО ТАКОЕ СТИЛЬ ССЫЛОК?

Каждый лист Excel состоит из строк и столбцов. В большинстве случаев, столбцы обозначаются буквами (A, B, C), а строки обозначаются цифрами (1, 2, 3). В Excel это называется стиль ссылок A1. Тем не менее, некоторые предпочитают использовать другой стиль, где столбцы также обозначаются цифрами. Это называется стиль ссылок R1C1.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Стиль ссылок R1C1 бывает полезен в некоторых ситуациях, но как показывает практика – это большая редкость. Так же есть группа пользователей, которым нравится работать с таким форматом ссылок, правда, они уже давно не новички. Вы же в большинстве случаев будете работать со стилем ссылок А1, который установлен в Microsoft Excel по умолчанию.

В данном учебном курсе и практически во всех уроках данного сайта используется стиль ссылок A1. Если в данный момент Вы используете стиль ссылок R1C1, Вам необходимо его отключить.

ВКЛЮЧЕНИЕ/ВЫКЛЮЧЕНИЕ СТИЛЯ ССЫЛОК R1C1

1. Откройте вкладку Файл, чтобы перейти к представлению Backstage.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

2. Нажмите Параметры.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

3. В появившемся диалоговом окне Параметры Excel выберите раздел Формулы. Поставьте флажок Стиль ссылок R1C1 и нажмите OK. Excel перейдет к стилю ссылок R1C1.

КАК ПОСЧИТАТЬ НАКОПИТЕЛЬНУЮ СУММУ В EXCEL? — Студопедия.Нет

Как вы уже догадались, чтобы вернуться к стилю ссылок A1, достаточно снять этот флажок.

Оцените статью
Adblock
detector