Квадратное уравнение (строим график функции в EXCEL)
Построим график функции y=a*x^2+b*x+с (квадратное уравнение). Также рассчитаем дискриминант, найдем корни уравнения, координаты точки экстремума (максимума или минимума). Сделаем форму для сдвига и отражения графика с помощью элементов управления формы.
Построим график функции y=a*x^2+b*x+с в диаграмме типа Точечная с гладкими кривыми. (см. файл примера лист График).
Укажем на графике точку экстремума (для этого в диаграмме создан дополнительный ряд, состоящий из 1 точки).
Коэффициенты а, b, с введем в отдельные ячейки, чтобы можно было быстро построить нужный график.
Строить график в диаграмме типа Точечная с гладкими кривыми проще, чем строить его в диаграмме типа График (см. статью График vs Точечная диаграмма в MS EXCEL ), т.к. точку пересечения вертикальной осью горизонтальной оси можно настроить только по номеру категории (порядковый номер точки), а не по значению x (получается, что вертикальная ось y не проходит через х=0, что не удобно).
Диаграмма также может построить график, отраженный относительно горизонтальной оси (проходящей через точку экстремума).
Сдвиг графика
В файле примера на листе Сдвиг-Отражение сделана форма для сдвига графика по координатам х и y с помощью Элементов управления формы .
Эта форма позволяет быстро рассчитывать коэффициенты нового квадратного уравнения, полученного при сдвиге.
Форма также позволяет рассчитывать коэффициенты квадратного уравнения, полученного при горизонтальном отражении ранее сдвинутого графика (с его построением на диаграмме).
СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .
Вафельная диаграмма в Excel
В открывшемся окне выбираем тип правила Форматировать только ячейки, которые содержат (Format only cells than contains) , чуть ниже в выпадающем списке выбираем вариант Меньше или равно (Less or equal) и указываем рядом ссылку на ячейку с исходным значением ($B$2). Задаём цвет заливки, нажав на кнопку Формат (Format) :
После нажатия на ОК получаем почти готовую диаграмму:
Для пущей красоты можно скрыть значения процентов в ячейках таблицы, задав для неё в окне Формат ячеек (Format cells) на вкладке Число (Number) пользовательский формат, состоящий из трёх подряд точек с запятой:
Затем на вкладке Вставка (Insert) жмём кнопку WordArt и, выбрав приглянувшийся дизайн надписи и вставив её поверх нашей таблицы, вводим в строку формул знак «равно» и делаем ссылку на ячейку с исходным значением:
Получаем надпись, текст которой автоматически обновляется из ячейки B2, отображая поверх нашей «вафли» текущее значение нашего параметра.
Способ 2. Вафельная диаграмма из линейчатой
Этот способ создания вафельной диаграммы основан на выпиливании её из стандартной линейчатой диаграммы, встроенной в Excel (горизонтальная гистограмма). Однако, сначала нам придется подготовить таблицу — источник данных для будущей диаграммы.
Начинаем с создания процентного ряда от 0% до 100% с шагом 10% (диапазон A4:A13). Затем добавляем к нему столбец с вычислением разности между значениями ряда и нашим исходным значением, которое нужно визуализировать из ячейки B2:
Затем добавляем столбец с вложенными друг в друга функциями ЕСЛИ (IF) , чтобы реализовать следующую логику:
- отрицательные значения заменяем на 0
- значения больше 10% на 10
- оставшиеся выводим как есть, но добавляем умножение на 100 (т.к. проценты в Excel представляют из себя числовые значения от 0 до 1, а нам нужно на выходе получить числа от 0 до 10)
Выделяем последний вычисленный столбец (диапазон C4:C13) и строим по нему линейчатую диаграмму на вкладке Вставка (Insert) :
. и получаем вот такую картину:
Осталось сделать эту диаграмму более похожей на «вафлю». Для этого:
-
Щёлкаем правой кнопкой мыши по синим столбцам, выбираем опцию Формат ряда данных (Format data series) и убираем Боковой зазор (Gap width) до нуля. Столбики становятся максимально широкими и сливаются в единое целое:
Дополнительно и при желании, можно настроить ещё цвет линий сетки, сделав их более яркими.
Вот и всё — вафля готова 🙂
Пара нюансов и лайфхаков вдогон:
- Если после создания диаграммы захочется скрыть вспомогательную таблицу A4:C13, оставив только ячейку B2 с исходным значением, то лучше щёлкнуть по диаграмме правой, затем команда Выбрать данные — Скрытые и пустые ячейки (Select data source — Hidden and Empty cells) и включить флажок Показывать данные в скрытых строках и столбцах (Show data from hidden rows and columns) . Иначе после скрытия исходных данных пропадет и диаграмма.
- Если такой вид диаграммы вам придётся делать ещё неоднократно, то имеет смысл сохранить созданную диаграмму как шаблон, щёлкнув по ней правой кнопкой мыши и выбрав команду Сохранить как шаблон (Save as Template) :
После этого вафельную диаграмму (по подготовленной таблице!) можно будет создать через выбор в стандартном окне типов диаграмм Excel, найдя её в разделе Шаблоны (Templates) :
Вот и всё. Теперь вы сможете делать вафли не только на кухне, но и в Excel 🙂
Ссылки по теме
- Что умеет условное форматирование в Excel
- Диаграммы «план-факт» в Microsoft Excel
- Имитация гистограмм значками с функциями ПОВТОР и СИМВОЛ
Поверхностная диаграмма в Excel и пример ее построения
Принцип построения поверхностных диаграмм в Excel можно сравнить с рельефными картами. Где положение пункта определяется не только долготой и широтой, но и третьей величиной – высотой.
Данное сравнение поможет понять, как создать на первый взгляд сложную поверхностную диаграмму в Excel и как ее использовать.
Построение поверхностной диаграммы в Excel
Практический пример применения и создания поверхностной диаграммы в Excel.
Напряжение излучения в квадратной комнате определено формулой z=[sin(x)*y] 2 . Начало осей координат расположено центру комнаты.
Визуально сложно определить место в комнате, где наиболее интенсивное излучение. Создадим графическое представление ситуации, которое будет читабельно даже для дилетантов.
Сначала выполним все необходимые расчеты и вычисления в таблице. А поверхностную диаграмму построим на основе уже полученных данных.
- Заполните таблицу как указано на рисунке.
- Выделите диапазон B2:L12 так, чтобы B2 активной ячейкой выделенного диапазона (выделяйте с верхнего левого угла B2 диапазона к нижнему правому углу до ячейки L12).
- Не снимая выделения, введите в строку формул: =(SIN(B$13)*$A2)^2 и нажмите комбинацию клавиш CTRL+Enter. Обратите внимание, как мы используем в аргументах формулы смешанные ссылки на ячейки.
- Между столбцами A и B вставьте новый столбец и заполните его вторую ячейку формулой: =» «&A2 (не забудьте поставить пробел между кавычками). Скопируйте эту формулу во все ячейки столбца до 12-ой строки (то есть заполните этой формулой диапазон ячеек B2:B12).
- Выделите диапазон: B2:M12 и выберите инструмент: «Вставка»-«Диаграммы»-«Другие»-«Поверхность».
Теперь четко видно на диаграмме что наибольшая интенсивность излучения находится в углах комнаты.
Чтобы правильно настроить горизонтальную ось X, щелкните по диаграмме, чтобы ее активировать и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».
В появившемся окне «Выбор источника данных» в правом разделе «Подписи горизонтальной оси (категории)» щелкните на кнопку «Изменить».
В окне «Подписи оси» измените значение, выделив диапазон ячеек C13:M13 и на всех диалоговых окнах нажмите ОК.
Краткое описание примера
Стоит отметить! При создании поверхностной диаграммы мы изменили числовые значения столбца A в текстовые, поместив их в столбец B с помощью формулы . Если бы мы этого не сделали, то Excel воспринял бы эти числовые значения (столбца A) как данные для построения поверхностной диаграммы, а не как подписи данных.
Если бы мы просто присвоили текстовый формат для значений столбца A (вместо дополнительного столбца с формулами), тогда мы просто получили бы ошибку при расчетах.
Вот в такой нехитрый способ мы красиво сделали подписи для осей диаграммы и не допустили ошибок при расчетах.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Как создать полулогарифмический график в Excel
Полулогарифмический график — это тип графика, в котором используется логарифмическая шкала по оси Y и линейная шкала по оси X.
Этот тип графика часто используется, когда значения переменной y имеют гораздо большую изменчивость по сравнению со значениями переменной x.
Это часто происходит в наборах данных в области финансов, экономики, биологии и астрономии среди других областей.
В следующем пошаговом примере показано, как создать полулогарифмический график в Excel для заданного набора данных.
Шаг 1: введите данные
Во-первых, давайте введем значения для поддельного набора данных:
Шаг 2: Создайте диаграмму рассеяния
Затем выделите значения данных:
На верхней ленте нажмите « Вставить ».Затем щелкните первый вариант в разделе «График рассеяния »:
Автоматически отобразится следующая диаграмма рассеяния:
Из графика видно, что значения переменной y имеют гораздо более высокую изменчивость, чем значения переменной x.
Это означает, что рекомендуется преобразовать ось Y в логарифмическую шкалу, чтобы более эффективно визуализировать значения Y.
Шаг 3: Измените шкалу оси Y
Затем щелкните правой кнопкой мыши ось Y. В появившемся раскрывающемся меню нажмите « Формат оси» :
В появившемся окне в правой части экрана поставьте галочку рядом с Логарифмическая шкала :
Ось Y будет автоматически преобразована в логарифмическую шкалу:
Ось x остается на линейной шкале, но ось y была преобразована в логарифмическую шкалу.
Обратите внимание, насколько проще интерпретировать значения y на этом графике по сравнению с предыдущим графиком.