Как сделать точку пересечения в excel?

Как сделать точку пересечения в excel?

Задали мне тут недавно вопрос. Есть график, на котором имеем 3 показателя работы производства: удельный расход материала, норматив расхода и объём производства, а по горизонтальной оси временной период (недели, например).

Исходные данные выглядят так:

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

А это наша конечная цель:

Скачать пример

ChartsIntersection.xlsx

Для начала обратим внимание, что исходный тип диаграммы — График с маркерами .

Ряды этого типа описываются так:

Заметьте, что тут нет значений координат по оси X, а есть только координаты по условной оси Y. Этим подразумевается, что условная ось X — общая для всех рядов, а также, что при их построении не используются значения, отличные от заранее фиксированного ряда оси X. Вот есть у нас периоды: 1, 2, 3 и т.д., нанесенные на ось Х, и использовать можно только их. Периода, где X=3.5 не существует для данного типа диаграммы.

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

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

Теперь нам необходимо нанести на диаграмму дополнительные ряды данных:

Точки пересечения линий расхода материала с нормами расхода. Таких точек у нас будет 3, исходя из условий примера. Эти точки нам необходимо будет расчитать, вспомнив школьный курс геометрии (см. лист Треугольники ). В файле учебного примера на листе После в таблице N3:Q5 мы получили координаты нужных точек путем нехитрых формул. Тип диаграммы Точечная в отличие от рядов с показателями работы производства.

Точки пересечения найденных значений по оси X с объемом производства. Расчитывается в таблице N8:Q10 по близким формулам. Тип диаграммы Точечная .

Пунктирные линии, которые мы рисуем из эстетических соображений. Таблица с их координатами находится в N12:P18 .

Добавляем ряды данных в соответствии с теми точками, которые мы расчитали. Щёлкните ПКМ по диаграмме и выберите » Выбрать данные. «, далее воспользуйтесь кнопкой » Добавить «.

Пример одного из рядов:

Отформатируйте линии и маркеры новых рядов данных, так как вам необходимо. Готово! 🙂

Источник:
http://perfect-excel.ru/publ/excel/grafiki_i_diagrammy/kak_najti_peresechenija_linij_grafikov_i_otobrazit_ikh_na_diagramme/6-1-0-58

Точка пересечения графиков в Excel

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

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

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


Общий принцип поиска координат следующий: для каждых двух соседних пар точек на оси абсцисс (на рисунке x1 и x2 расположены по горизонтали) проверяется условие пересекаются ли линии, то есть выполняется ли условие y1 ≥ z1 и y2 ≤ z2, или наоборот y1 ≤ z1 и y2 ≥ z2 (на рисунке y1, y2, z1 и z2 расположены по вертикали).

Пересечение двух графиков

Предположим, что у нас имеется таблица с координатами двух линий:


Построим на основе этих данных точечную диаграмму. Выделяем диапазон данных A1:K3 и на панели вкладок выбираем Вставка -> Диаграмма -> Точечная -> Точечная с прямыми отрезками.
В итоге получаем точечную диаграмму с двумя линиями:


Как видим на диаграмме линии пересеклись в 5 местах. В общем случае подобных точек может быть сколь угодно много, поэтому вручную находить каждую из них представляется достаточно трудоемким процессом.
Чтобы упростить работу и автоматизировать расчет воспользуемся средствами Visual Basic.
Переходим в редактор VBA (в панели вкладок выбираем Разработчик -> Visual Basic или воспользуемся сочетанием клавиш Alt + F11), создаем модуль и записываем в него макрос (напротив каждой строчки даются пояснения к коду):

Источник:
http://tutorexcel.ru/makrosy-vba/tochka-peresecheniya-grafikov-v-excel/

Функция ОТРЕЗОК

В этой статье описаны синтаксис формулы и использование функции ОТРЕЗОК в Microsoft Excel.

Вычисляет точку пересечения линии с осью y, используя значения аргументов «известные_значения_x» и «известные_значения_y». Точка пересечения находится на оптимальной линии регрессии, проведенной через точки, заданные аргументами «известные_значения_x» и «известные_значения_y». Функция ОТРЕЗОК используется, если нужно определить значение зависимой переменной при нулевом значении независимой переменной. Например, с помощью функции ОТРЕЗОК можно предсказать электрическое сопротивление металла при температуре 0°C, если имеются данные измерений при комнатной температуре и выше.

Аргументы функции ОТРЕЗОК описаны ниже.

Известные_значения_y — обязательный аргумент. Зависимое множество наблюдений или данных.

Известные_значения_x — обязательный аргумент. Независимое множество наблюдений или данных.

Аргументы могут быть числами либо содержащими числа именами, массивами или ссылками.

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

Если аргументы «известные_значения_y» и «известные_значения_x» содержат разное количество точек данных или вовсе не содержат точек данных, функция ОТРЕЗОК возвращает значение ошибки #Н/Д.

Уравнение для точки пересечения линии линейной регрессии a с осью y имеет следующий вид:

где наклон b вычисляется следующим образом:

где x и y — средние значения выборок СРЗНАЧ(известные_значения_x) и СРЗНАЧ(известные_значения_y).

Алгоритм, лежащий в основе работы функций ОТРЕЗОК и НАКЛОН, отличается от алгоритма, на котором основана функция ЛИНЕЙН. Результаты вычислений по этим алгоритмам могут не совпадать в случае неопределенных и коллинеарных данных. Например, если точками данных аргумента «известные_значения_y» являются нули, а аргумента «известные_значения_x» — единицы, то справедливо указанное ниже.

Пересечение и наклон возвращают #DIV/0! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!. Алгоритмы «КОНСТ» и «наклон» предназначены для поиска одного и только одного ответа, и в этом случае может быть несколько ответов.

Читайте также  Как создать смешанную диаграмму в Excel - Трюки и приемы в Microsoft Excel

Функция ЛИНЕЙН возвратит нулевое значение. Алгоритм, используемый в функции ЛИНЕЙН, предназначен для возврата правдоподобных результатов для коллинеарных данных, а в этом случае может быть найдено по меньшей мере одно решение.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Источник:
http://support.microsoft.com/ru-ru/office/%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D0%BE%D1%82%D1%80%D0%B5%D0%B7%D0%BE%D0%BA-2a9b74e2-9d47-4772-b663-3bca70bf63ef

Рассмотрим приемы, с помощью которых можно создать полный перечень пересечения объектов в Excel (т. е. выполнить операциюи CrossJoin).

Предложим три варианта:

1) с использованием формул СТРОКА(), ОКРУГЛВВЕРХ(), ОСТАТ(), ИНДЕКС();

2) с помощью инструментов интерфейса Power Query;

3) с помощью DAX-запросов к таблицам в модели данных Power Pivot.

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

Рассмотрим простой пример.

Имеем выгрузку продаж в кг за день по категории «Молочная продукция» в пяти магазинах (рис. 1). При этом у нас есть пять магазинов — таблица «Магазин» и пять единиц товаров — таблица «Товар».

Количество строк в отчете «Выгрузка» — 23, а полное пересечение магазинов и товаров — 25 строк (5 магазинов × 5 товаров). Это значит, что два товара в нашем случае имеют нулевые продажи за день, поэтому не попали в выгрузку.

Для магазина «Купеческий» нет категории «Молоко», для магазина «Фестивальный» — позиции «Йогурт».

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

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

Построим сводную таблицу, поместив в область строк поле «Магазин», в поле значений — Сумму по полю Продажи, кг, Среднее по полю продажи, кг, Количество по полю Продажи, кг (рис. 2).

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

Мы знаем, что в нашей таблице перечислены не все категории, которые продавались в магазине, а значит, среднее значение по магазинам «Купеческий» и «Фестивальный» завышены.

Чтобы рассчитать среднее относительно всех категорий, нам необходимо сумму продаж разделить на пять (полное количество категорий) — столбец «Правильное среднее»на рис. 2.

Столбец «Отношение» на рис. 2 — это разница между столбцом «Правильное среднее» и «Среднее по полю продажи».

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

Полный перечень пересечений также называется операцией CROSSJOIN.

Способ 1

CrossJoin двух объектов с помощью формул

Мы знаем, что нам нужно создать полный список пересечений магазинов и товаров и у нас есть пять магазинов и пять товаров. Полный перечень — это всегда произведение уникальных значений каждого объекта. В нашем случае это 25 (рис. 3).

Если бы надо было добавить еще пять дней к перечню, то перечень состоял бы из 25 × 5 = 125 строк и т. д.

Порядок расчета:

1. Начиная с ячейки A2 пронумеруем строки от 1 до 25. Автор использует формулу:

Функция СТРОКА() возвращает порядковый номер текущей строки.

2. В столбце B указан порядковый номер магазина. Нам необходимо сделать так, чтобы для порядковых номеров столбца А 1–5 был указан номер 1, для порядковых номеров 2–10 — номер 2 и т. д. Для этого запишем в ячейку B2 формулу:

Функция ОКРУГЛВВЕРХ() округляет число до ближайшего сверху с заданной точностью. Мы делим число в столбце А на 5 и округляем до целого.

3. В столбце C указан порядковый номер товара. Нам необходимо сделать так, чтобы в данном столбце цифры от 1 до 5 шли в хронологическом порядке, возвращаясь после пяти к единице и т. д. Для этого запишем в ячейку C2 формулу:

Функция ОСТАТ() возвращает остаток от деления (в нашем случае — деления на 5). Если остаток равен нулю, то возвращает 5.

4. Теперь мы имеем порядковые номера магазинов — столбец B и порядковые номера товаров — столбец C. Чтобы добавить магазины в столбец D, воспользуемся функцией ИНДЕКС().

Для подстановки магазинов в столбце D функция будет выглядеть следующим образом:

Для подстановки товаров в столбце E:

Столбцы D и E — это тот результат, к которому мы хотели прийти. Чтобы подставить продажи из первоначальной таблицы, можно воспользоваться функцией ВПР() по связке столбцов либо СУММЕСЛИМН(), где условиями будут «Магазины» и «Товары».

П. М. Чеглаков,
ведущий аналитик АО «Тандер»

Материал публикуется частично. Полностью его можно прочитать в журнале «Справочник экономиста» № 11, 2018.

Источник:
http://www.profiz.ru/se/11_2018/pereschen_v_CrossJoin/

Help. Вопрос по Excel.

Сообщение от grin:
Здраствуйте! Помогите решить задачку! 🙂 В Excel’е создан график на котором изображены 2 функции: y=cos(x) и y=x. Косинусоида и прямая параллельная оси x.

Саму задачу решить не помогу, но тут что-то не то. По-моему, y=x это прямая, наклоненная к оси OX под 45 градусов.
А в общем тут вроде как систему уравнений составить надо. Общие значения x и y этой системы и есть решение задачи. Я не знаю, решает ли Эксель системы уравнений.

Сообщение от :
По-моему, y=x это прямая, наклоненная к оси OX под 45 градусов.

Нет, y=x это прямая параллельная оси x. Т.е. в Excel’e задаём значения x, например:[-20,20] с шагом 0,5. в других ячейках подсчитываем значения y=cos(x) и (например)y=0,65. В результате у нас будет 3 столбика. по ним построим диаграмму(в виде графика). и, вот, я никак не могу додуматься как найти точки x пересечения графиков. (y=0,65!). Хотя возникали мысли посчитать по прямоугольному триугольнику, те взять две точки находящиеся выше и ниже точки пересечения графиков, построить что-то типа триугольника и как-нить по теореме подсчитать, а вот как, до сих пор понять не могу(но интуитивно чуствую что можно :-)). У вас ещё нет каких-нить мыслей по этому поводу? (или ссылочек :-))

Сообщение от grin:
Нет, y=x это прямая параллельная оси x.

Эх. двойка вам, молодой челоквек. Прямая, параллельная оси х, описывается функцией y=const

Сообщение от The_Mercy_Seat:
А вообще для решения уравнений ничего нельзя придумать лучше программ символьной математики. См. в даунлоаде wsMaxima — опенсорсный аналог Maple.

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

Сообщение от Румын:
Ой, тяжко она у меня идет, после маткада-то. Совсем мозги набекрень выворачивать надо. 😥

Да она и не предназначена заменой маткаду — скорее Мэплу. Это символьной математики пакет, для получения ответов символьно, где это возможно. Например для уравнения a*x+b=0 =>x=-b/a и т.д.
«Маткад» — программа оформительско-расчетная. С точки зрения математики профессиональной — несерьезная (как и Эксель с ООоКалком).
«Мэпл», «Максима» и «Математика» — преимущественно символьные пакеты.
«Матлаб», «Октава» — преим. используются как мощные численные пакеты.
Маткад можно заменить Нормкадом, а лучше сразу — VisualStudio2005 Express (или просто VisualBasic.NET например) — если бесплатный хотите (ссылку давал в опросе про программирование по моему). Вам там нужно будет освоить некоторые приемы и несколько видов контролов (RTB в перую очередь).

Читайте также  Делаем профессиональные отчёты в Excel: пять простых советов, Медиа Нетологии: образовательная платформа

Сообщение от Михаил_М:
Решите уравнение, в нем будет параметр (константа) и пусть эксель подставляет значение этой константы в формулу решения. Даже без графиков обойтись можно :-).

Сообщение от Румын:
Кстати о нормкаде. Наверное стоит мне в его сторону посмотреть, ибо уже куплен 🙂
Не пробовал я в нем еще абсолютно новые расчеты с нуля создавать.

Нормакад это эрзац-язык как и Маткад. Это, пардон, как резиновая кукла вместо бабы — вроде как просто на первый взгляд но ограниченно-эффективно. С basic-ом вы провозитесь до начально-достаточного уровня всего раза в 1,5 больше, зато у вас появится возможность постоянно расти в нем по жизни, и перевести на него все свои задачи. Например, чтобы нужные вам узлы и детали типовые генерились сразу в dxf. Или картинка к расчету произвольно менялась. Или связь с другими приложениями. Программы можно будет переслать, обменять, взять на новую работу ибо работают только за счет фрейма, а он бесплатен относительно Винды.

Сообщение от :
Нормакад это эрзац-язык как и Маткад. Это, пардон, как резиновая кукла вместо бабы — вроде как просто на первый взгляд но ограниченно-эффективно. С basic-ом вы провозитесь до начально-достаточного уровня всего раза в 1,5 больше, зато у вас появится возможность постоянно расти в нем по жизни, и перевести на него все свои задачи. Например, чтобы нужные вам узлы и детали типовые генерились сразу в dxf. Или картинка к расчету произвольно менялась. Или связь с другими приложениями. Программы можно будет переслать, обменять, взять на новую работу ибо работают только за счет фрейма, а он бесплатен относительно Винды.

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

Сообщение от Sleekka:
(алгоритмы у них достаточно приличные).

Ага, если с запятой не промазали. 🙂
черт, почти убедили 8)
Вот появится у меня (я в это верю!) свободное время, посмотрю на вба пристально, авось чего и выйдет 🙄

Сообщение от Румын:

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

Сообщение от Sleekka:
Все абсолютно верно, но есть одно но, в нормкаде много расчетов сделано, можно эскпортировать в бейсик а дальше уже кумекать дорабатывать, (алгоритмы у них достаточно приличные).

Будет очень здорово, если вы экспортнете пару-тройку нужных расчетов и выложите сии кирпичи в даунлоад. Но вообще мне видится тут какой то подвох. С чего владельцам Нормкада выдавать свои коды в открытый вид — это же практически чистая опенсорсность? Так любой моментом их скоммуниздит, перекомпилирует и будет продавать под своей маркой 🙂
Как правило экспорт-импорт откуда то куда то всегда столь некорректен, или сопровождается какими то «но», что проще сделать все в одной программе и не парится (у программистов это правило звучит примерно «проще два раза написать свою программу, чем разобраться в чужой»)

Сообщение от The_Mercy_Seat:
Ну вам, поскольку вы к Маткаду привыкши действительно невыгодно переходить ни на что. А сколько он стоит? Или есть ли сравнительно нормальные заменители его? тут как то поднимался этот вопрос и была какая то программулина (вроде даже отечественного автора), но дюже сырая. Не поверю, чтобы аналогов за бугром не было.

На софткее 13-я версия стоит 1412 уёв. Ни купят нам ее скорее всего, и так директор в слезах ходит сколько бабла за лицензионное программное обеспечение отвалил (на самом деле не мало 🙄 ) А нормкад уже куплен давно, ибо дюже дешевый и лицензия сетевая есть, не надо на всех покупать.
Если уж на что-то директора и раскрутят, так это на Математику, к ней у нас главспецы приучены. И то врядли, на СПДС-то уже жмется. 😈

А про нормкад. Выкладываю результаты экспорта одного из пунктов, если сильны в ВБ, скажите, насолько это полезно 🙂
Тока никому! [sm3537]

ЗЫ Упс, файлик забыл прицепить.
[ATTACH]1175624445.rar[/ATTACH]

Сообщение от Румын:
На софткее 13-я версия стоит 1412 уёв. Ни купят нам ее скорее всего, и так директор в слезах ходит сколько бабла за лицензионное программное обеспечение отвалил (на самом деле не мало 🙄 ) А нормкад уже куплен давно, ибо дюже дешевый и лицензия сетевая есть, не надо на всех покупать.
Если уж на что-то директора и раскрутят, так это на Математику, к ней у нас главспецы приучены. И то врядли, на СПДС-то уже жмется. 😈

Вот -вот. Нельзя так с директорами. А все потому что люди к пропиетарному софту привыкши. Нет чтобы просто, научиться набирать к примеру sqr(x^2+y^2) — обязательно надо надавить на кнопочки типа «корень» или «квадрат». Вот и мучаемся, как при старом режиме 🙂
В гугле полагаю надо искать. Вот набрал «open-source analog Mathcad» выскочило Math system in MathCad style = Gap for Unix . Раз «for Unix» есть то с 90% вероятностью этот Гэп и под винду есть тоже. Иногда долго приходится искать бесплатные аналоги спецПО — все новые и новые находишь чуть не каждый день. В рунете в основном бесполезно — голладцы, французы, немцы и в меньшей степени американцы склонны к таким вещам.

Сообщение от grin:

Сообщение от :
По-моему, y=x это прямая, наклоненная к оси OX под 45 градусов.

Нет, y=x это прямая параллельная оси x.

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

Источник:
http://forum.dwg.ru/showthread.php?t=10817

gymn19.wordpress.com

Уроки по информатике учителя Халиковой Д.Н.

Построение диаграмм в Excel

Задание 1. Построение треугольника

Построить треугольник, заданный координатами вершин, и точку с заданными координатами.

Порядок выполнения:

1.1 Переименовать Лист 1 в Треугольник и точка.

1.2 Занести в ячейки B2:E3 координаты вершин треугольника, повторив координаты 1-й вершины дважды. В ячейки B6:B7 занести координаты точки.

1.3 Построить точечную диаграмму для диапазона A2:E3.

1.4 Добавить ряд данных с именем Точка для значений координат точки.

1.5 Изменить координаты точки так, чтобы точка лежала внутри треугольника.

Задание 2. Построение графиков тригонометрических функций

В одном графическом блоке построить графики функций Y1 = sin(X) и Y2 = sin2 (X) для Х, изменяющегося от -6,5 до 6,5 с шагом 0,5

2.1 Переименовать Лист2 в Тригоном. функции.

2.2 В ячейки, начиная с B1, занести значения X с шагом 0,5 (на рисунке приведен только фрагмент таблицы).

2.3 В ячейки нижележащих строк занести формулы для вычисления Y1 и Y2.

Читайте также  Добавление надписи на диаграмму - Служба поддержки Office

2.4 Построить графики функций. Подписать диаграмму «Графики тригонометрических функций»

2.5 Добавить в таблицу значения функции Y3 = cos2 (X) для тех же значений аргумента.

2.6 Добавить в графический блок график функции Y3.

Задание 3. Точка пересечения прямых

Две прямые заданы уравнениями Y1 = a1*x + b1 и Y2 = a2*x + b2. Найти координаты точки пересечения прямых Xp и Yp. Для выполнения задания построить графики прямых и нанести на график точку пересечения.

3.1 Переименовать Лист 3 в Точка пересечения прямых.

3.2 Занести в ячейки A2:A7 обозначения коэффициентов и координат точек пересечения.

3.3 Присвоить имена ячейкам B2:B7, используя в качестве имен значения из левого столбца.

3.4 Вычислить координаты точки пересечения по формулам

3.5 Создать таблицу значений Y для заданных прямых Y1 = a1*x + b1 и Y2 = a2*x + b2 (для построения прямой достаточно задать две точки): задать значения X в ячейках B10:C10, вычислить значения Y1 и Y2 в ячейках B11:C11, B12:C12, подставляя заданные значения x в уравнения прямых.

3.6 Построить графики прямых и нанести точку пересечения.

Источник:
http://gymn19.wordpress.com/2017/03/06/%D0%BF%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%B5%D0%BD%D0%B8%D0%B5-%D0%B4%D0%B8%D0%B0%D0%B3%D1%80%D0%B0%D0%BC%D0%BC-%D0%B2-excel/