Содержание

Инструмент Таблица данных в Excel

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

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

Задача 1. А ведь ничего сложного

На самом деле, ничего сверхъестественного в вычислениях нет. Для расчета ежемесячного платежа по аннуитету используется финансовая функция Excel ПЛТ (PMT). Соответственно, зная желаемую сумму займа и срок кредитования, можно рассчитать ежемесячный платеж.

Общая сумма выплат рассчитывается как ежемесячный платеж умноженный на количество периодов (всего 36 месяцев), проценты переплаты – это общая сумма выплат минус сумма займа.

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

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

Решение первой задачи с помощью инструмента «Таблица данных».

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

При использовании «Таблица данных» не важен тип ссылок в формуле

Шаг 2. Выделяется диапазон с тестовым расчетом и заготовкой под остальные данные (в нашем случае это диапазон B5:G8), дальше выбирается инструмент «Таблица данных» на вкладке Данные, группа «Прогноз», команда «Анализ что если» (для версии Excel 2016, если у вас версия 2013, то таблица данных находится в группе «Работа с данными»).

Выбор инструмента «Таблица данных» на ленте интерфейса

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

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

После расчета в строке формул находится только специальная функция ТАБЛИЦА

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

Задача 2. Рассчитать ежемесячный платеж при различных сроках займа и различных процентных ставках.

Подготовительная таблица выглядит следующим образом.

Заготовка для прогноза значений с двумя переменными

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

Расстановка ссылок на переменные в диалоговом окне

Результат работы инструмента «Таблица данных» с двумя входами

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

Больше примеров работы с финансовыми функциями, инструментом «Таблица данных» в восьмом занятии курса «Excel от новичка до профессионала».

[expert_bq id=»1570″]Например, можно вывести на экран все строки, содержащие некоторое значение в столбце, скажем, все строки, которые содержат дату заказа 11. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Без Повторений -Выводит только строки, удовлетворяющие критерию и не содержащие повторяющихся элементов. Если интервал критериев не определен, то в этом случае все строки списка, содержащие дубликаты, будут скрыты.

Какой Инструмент Управления Данными в ms Excel не Меняет Количество Обработанных Данных • Функция если

Обработка данных в Excel

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

Ввожу вот так 5-30 http://prntscr.com/nqfhb0 , получается вот так http://prntscr.com/nqfhg1 , менял формат ячейки на общий до ввода. Мне нужен диапазон от 5 до 30 для формулы массива. от 30-50 нормально получается.

Обработка данных в MS Excel

Деловая графика в MS Excel. Построение и редактирование диаграмм.

ДЕЛОВАЯ ГРАФИКА В MS EXCEL.
ПОСТРОЕНИЕ И РЕДАКТИРОВАНИЕ ДИАГРАММ.
Для большей наглядности числовые данные иногда
лучше представлять в графическом виде.
В Excel используется следующие типы диаграмм:
• Гистограммы;
• Графики;
• Круговые диаграммы;
• Диаграммы с областями;
• Биржевые диаграммы;
• Поверхностные диаграммы;
• Кольцевые диаграммы;
• Пузырьковые диаграммы;
• Лепестковые диаграммы.

Название
диаграммы
Валовой сбор зерна и зеленой массы люпина
Валовой сбор зерна, ц
Легенда
Валовой сбор зеленной массы, ц
1200
1000
1050
800
875
735
600
400
200
701
501,7
480,3
390,8
200,1 250
177,9
315
420,5
0
Академический
Ряд данных
Подписи
данных
Гродненский
БГСХА 382
Кастрычник
Нарочанский
Гродненский
Подписи
горизонтальной
оси
Демонстрация

Списки MS Excel. Сортировка и фильтрация списков. Промежуточные итоги.

СПИСКИ MS EXCEL. СОРТИРОВКА И ФИЛЬТРАЦИЯ
СПИСКОВ. ПРОМЕЖУТОЧНЫЕ ИТОГИ.
Списком называется таблица, строки которой
содержат однородную информацию.
Первая строка списка называется заглавной.
Остальные строки таблицы называются
записями.
Заголовки столбцов — названия полей.
Отдельные ячейки в столбце — поля.

Правила создания списка:

Со списками можно выполнять следующие действия:
• Сортировать;
• Отфильтровывать записи по определенному критерию.
• Вычислять промежуточные итоги.

Сортировка списков в MS Excel

СОРТИРОВКА СПИСКОВ В MS EXCEL
Сортировка — расположение данных в порядке возрастания или
убывания по одному или нескольким полям.
Сортировка списков осуществляется командой Сортировка на
вкладке Данные.

Фильтрация данных в списках MS Excel

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

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

Команда Промежуточные итоги в группе Структура на вкладке
Данные откроет диалоговое окно Промежуточные итоги

Функция ЕСЛИ

ФУНКЦИЯ ЕСЛИ
Функция ЕСЛИ имеет следующий синтаксис:
=ЕСЛИ(логическое_выражение;значение_если_истина
;значение_если_ложь)

Функция ВПР.

ФУНКЦИЯ ВПР.
Функция ВПР – это функция работы со справочниками
из категории «Ссылки и массивы».
Она ищет значение в первом столбце массива таблицы
и возвращает значение в той же строке из другого
столбца массива таблицы.

таблица – два или более столбцов данных. Можно
использовать ссылку на диапазон или имя диапазона.
Значения в первом столбце аргумента «таблица» — это
значения, в которых выполняется поиск аргумента
«искомое_значение». Эти значения могут быть текстовыми,
числовыми или логическими. Текстовые значения в
нижнем и верхнем регистре считаются эквивалентными.

номер_столбца – номер столбца в аргументе «таблица», из
которого возвращается соответствующее значение.
Если номер_столбца = 2, то возвращается значение из
второго столбца таблицы; если номер_столбца = 3 —
значение из третьего столбца таблицы и т.д.
Демонстрация