Как в Excel Присвоить Место в Зависимости от Показателей • Группируем столбцы

Как в Excel Присвоить Место в Зависимости от Показателей

На этом шаге мы рассмотрим создание таблиц подстановки.

При работе с моделью «что-если» в определенный момент времени можно использовать только один сценарий (только один набор данных). Но что если необходимо сравнить результаты нескольких сценариев? Вот несколько вариантов решения подобной проблемы:

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

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

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

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

Рассмотрим пример создания таблицы подстановки, в которой бы отражались значения, рассчитанные по формулам, находящимся в ячейках Размер ссуды, Месячная плата, Общая сумма, Общая сумма комиссионных , при изменении ставок от 7% до 9% с шагом 0,25%. На рисунке 3 показана заготовка таблицы подстановки для описанного примера. Строка 2 состоит из ссылок на соответствующие ячейки с формулами.

Рис.3. Подготовка к созданию таблицы подстановки с одним входом

Чтобы создать таблицу подстановки, выделите диапазон ячеек (для рассматриваемого примера G2:K11 ), а затем выберите команду Данные | Таблица подстановки . Появится диалоговое окно, показанное на рисунке 4.

Вам необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7 ). Щелкните на кнопке OK , и Excel заполнит таблицу соответствующими результатами (рис. 5).

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

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

Таблица подстановки с двумя входами позволяет отобразить на экране результаты расчетов при изменении двух входных параметров. Макет для этого типа таблицы показан на рисунке 6.

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

Рис.7. Пример расчета чистой прибыли после проведения рекламной акции

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

  • Стоимость печатных материалов . Стоимость печати одного рекламного буклета. Цена изменяется в зависимости от количества: 0,20 — если количество экземпляров не превышает 200000; 0,15 — от 200001 до 300000 экземпляров; 0,10 — если больше 300000. Стоимость отпечатаннх материалов (в зависимости от их количества) определяется по фомуле:
    =ЕСЛИ(Разослано_материалов .
  • Почтовые расходы . Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.
  • Число респондентов . Количество ответов, которое предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для этой ячейки следующая:
    =Процент_ответевших*Разослано_материалов .
  • Доход на одного респондента . Это фиксированное значение. Компании известно, что за каждый заказ она получит прибыль 22.
  • Суммарный доход . Суммарный доход вычисляется по простой формуле, в которой величина дохода, полученного от одного заказа, умножается на количество заказов:
    =Доход_на_одного_респондента*Число_респондентов .
  • Суммарные расходы . По формуле, находящейся в этой ячейке, вычисляются суммарные расходы на рекламу, в которую входит стоимость печатных материалов и почтовых услуг:
    =(Стоимость_печатных_материалов+Почтовые_расходы)*Разослано_материалов .
  • Чистая прибыль . Определяется как разность суммарных доходов и суммарных расходов.

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

По данным таблицы подстановки с двумя входами можно построить трехмерные диаграммы (рис. 9).

На следующем шаге мы рассмотрим анализ данных с помощью средства Диспетчер сценариев .

Поиск решения в Excel: пример использования функции для решения задачи с неизвестными параметрами
Ограничения — условия, которые необходимо учесть при оптимизации функции, называющейся целевой. К ним относятся размеры инвестирования, срок реализации проекта или объем покупательского спроса. В нашем случае — количество дней и число работников.
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Для того, чтобы узнать Время на выполнение заказа , нужно Число заготовок, передаваемых в работу разделить на Производительность. Если же вы хотите что-то уточнить, я с радостью помогу!
Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Excel выпадающий список в зависимости от значения

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

По завершении этих манипуляций наверху или слева от рабочей области Excel появится линия с пиктограммой в виде минуса. Это графическое обозначение соединённых категорий. Луч «охватывает» данные объекты и показывает, где они находятся.

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Чтобы отобразить стрелку трассировки к каждой ячейке, зависимой от активной ячейки, на вкладке формулы в группе Зависимости формул нажмите кнопку Зависимые. Если же вы хотите что-то уточнить, я с радостью помогу!
Сразу же на панели инструментов появляется новая группа вкладок для работы с графиками, в которых есть функции для настройки визуальных параметров (цветовая гамма, размеры и форма точек на кривой, наличие табличных данных или легенды и т.п.).

Как определить зависимость в excel

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

Открывается окошко ещё меньше предыдущего. В нём нужно указать координаты в таблице тех значений, которые должны отображаться на оси. С этой целью устанавливаем курсор в единственное поле данного окна. Затем зажимаем левую кнопку мыши и выделяем всё содержимое столбца «Год», кроме его наименования. Адрес тотчас отразится в поле, жмем «OK».

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Признаюсь, что в предложенном мной варианте домашнего бюджета я ограничиваюсь только категорией, поскольку для меня такого разделения расходов вполне достаточно название расходов доходов рассматривается как подкатегория. Если же вы хотите что-то уточнить, я с радостью помогу!
Первым был список всех категорий продуктов, второй – список всех продуктов, находящихся в выбранной категории. Поэтому я создал выпадающий список, зависимый от выбора, сделанного в предыдущем списке (здесь вы найдете материал о том, как создать два зависимых раскрывающихся списка).

Как строить выпадающие списки в Excel | Бизнес-школа Laba (Лаба)

    Выделяем таблицу и переходим во вкладку «Вставка». Кликаем по кнопке «График», которая имеет локализацию в группе «Диаграммы» на ленте. Открывается выбор различных типов графиков. Для наших целей выбираем самый простой. Он располагается первым в перечне. Клацаем по нему.

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

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры. Если же вы хотите что-то уточнить, я с радостью помогу!
В эту таблицу необходимо ввести категорию и рядом с ней ее подкатегории. Имя категории должно повторяться столько раз, сколько есть подкатегорий. Очень важно, чтобы данные были отсортированы по столбцу Категория. Это будет чрезвычайно важно, когда позже будем писать формулу.

Функция ВПР (VLOOKUP) в Excel: пошаговая инструкция с примерами

  • ОтделСотрудники отдела. При выборе отдела из списка всех отделов компании, динамически формируется список, содержащий перечень фамилий всех сотрудников этого отдела (двухуровневая иерархия);
  • Город – Улица – Номер дома. При заполнении адреса проживания можно из списка выбрать город, затем из списка всех улиц этого города – улицу, затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

Наводим курсор на нижний правый край элемента, в который ранее была записана формула. При этом с курсором должно произойти преображение. Он станет черным крестиком, который носит наименование маркера заполнения. Зажимаем левую кнопку мыши и тащим этот маркер до нижних границ таблицы в столбце «Y».

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

Как в Excel Присвоить Место в Зависимости от Показателей

  • Тип данных. Можно выбрать тип данных, который будет содержать список: диапазон целых или действительных чисел, текстовые выражения, даты и время. Можно задать ограничения по длине текста и различные формулы.
  • Игнорировать пустые значения — данный пункт означает, что Excel не будет проверять на правильность ячейки, в которых содержатся пустые значения.
  • Список допустимых значений. Этот флажок отображается только в том случае, если выбран тип данных «Список». Если убрать флажок, в ячейке будет происходить проверка на соответствие значений списку, но раскрывающее поле с выпадающими значениями будет отсутствовать.
  • Значение. Работает только с теми типами данных, в которых можно задать ограничения по числам или датам.
  • Источник. Здесь перечисляются значения для проверки данных или задается формула.
  • Распространить изменения на другие ячейки с тем же условием. Excel здесь находит все ячейки в книге, которые ссылаются на идентичное по свойствам условие и изменяет их согласно новых параметров. В случае, если флажок не будет установлен, условие будет изменено только для выделенных ячеек в таблице.
  • Очистить все — удаляет установленную проверку данных с выделенных ячеек.

Если авто-структура не работает должным образом, выберите обычный способ: он может оказаться проще и больше соответствовать информации, содержащейся в таблице Excel. Команда не может использоваться на листах с совместным доступом, при сохранении в htm/html или при защите документа, в последнем случае пользователь-получатель не сможет сворачивать/разворачивать ряды.

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Допустим, в таблице, в которой находятся данные по продажам костюмов, есть столбцы Наименование товара , Размер , Количество на складе и Цена. Если же вы хотите что-то уточнить, я с радостью помогу!
Порядок добавления единиц не важен. Главное, чтобы сохранялась развёртка верхней единицы. В конкретном примере порядок создания структуры имел вид: группировка месяцев, после которой они были свёрнуты и сгруппированы уже кварталы. Таким образом, удалось добиться условной единицы «полугодие».
Как в Excel Присвоить Место в Зависимости от Показателей • Группируем столбцы

Группировка и разгруппировка данных в Excel. Как сделать уровни группировок в excel?

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

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Если же вы хотите что-то уточнить, я с радостью помогу!
Сейчас будет весело. Создавать списки мы умеем – только что это сделали для категории. Только единственный вопрос: «Как сказать Excelю выбрать только те значения, которые предназначены для конкретной категории?» Как вы, наверное, догадываетесь, я буду использовать здесь рабочую таблицу и, конечно же, формулы.

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

  1. Щелкните значение в сводной таблице правой кнопкой мыши и выберите команду Группировать.
  2. В окне Группировка установите флажки Начиная с и Заканчивая и при необходимости измените значения.
  3. В разделе С шагом выберите период времени. …
  4. Нажмите кнопку ОК.

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

Оставить отзыв

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