Darbe.ru

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

Как сделать формулу в Excel

Как сделать формулу в Excel

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

Что такое формула?

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

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

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

Первые шаги

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

Как сделать формулу в программе Эксель

Для примера, мы имеем две колонки случайных чисел. Итак:

1) Выделите ячейку, значение которой хотите рассчитать.

2) Ввод формулы всегда начинается со знака «=» – он указывает программе, что вводится не просто текст. Вы можете вводить ее прямо в поле, либо в верхнюю строку.

Например, мы вручную ввели формулу в Е11.

Как сделать формулу в Экселе

Как создать формулу в программе Excel

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

3) Она будет работать без изменений, даже если вы измените данные в одной из единиц, участвующих в вычислении.

Как создать формулу в Экселе

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

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

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

Копирование

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

1) Суммируем значения конкретных ячеек. В моем примере это А1 и В1.

2) Получаем результат (С1).

3) Наводим курсор на нижний правый угол ячейки и ждем появления черного крестика («плюсика»).

4) Протаскиваем крестик вниз, выделяя необходимое нам поле. Заданные параметры применяются автоматически.

Как сделать формулу в excel - копирование

Функции

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

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

Найти необходимую функцию можно несколькими способами:

• найти в категориях библиотеки в верхнем меню;

• при помощи команды «вставить функцию»;

Как сделать формулу в Эксель - функции

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

На нашем примере будет использоваться команда «Вставить функцию»:

1) При нажатии команды, всплывает диалоговое окно. В строке «Категория» необходимо указать категорию, к которой относится необходимая вам функция. Если вы не знаете категорию, укажите «Полный алфавитный перечень», чтобы отобразился полный список существующих в программе вариантов. Найти необходимый не составит никакого труда – в этом случае, они указаны в алфавитном порядке.

2) После указания параметра поиска, отображается список. Ищите необходимую и нажмите «ОК».

Как сделать формулу в Экселе - Вставка функций

3) Появляется новое диалоговое окно. В строке «Число 1» указан диапазон суммирования. Значение, которое получится в результате этого действия, также указано в диалоговом окне. Для того чтобы сумма отобразилась в ячейке, просто нажмите «ОК».

Как сделать формулу в excel - аргументы функций

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

Читайте так же:
Можно ли восстановить эмаль на кастрюле

Как сделать формулу в Эксель - аргументы функций

Остальные два способа вызова отличаются от этого незначительно.

Обратите внимание на то, что суммирование – это одна из наиболее часто используемых функций в Excel. Поэтому разработчики позаботились о том, чтобы пользователю было проще – они сделали ее горячей кнопкой в меню «Главная» и в меню «Формулы».

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

На этом примере, воспользуемся быстрой кнопкой в меню «Главная».

1) Выделяем ячейку, значение которой рассчитываем с помощью СРЗНАЧ. У меня это В11.

Как сделать формулу в Экселе - СРЗНАЧ

Как сделать формулу в Excel - Автосумма

2) Нажимаем стрелку выпадающего меню «Автосумма» и выбираем функцию «Среднее».

3) В контуре «крутящихся муравьев» автоматически выделен весь диапазон колонки. Меняем его, для разнообразия. Теперь высчитывается среднее значение всего пяти ячеек.

4) Нажимаем «Enter» для отображения результата.

Как сделать формулу в excel - Столбец

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

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

Источниками данных для формирования сводной таблицы являются:

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

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

Например: Есть таблица товаров (скачать файл)

И нужно выполнить два задания:

1. Создать сводную таблицу, выводящую для каждого товара среднюю цену и суммарное количество. Создать 2 группы данных: для 2011 и 2012 годов (Произвести группировку по годам).

2. Создать сводную таблицу, выводящую для каждого товара среднюю цену с НДС (требуется предварительно создать вычисляемое поле — Цена с НДС). Создать группы данных для каждого Продавца.

Выполним 1 задание:

Для этого выберите Вставка -> Сводная таблица. На экране вы увидите Мастера сводных таблиц.

В верхней строчке выделите всю таблицу вместе с названиями полей ‘Исходная таблица’!$A$6:$G$57

И выберите На новый лист

Справа отобразятся поля

Перенести в Значение Количество и Цена

Перенести в Строки Дата и Товар

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

Выберите первую ячейку с месяцем январь и зайдите в меню раздел Данные -> Группировать

Оставьте только годы

И у вас должно получиться!

Выполним 2 задание:

В исходной таблице построим новый столбец «Цена с НДС» вбейте формулу =E7*1,13 (цена * 1,13) и растяните по всем ячейкам.

Теперь также строим сводную таблицу как в задании 1, поэтому без картинок.

В Строки разместить Продавец и товар

В Значения разместить (цена с НДС цена, цена с НДС) Сумма по полю цена с НДС, Количество по полю Цена, Среднее по полю цена с НДС

Слева на ячейке с Canon нажмите ПКМ (правой кнопкой мыши) и уберите галку с «Промежуточный итог продавец»

Все отлично! Вот что у вас получится.

В итоге! Сводная таблица — это способ быстро собрать таблицу с промежуточными вычислениями.

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

Арифметические операции в Excel

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

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

Читайте так же:
Можно ли вязанные вещи

Базовые арифметические операции в Excel – это:

  • Сложение (знак «+» на клавиатуре),
  • Вычитание (знак «-» на клавиатуре),
  • Умножение (знак «*» на клавиатуре),
  • Деление (знак «/» на клавиатуре),
  • Возведение в степень (знак «^» на клавиатуре).

Все операции производятся в ячейках таблицы после установки знака равенства «=». После ввода операций жмем Enter для получения результата. Комбинируя базовые операции, можно проводить более сложные арифметические операции. И при необходимости использовать их формулы уже для оперирования другими данными. Давайте посмотрим, как всё это работает.

Excel как калькулятор

Таблицу Excel можно использовать элементарно как обычный калькулятор. Если возникнет необходимость отдельно от таблицы с данными произвести какие-то промежуточные вычисления, нам теперь не нужно отвлекаться и использовать отдельное приложение калькулятора, всё делается у нас в любой пустой ячейке Microsoft Excel. Ставим знак «=». Вводим нужное арифметическое вычисление – сложение, вычитание, умножение, деление, возведение в степень. Жмём Enter и получаем результат.

Арифметические операции в Excel

Можем произвести более сложное вычисление.

Арифметические операции в Excel

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

Арифметические операции в Excel

Арифметические операции с данными таблицы

Рассмотрев принцип выполнения арифметических операций непосредственно с числами можем поступить и с табличными значениями. Возьмём какой-нибудь элементарный расчёт, вот например, годовой расчёт выплаты кредита. Каждый месяц надо уплатить и тело кредита и проценты. Вносим эти данные в таблицу. Последний столбец у нас будет для вычислений – за каждый месяц и в общем. Чтобы узнать общую сумму наших отчислений в месяц, сложим тело кредита и проценты. А в последнем столбце в ячейке первого месяца мы ставим знак «=». Кликаем ячейку тела кредита, ставим знак «+», затем кликаем на ячейку суммы процентов. Жмём Enter.

Арифметические операции в Excel

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

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

Арифметические операции в Excel

Альтернативный же вариант – это кликаем ячейку формулы, наводим курсор в её правый нижний угол до превращения курсора в жирный плюс и оттягиваем этот плюс до нужной ячейки.

Арифметические операции в Excel

И, наконец, нам нужно узнать общую сумму за все 12 месяцев. Отдельно по телу нашего кредита, отдельно по процентам, а также вместе тело и проценты. Здесь можно применить метод обычных вычислений: поставить в итоговой ячейке знак «=», сложить каждые 12 ячеек, нажать Enter. И так нам нужно будет сделать со всеми тремя столбцами – тела, процентов и общей их суммы.

Арифметические операции в Excel

И мы получим итоговые суммы.

Арифметические операции в Excel

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

Автосумма

Чтобы получить итоговую сумму значений подряд идущих ячеек (вертикально, горизонтально – не важно), мы выделяем блоком эти ячейки. Во вкладке панели инструментов Excel «Формула» кликаем пункт «Автосумма». И в ячейке ниже или правее получаем результат.

Читайте так же:
Как в excel вставить текущую дату

Арифметические операции в Excel

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

Арифметические операции в Excel

Если у вас остались вопросы, оставляйте их в комментариях ниже. А пока… пока.

Рейтинг в excel как сделать

рейтинг в excel как сделатьЕсли необходимо быстро построить рейтинг значений, не прибегая к сортировке данных в таблице, можно воспользоваться функцией РАНГ. Для этого:

1. В дополнительный столбец, в котором будем указывать рейтинг, вставляем функцию РАНГ (пишем в ячейке =РАНГ и выбираем из списка предложенную EXCEL функцию, жмем в строке формул fx)

рейтинг в excel как сделать

рейтинг в excel как сделать
2. Заполняем аргументы в открывшемся окне: «Число» – указываем первое значение в нашей таблице в той же строке, где находится формула.

рейтинг в excel как сделать
3. «Ссылка» — указываем весь массив данных, т.е. диапазон со всеми числами (значениями продаж).

рейтинг в excel как сделать
4. Фиксируем границы этого диапазона (нажимаем F4 на клавиатуре) для того, чтобы при протягивании в дальнейшем адрес диапазона не «съезжал» и нажимаем ОК.

рейтинг в excel как сделать
5. Протягиваем, формулу на все ячейки столбца «рейтинг» вниз.

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

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

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

рейтинг в excel как сделать

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

Скачать пример.

Первый этап.

Во-первых, давайте отсортируем все продажи по убыванию и выберем 10 лучших.

Для этого я решил использовать функцию

Наша формула выглядит следующим образом:

где C4:C19 это диапазон с количеством реализованных продуктов.

В результате мы получаем лист Топ-10 продаж. Далее, более сложная часть.

рейтинг в excel как сделать

Второй этап.

Как назначить названия продуктов номерам?

Если Вы уверены, что количество проданных товаров

никогда не будет одинаковым

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

Наша формула может выглядеть следующим образом:

И она будет работать отлично.

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

Для первого продукта мы воспользуемся формулой:

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

Протягиваем эту формулу для всех оставшихся ячеек.

рейтинг в excel как сделать

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

Обратите внимание на фигурные скобки перед и после формулы. Эти скобки обозначают что формула применена для массива. Что бы Вам добиться такого же результата, то внесите в ячейку формулу, а после нажмите комбинацию клавиш Ctrl+Shift+Enter.

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

Для ранжирования данных в Excel применяются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в ранжированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.

Пример функции РАНГ в Excel

Функция используется при ранжировании в перечне чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если отсортировать список по возрастанию, то функция вернет позицию числа. Например, в массиве чисел <30;2;26>число 2 будет иметь ранг 1; 26 –2; 30 –3 (как наибольшее значение в списке).

  1. Число. Для, которого необходимо определить номер в ранжировании.
  2. Ссылка. На массив чисел или диапазон ячеек с числовыми значениями. Если задать в качестве аргумента просто числа, то функция вернет ошибку. Нечисловым значениям номер не присваивается.
  3. Порядок. Способ упорядочения чисел в списке. Варианты: аргумент равен «0» или опущен – значение 1 присваивается максимальному числу в списке (как будто список отсортирован в порядке убывания); аргумент равен любому неравному нулю числу – номер ранжирования 1 присваивается минимальному числу в списке (как будто список отсортирован в порядке возрастания).
Читайте так же:
Как в excel сделать ссылку на картинку

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

Аргумент, определяющий способ упорядочения чисел, равен «0». Следовательно, в данной функции номера присваивались значениям от большего к меньшему. Максимальному числу 87 присвоен номер 1.

В третьем столбце приведена формула с рангом по возрастанию.

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

Желтым цветом выделены повторяющиеся числа. Для них определяется один и тот же номер. Например, числу 7 во втором столбце присвоен номер 9 (и во второй строке, и в девятой); в третьем столбце – 3. Но ни одно из чисел во втором столбце не будет иметь 10, а в третьем – 4.

Чтобы ранги не повторялись (иногда это мешает пользователю решить поставленную задачу), используется следующая формула:

Для работы функции можно установить пределы. Например, необходимо ранжировать только значения от 0 до 30. Чтобы решить задачу, применим функцию ЕСЛИ (=ЕСЛИ(A2

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

Пример функции РАНГ.РВ в Excel

В версиях Excel, начиная с 2010 года, появилась функция РАНГ.РВ. Это абсолютный аналог предыдущей функции. Синтаксис такой же. Буквы «РВ» в названии указывают на то, что при обнаружении формулой одинаковых значений функция вернет высший номер ранжирования (то есть первого обнаруженного элемента в перечне равных).

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

Пример функции РАНГ.СР в Excel

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

Вот результат работы функции:

Формула в столбце «по убыванию»: =РАНГ.СР(A2;$A$2:$A$9;0). Так, функция значению 87 присвоила средний номер 1,5.

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

Функция присвоила каждому из них ранг 5, что является средним для 4, 5 и 6.

Сравним работу двух функций:

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

Скачать примеры функции ранжирования РАНГ в Excel.

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

Вам нужно присвоить каждому числу в Excel свой номер, чтобы можно было их отсортировать по этому номеру? Можно придумать сложные конструкции для текстовых данных, но для числовых данных есть специальная функция РАНГ в Excel. Относится к числу статических функций и бывает довольно полезной. В статье мы так же рассказываем о новых функциях из Excel 2010 РАНГ.CP() и РАНГ.РВ()

Как пользоваться. Функция РАНГ в Excel

Функцией РАНГ пользоваться довольно просто. Набор аргументов тоже несложный, а именно:

  • Число — число для которого нужно определить ранг/место в массиве
  • Ссылка — массив значений в котором определяется ранг/место числа
  • Порядок — необязательный аргумент.

. Если поставить 0 или не заполнять этот аргумент то ранг определяется по убыванию. Т.е. наибольшее число 2000 будет на 1ом месте.

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

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

Примечание. Если списке 2 одинаковых числа — в нашем случае 1 400, то им будет присвоены одинаковые значения ранга — 2, т.е. второе место. При этом следующее место будет уже не 3, а 4 (1 250). Довольно важное свойство, которое не всех устраивало. Поэтому разработчики пошли дальше и сделали дополнительные функции РАНГ.CP() и РАНГ.РВ() в Excel 2010.

РАНГ.CP

Если чисел с одинаковым значением 1400 3 и более, всем им будет присвоен ранг =2 — это для обычной функции РАНГ. Функция РАНГ.СР посчитает место как среднее из 3 мест, т.е. 3 места занимают второе, третье и четвертое место

Читайте так же:
Макрос впр в excel

РАНГ присвоит всем 3 числам второе место.

РАНГ.CH присвоит всем троим четвертое место.

РАНГ.РВ

Все просто — это та же функция РАНГ только для новых версий Excel — 2010 и старше.

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

Excel и длинные формулы

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

Но я бы хотел показать несколько примеров использования стандартных функций листа программы Excel. Достаточно часто приходится городить огород из формул, чтобы вытащить из ячеек нужный текст или числа. Происходит это как правило из-за неверного представления данных. Числа — это числа, текст — это текст, не надо их смешивать. Даты (и время) это числа, с которыми можно и нужно производить вычисления. Не надо путать 14.03.2018 с 14 марта 2018 г. Второе это только пользовательский формат даты [$-F800], то, что мы видим в ячейке, а в строке формул будет 14.03.2018. И хватит об этом.

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

На рисунке часть таблицы _t2. Необходимо вычислить максимальное одновременное количество событий. Для этого есть такая формула:

Формула массивная, ввод подтверждается одновременным нажатием кнопок Control, Shift, Enter (CSE). В строке формул будет видно, что формула в фигурных скобках. МУМНОЖ делает виртуальную матрицу размером число строк таблица на число строк таблицы, в строках которой результаты вычисления, как если бы в строки таблицы была введена следующая формула (для второй строки) =СУММПРОИЗВ(Ч((C$2:C2>A2)))/ =SUMPRODUCT(N((C$2:C2>A2))), и протянуть на всю таблицу.

2. То же самое, но посложнее. Кроме начала и окончания есть количество агрегатов или отдаваемая/получаемая мощность. Необходимо определить максимальную мощность в какой-то момент времени.

Часть таблицы _t1:

Поскольку время — это доля единицы, умножаем значения на количество минут в сутках, чтобы получить целые числа. И второй аргумент МУМНОЖ – столбец значений мощности. В первом случае был сформирован столбец единиц.

3. Слева в таблице представлены суммы, соответствующие определенным интервалам. Необходимо вычислить суммы по месяцам. Предполагается, что суммы распределены равномерно внутри своих интервалов.

В столбце Н первые числа месяцев, в столбце I следующие формулы:

Формулы массивные. Можно было бы использовать СУММПРОИЗВ/SUMPRODUCT, но ЕСЛИОШИБКА не работает без массивного ввода. Также, к сожалению, МАКС и МИН не могут сформировать виртуальный массив значений. Поэтому для вычисления частей интервалов, приходящихся на месяц, используется ЕСЛИ. Так как полученные части могут быть отрицательными, искусственно вызывается ошибка (квадратный корень из отрицательного числа приводит к ней). В исходной таблице можно было обойтись без столбцов длительности интервала и дневной суммы. Формула стала бы немного длиннее.

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

Это очень хорошо (здесь), что первая строка листа соответствует 01.01.1901. Поэтому достаточно легко сформировать массив дат года с помощью ИНДЕКС и СТРОКА и считать только пятницы тринадцатое.

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

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

Для таких исходных данных:

Формула массивная. В А3 – сумма, для которой нужно вычислить проценты. Исходные данные –tbl.
И, напоследок, кредитная линия с изменяемой процентной ставкой. Формула для расчета суммы процентов в зависимости от дат/сумм траншей/возвратов.

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