Как перейти в Excel

Обновлено: 06.07.2024

Иногда сложно понять, как вложенная формула вычисляет окончательный результат, потому что есть несколько промежуточных вычислений и логических тестов. Однако с помощью диалогового окна «Вычислить формулу» можно увидеть, как различные части вложенной формулы оцениваются в том порядке, в котором вычисляется формула. Например, формулу =ЕСЛИ(СРЗНАЧ(F2:F5)>50,СУММ(G2:G5),0) легче понять, если вы видите следующие промежуточные результаты:

Шаги, показанные в диалоговом окне

Описание

Сначала отображается вложенная формула. Функция СРЗНАЧ и функция СУММ вложены в функцию ЕСЛИ.

=ЕСЛИ(40>50,СУММ(G2:G5),0)

Диапазон ячеек F2:F5 содержит значения 55, 35, 45 и 25, поэтому результат функции СРЗНАЧ(F2:F5) равен 40.

=ЕСЛИ(Ложь,СУММ(G2:G5),0)

40 не больше 50, поэтому выражение в первом аргументе функции ЕСЛИ (аргумент логическая_проверка) ложно.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не оценивается, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только в том случае, если выражение истинно.

Выберите ячейку, которую вы хотите оценить. Одновременно можно оценивать только одну ячейку.

На вкладке "Формулы" в группе "Аудит формул" нажмите "Оценить формулу".

Нажмите "Оценить", чтобы проверить значение подчеркнутой ссылки. Результат оценки выделен курсивом.

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

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

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

Чтобы снова просмотреть оценку, нажмите "Перезапустить".

Чтобы завершить оценку, нажмите "Закрыть".

Если ссылка пуста, в поле "Оценка" отображается нулевое значение (0).

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

Следующие функции пересчитываются каждый раз при изменении рабочего листа и могут привести к тому, что инструмент "Вычислить формулу" выдаст результаты, отличные от показанных в ячейке: СЛУЧАЙ, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, СЕЙЧАС, СЕГОДНЯ, СЛУЧМЕЖДУ, ИНФОРМАЦИЯ и СУММЕСЛИ. (в некоторых случаях).

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

Что такое пошаговая диаграмма?

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

Рассмотрите следующие данные —

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

Пошаговая диаграмма выглядит так, как показано ниже.

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

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

Различия между линейными и ступенчатыми диаграммами

Вы можете определить следующие различия между линейной диаграммой и ступенчатой диаграммой для одних и тех же данных —

Линейная диаграмма сосредоточена на тренде точек данных, а не на точном времени изменения. Пошаговая диаграмма показывает точное время изменения данных вместе с тенденцией.

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

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

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

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

Преимущества пошаговых диаграмм

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

Процентные ставки в зависимости от времени.
Налоговые ставки и доход.
Плиты платы за электроэнергию в зависимости от используемых единиц.

Подготовка данных

Рассмотрите следующие данные —

Шаг 1. Выберите данные. Скопируйте и вставьте данные под последней строкой данных.

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

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

Шаг 4. Сдвиньте ячейки вверх при удалении. Второй набор данных выглядит так, как показано ниже.

Шаг 5. Скопируйте второй набор данных и вставьте его справа от него, чтобы получить третий набор данных.

Шаг 6. Выберите третий набор данных. Отсортируйте его от наименьшего к наибольшему значению.

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

Создание пошаговой диаграммы

Выполните шаги, указанные ниже, чтобы создать пошаговую диаграмму –

Шаг 1. Выберите третий набор данных и вставьте линейную диаграмму.

Шаг 2 — Отформатируйте диаграмму следующим образом —

Нажмите на диаграмму.

Перейдите на вкладку ДИЗАЙН на ленте.

Нажмите «Выбрать данные» в группе «Данные». Появится диалоговое окно "Выбор источника данных".

Выберите Series1 в разделе Legend Entries (Series).

Нажмите кнопку "Удалить".

Нажмите кнопку "Изменить" в разделе "Ярлыки горизонтальной оси (категории)". Нажмите "ОК".

Появится диалоговое окно "Ярлыки осей".

Шаг 3. Выберите ячейки F2:F10 в диапазоне меток оси и нажмите OK.

Шаг 4. Нажмите «ОК» в диалоговом окне «Выбор источника данных». Ваша диаграмма будет выглядеть так, как показано ниже.

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

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

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

Вот как это выглядит:

Зачем использовать пошаговую диаграмму?

Пошаговые диаграммы показывают изменения, которые происходят время от времени. Например, основная ставка США, которая основана на целевой ставке Федерального резерва, но меняется не так часто. С 2000 года он менялся всего 43 раза, тогда как обычный линейный график может меняться ежеквартально, ежемесячно или даже ежедневно.

К сожалению, в Excel нет стандартного типа пошаговой диаграммы, но мы можем создать его за несколько шагов.

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

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

Это неточная картина наших данных. Например, если вы посмотрите на диаграмму за 2003 год, вы можете предположить, что текущая ставка была ниже 6 процентов, но на самом деле она все еще составляла 6 процентов. Excel предполагает, что наши данные должны быть интерполированы с 6 процентов в 2000 году до 5 процентов в 2004 году. Мы хотим, чтобы это выглядело так, как показано на рисунке вверху этого поста. Итак, приступим к делу…

Разбивка

1) Создать новый диапазон диаграммы

2) Создать линейную диаграмму

3) Изменить тип горизонтальной оси

Шаг за шагом

1) Создать новый диапазон диаграммы

Это первый прием создания типа пошаговой диаграммы в Excel 2013.

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

Обратите внимание, что за 2004 г. есть две точки данных: одна соответствует ставке 6 (предыдущая ставка) и одна соответствует фактической ставке 5 %. Это создаст вертикальную линию в точке 2004 года от 6 до конечной ставки 5%. Вам нужно повторить этот шаг для всех точек данных, как показано на рисунке выше, в диапазоне C2:D13.

2) Создать линейную диаграмму

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

Затем выделите диапазон данных диаграммы от C2:D13.

Затем перейдите на ленту «Вставка» и выберите тип линейной диаграммы в группе диаграмм.

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

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

3) Изменить тип горизонтальной оси

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

Для этого нам нужно выбрать нашу диаграмму, затем выбрать горизонтальную ось и нажать CTRL+1 или щелкнуть ее правой кнопкой мыши и выбрать «Формат оси…».

Затем в параметрах оси выберите переключатель «Ось дат» в качестве типа оси.

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

Видеоруководство

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

Надеюсь, вам понравилось. Убедитесь, что вы усвоили этот совет по составлению пошаговых диаграмм в Excel и вернетесь к пятничному испытанию, так как оно будет включать в себя пошаговые диаграммы. Кроме того, спасибо за то, что вы являетесь поклонником и приглашаете друга и коллегу!

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

Однако, поскольку диаграмма не поддерживается в Excel, вам придется приложить некоторые усилия, чтобы построить ее самостоятельно. Чтобы сэкономить ваше время, мы разработали надстройку Chart Creator — простой в использовании инструмент для создания расширенных диаграмм Excel за считанные секунды.

В этом пошаговом руководстве вы узнаете, как создать простую пошаговую диаграмму с нуля в Excel:

Начало работы

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

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

С учетом этого, вот пример таблицы, обобщающей эффективность маркетингового канала в 2019 году:

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

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

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

Для этого выделите все значения во второй таблице и нажмите Ctrl + C, чтобы скопировать выбранные ячейки. Затем выберите первую пустую ячейку под таблицей (в нашем случае D14) и нажмите Ctrl + V, чтобы вставить данные.

Вот как это выглядит на практике:

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

Выделите всю клонированную таблицу (D1:E25).
Перейдите на вкладку "Данные".
В группе "Сортировка и фильтр" нажмите кнопку "Сортировка".
В каждом раскрывающемся меню отсортируйте по следующим параметрам:
1. В поле "Столбец" выберите "Месяц" (столбец D).
2. Для "Сортировать по" выберите "Значения" / "Значения ячеек".
3. В разделе "Порядок" выберите "Старейшие к новейшим".
Этот простой прием избавит вас от необходимости вручную вводить значения для каждой даты.

Выделите все значения из столбца CAC (столбец E) в дублирующейся таблице. Затем сдвиньте все на одну ячейку вниз, нажав Ctrl + X, чтобы вырезать данные, а затем Ctrl + V в следующей ячейке вниз (E3), чтобы вставить их на новое место.

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

Выделите ячейки D2 и E2, щелкните правой кнопкой мыши и выберите "Удалить". В диалоговом окне выберите Сдвинуть ячейки вверх. Сделайте то же самое для ячейки E25.

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

Сначала настройте новую категорию данных под названием "Ярлык" в столбце, примыкающем ко второй таблице (в нашем случае, столбце F).

Оказавшись там, чтобы создать метки, содержащие даты в паре с соответствующими значениями, вам нужно раскрыть всю мощь функций IF, CHAR и TEXT. Этот специальный коктейль из функций Excel поможет нам справиться с задачей:

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

Проще говоря, формула сравнивает значения E1 и E2 между собой, и если они не совпадают, ячейка F2 заполняется специальной меткой, отображающей соответствующий месяц и стоимость. Функция CHAR(10) остается константой и обозначает разрыв строки. В противном случае формула возвращает пустое значение.

Вот как это выглядит, когда резина отправляется в путь:

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

Все препятствия устранены. Пришло время создать простую линейную диаграмму и посмотреть, что получится.
1. Выделите все ячейки, содержащие значения даты и стоимости (D2:E24).
2. Перейдите на вкладку "Вставка".
3. Нажмите значок "Вставить линейную или диаграмму с областями".
4. Выберите «Линия».
Чудесным образом появляется пошаговая диаграмма! Однако стоит упомянуть одно предостережение. Предположим, вы решили проанализировать эффективность электронного маркетинга только за три месяца:

Как вы могли заметить, горизонтальная ось выглядит довольно неряшливо. К счастью, решить проблему проще простого. Сначала щелкните правой кнопкой мыши горизонтальную ось (метки внизу) и выберите «Формат оси».

В появившейся области задач выполните следующие действия:
1. Перейдите на вкладку "Параметры оси".
2. В разделе "Тип оси" выберите переключатель "Ось дат".
3. В разделе "Единицы" установите базовое значение "Дни/месяц/год" в зависимости от ваших фактических данных.
Теперь вернемся к нашей пошаговой диаграмме.

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

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

Нажмите правой кнопкой мыши любую метку на диаграмме и выберите "Форматировать метки данных".

Когда появится панель задач, выполните следующие действия:
1. Перейдите на вкладку "Параметры ярлыка".
2. Установите флажок "Значение из ячеек".
3. Выделите все значения в столбце Метка (F2:F24).
4. Нажмите «ОК», чтобы закрыть диалоговое окно.
5. Снимите флажок "Значение", чтобы удалить ярлыки по умолчанию.
Мы почти закончили! В качестве последнего штриха вам нужно выровнять метки в нужных местах. Метки, обозначающие рост затрат, будут размещены над линией. И наоборот, метки, отображающие снижение стоимости, будут перемещены ниже строки.

На панели задач "Формат меток данных" на вкладке "Параметры метки" прокрутите вниз до "Положение метки" и выберите вариант "Выше".

Наконец, выберите один за другим ярлыки, которые отображают переходы в CAC, и установите для каждого из них значение «Положение ярлыка» на «Ниже».

Последняя корректировка: сделайте метки жирными (вкладка «Главная» > «Шрифт») и измените название диаграммы. Ваша пошаговая диаграмма готова.

Итак, вот и все: больше информации для принятия более эффективных решений благодаря пошаговой диаграмме!

Читайте также: