Как создать таблицу данных в Excel для расчета скидок

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

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

Внедрение таблицы данных в Excel

Таблица данных – это симулятор работает по принципу: «а что если?» методом подстановки значений для демонстрации всех вероятных композиций. Симулятор следит за конфигурацией значений ячеек и показывает, как эти конфигурации воздействую на конечный итог в показателях модели программки лояльности. Таблицы данных в MS Excel дозволяет стремительно рассматривать целый набор возможных результатов модели. При настройке всего лишь 2-ух характеристик можно получить сотки композиций результатов. Опосля что избираем самые наилучшие из их.

Этот инструмент владеет неопровержимыми преимуществами. Все результаты показываются в одной таблице на одном листе.

Создание таблицы данных в Excel

Для начала нам необходимо выстроить 2 модели:

Модель бюджета конторы и условия бонусной системы. Чтоб выстроить такую табличку прочитайте предыдущею статью: как сделать бюджет в Excel.
Схему начальных данных на подобии «Таблицы Пифагора». Строчка обязана содержать количественные граничные значения для призов, к примеру, все числа от 100 до 500 кратные 50-ти. А процентные призы в приделах от 3,0% до 10,0% кратные 0,5%.

Внимание! Ячейка (в данном случае D2) пересечения строчки и столбца с заполненными значениями обязана быть пустой. Как на рисунке.

Сейчас в ячейку пересечения D2 вводим формулу такую же, как и для вычисления показателя «Маржа 2»:=B15/B8 (числовой формат ячейки – %) .

Дальше выделяем спектр ячеек D2:M17. Сейчас чтоб сделать таблицу данных следует избрать закладку «Данные» раздел инструментов «Работа с данными» инструмент «Анализ: что если» функция «Таблица данных».

Покажется диалоговое окно для введения характеристик:

Верхнее поле заполняем абсолютной ссылкой на ячейку с граничной планкой призов количества $B$2.
В нижнее поле ссылаемся на значение ячейки границ процентных призов $B$3.

Внимание! Мы рассчитываем рациональные скидки для количественной границы 1 при текущих показателях границы 2. Для расчета скидок количественной границы 2 в параметрах следует указывать ссылки на $B$4 и $B$5 – соответственно.

Жмем ОК и вся таблица заполняется показателями результатов «Маржа 2» при соответственных критериях бонусных систем. У нас перед очами сходу 135 вариантов (всем вариантам следует установить формат ячеек в %) .

Анализ что если в Excel таблицы данных

Для анализа при помощи визуализации данных добавим условное форматирование:

Выделяем приобретенные результаты результаты, а это спектр ячеек E3:M17.
Избираем инструмент: «Основная»-«Условное форматирование»-«Правила выделения ячее»-«Меж».

Указываем границы от 7% до 8% и задаем хотимый формат.

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

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

Интересно почитать: Как в эксель сделать диаграмму

Приз 2 и уровень для границы 2 аналогичным методом рассчитываем. Лишь не забудьте указать в параметрах правильные ссылки $B$4 и $B$5 – соответственно.

Таковым же самым методом мы можем выстроить матрицу для показателя «Прибыль НЕТТО». И создать для него хотимое условное форматирование. Отметить контроль прибыли при условном форматировании можно в границах 35 000-40 000.

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

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

Таблица данных в эксель

Препядствия с отображением видео:

Не видно видео примеров?

Facebook
YouTube
Pinterest
Twitter

Основная
Приемы Excel
- Начинающим
- Способности
- Хитрости
- Сводные таблицы
- Диаграммы, графики
- Макросы
- Скачать надстройку
- Справка
  - Установка и настройка
  - Команды
  - Функции (формулы)
  - Продвинутый курс MS Excel
  - Сводные таблицы MS Excel
  - Диаграммы MS Excel
  О том, что лучше один раз узреть.
  
  Что такое сводные таблицы?
  
  Приходилось ли для вас когда-нибудь попадать в такую ситуацию?:
  
  Вы весьма длительно «отрисовывали» сложную таблицу с обилием строк и столбцов и огромным содержанием данных, к примеру, таблицу — отчет по продажам в разрезе Подразделений, Городов, Менеджеров, Типов клиентов, с группировкой по кварталам, по количеству и суммам, с вычислением процентов и толикой. И когда уже все готово и «раскрашено», вы показываете эту таблицу, к примеру, руководителю, а он гласит: «Все круто, вот лишь бы добавить сюда еще разрез по Товарным категориям, совершенно было бы замечательно».
  
  Опосля этих слов, вы готовы его уничтожить, а сами готовы повеситься, потому что осознаете, что для вас на данный момент необходимо издержать еще полдня, чтоб все это воплотить.
  
  В данной статье я расскажу — как при помощи Сводных Таблиц это можно создать стремительно, практически за несколько секунд, и при всем этом не перебегать к членовредительству.
  
  Как выстроить сводную таблицу?
  
  Сводная таблица MS Excel — это мощнейший инструмент анализа данных. Данный инструмент дозволяет извлекать из огромного массива данных информацию, в каком угодно разрезе, делая при всем этом всего несколько кликов мышкой. В данной статье я постараюсь поведать на ординарном и понятном языке, по шагам, что и как необходимо созодать, чтоб получить в свое распоряжение этот необыкновенно удачный инструмент MS Excel.
  
  Как проводить вычисления в сводной таблице?
  
  Сводные таблицы способны проводить вычисления, еще наиболее сложные, чем обыкновенные суммирования данных. В данной статье я расскажу, как можно вынудить Сводную таблицу «считать».
  
  Как стремительно конвертировать таблицу в массив для сводной таблицы?
  
  Еще одна обычная неувязка для обработки данных при помощи Сводных таблиц — это «нарисованные» руками «Мега-таблицы», которые не поддаются анализу в силу специфичности их структуры. В данной статье я расскажу, как стремительно конвертировать такое «горе-творчество» в массив для построения сводной таблицы.
  
  Как стремительно выстроить сводную таблицу из отчета 1C либо SAP?
  
  Сущность трудности, я думаю, ясна всем, кто хоть раз строил Сводные таблицы на базе отчета, приобретенного из учетной системы. В одном столбце размещены разнотипные данные и Клиент, и Категория продукта, и Наименование продукта. Значения же, к примеру, размер продаж разбит по нескольким столбцам, по месяцам: Январь в собственном столбце, Февраль в собственном и так дальше.
  
  В данной статье я покажу, как можно конвертировать таковой «горе-отчет» в массив для анализа при помощи Сводной таблицы.
  
  Как отфильтровать столбец сводной таблицы?
  
  На тренингах я нередко задаю вопросец участникам: Кто понимает, как отфильтровать отдельный (не итоговый) столбец сводной таблицы по значениям? И что я слышу в ответ: Это нереально! В сводных таблицах нет таковой способности! Вопросец так старый, что люди уже перепробовали всякие варианты и не обнаружив решения пришли к выводу — нереально. Хотя решение существует.
  
  Как выстроить сводную таблицу по нескольким массивам (листам)?
  
  Обычная задачка при обработке инфы приобретенной из различных источников. Типовое решение — взять и свести все таблицы в одну. Но что созодать, когда таблиц много (к примеру, 20), либо свести их в одну нет способности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?
  
  Но решение существует! И оно не весьма сложное.
  
  Генератор примеров (массивы, таблицы, отчеты).
  
  Когда я начинал читать тренинги по MS Excel то «стер» пальцы нескончаемо создавая примеры для той либо другой темы. В особенности это касалось темы «Сводные таблицы». Решил я эту делему просто — сделал надстройку, которая мне эти примеры генерировала. Когда люди узрели это «волшебство» на следующем тренинге они весьма очень «возбудились». Оказалось, что это не только лишь хороший инструмент для тренера, да и таковой же хороший инструмент для «студента».
  
  Глава 21. Таблицы данных
  
  Это глава из книжки: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
  
  В данной главе рассматривается таблица данных, которая делает массив, как итог работы функции Таблица (эту функцию недозволено ввести в ячейки вручную). Таблица данных – это резвый и обычной метод выполнить анализ «что если» для сложных взаимосвязанных вычислений на базе формул. Эта функция дозволяет изменять одну либо две формулы входов и отображение того, что результаты. В главах 4, 5 и 10 уже были приведены примеры таблиц данных, которые использовались в сочетании с функциями базы данных.
  
  Рис. 21.1. Ячейка В6 содержит формулу, которая косвенно зависит от значения ячейки В2
  
  Скачать заметку в формате Word либо pdf, примеры в формате Excel
  
  Анализ «что если» на базе Таблицы с одной переменной
  
  На рис. 21.1 в ячейки В6 употребляется функция ПЛТ, косвенно зависящая от значения ячейки В2. Если вы измените годичную ставку ставка, функция ПЛТ обновит значение в ячейке В6. Цель состоит в том, чтоб сразу узреть, как месячный платеж будет изменяться при 5 разных годичных ставках. Хотя это можно создать методом написания формулы, функция Таблица быть может полезна по двум причинам:
  - Таблицы данных работают резвее, чем решения на базе других формул.
  - Таблицы данных проще в использовании, и время сотворения формулы сокращается.
  Чтоб сделать таблицу данных:
  1. Сделайте заглавия А9:В9. В ячейке В10 введите формулу =В6. В ячейки А11:А15 введите значения годичный ставки для анализа. Выделите спектр А10:В15.
  2. Пройдите по меню ДАННЫЕ –> Анализ «что если» –> Таблица данных, чтоб открыть диалоговое окно Таблица данных, либо нажав и удерживая кнопку Alt, поочередно нажмите Ы, Ё, Т (опосля нажатия Alt в меню будут появляться подсказки).
  3. Так как вы анализируете воздействие годичный ставки, укажите ссылку на нее в поле Подставлять значение по строчкам в (рис. 21.2). Вы гласите Таблице данных, поменять значение из ячейки В2 в процессе расчета ПЛТ и заместо него подставить в формулу значения из спектра А11:А15.
  4. Нажмите ОК.
  Рис. 21.2. Диалоговое окно Таблица данных
  
  Если вы выделите спектр В11:В15 и взглянете на строчку формул, то увидите формулу массива Таблица со ссылкой на ячейку В2. Функцию Таблица недозволено ввести с клавиатуры; она автоматом создается при использовании диалогового окна Таблица данных.
  
  Рис. 21.3. Функцию Таблица можно ввести лишь при помощи диалогового окна Таблица данных
  
  На рис. 21.4 ячейки в спектр E3:I3 содержат разные формулы, которые прямо либо косвенно ссылаются на число проданных штук (в ячейке В3). Используя Таблицу данные можно выполнить анализ «что если» для 5 формул. При этом они все основываются на одной и той же переменной, расположенной в спектре D4:D12.
  
  Рис. 21.4. Одна переменная Таблицы данных может работать на нескольких формулах; чтоб прирастить изображение кликните на нем правой клавишей мыши и изберите Открыть картину в новейшей вкладке
  
  Две переменные в Таблице данных
  
  В последующем примере задачка – отыскать малое значение по двум аспектам. Для этого употребляется Таблица данных на базе 2-ух переменных (рис. 21.5). Расположите формулу в левом верхнем углу Таблицы данных (в нашем примере – в ячейке F8), и введите настройте в диалоговом окне Таблица данных, как показано на рис. 21.5.
  
  Рис. 21.5. Таблица данных с 2-мя переменными
  
  2-ой пример (рис. 21.6) вы уже лицезрели в главе 5. Там использовалась формула массива. К примеру, в ячейке F9: =ИНДЕКС($C$2:$C$15;ПОИСКПОЗ($E9&F$8;$A$2:$A$15&$B$2:$B$15;0)). Решение на базе Таблицы данных проще, и работает резвее.
  
  Рис. 21.6. Внедрение Таблицы данных, как кандидатура ВПР по двум характеристикам
  
  Одно заключительное замечание по поводу Таблицы данных: существует параметр, который дозволяет отключить автоматическое обновление Таблиц данных, при всем этом остальные формулы будут пересчитываться автоматом. Если ваш файл «тормозит», пройдите по меню ФАЙЛ –> Характеристики, перейдите на вкладку Формулы, и изберите опцию автоматом, не считая таблиц данных (рис. 21.7). Когда вы всё же возжелаете обновить вычисления в Таблице данных, нажмите F9.
  
  Рис. 21.7. Отключение автоматического вычисления Таблиц данных
  
  1 комментарий для “Глава 21. Таблицы данных”
  
  Спасибо за перевод!
  Я не понимаю толка в ТД. Ведь она пересчитывается полностью, даже если лишь поменялась одна начальная ячейка — у таковой же таблице, но на обыденных заякоренных формулах в таком случае пересчёт будет лишь по зависимой строке либо столбцу. Ну и для чего таковая печаль-то нужна. «Ничё не понимаю»