В деревянном коне из пересказа Куна сидят вооружённые греки. Троянцы тянут его через пролом в стене: толчок, звон оружия, ещё толчок. Внутри буквально гремит объяснение происходящего. Но снаружи уже решили, что получили чудесный подарок, и этот вывод удаётся пронести в город вместе с конём.
У домашней таблицы голос тише. Она показывает красивое число, а мы уже примеряем объяснение: больше гуляю — больше фотографирую, раньше ложусь — больше успеваю, чаще поливаю — бодрее фикус. Рядом с файлом успевает вырасти целая теория счастливой жизни. Пока отложим её и послушаем, что именно посчитано. Фикус разбирательство переждёт на подоконнике.
Достаточно двух столбцов и одной формулы. На маленьком наборе увидим, как она работает, затем проверим себя противоположным примером. И только после этого можно брать собственный дневник прогулок или наблюдений за растениями.
Корреляция в Excel начинается с пар
Представьте дневник прогулок. В понедельник прошли километр и сделали два снимка, во вторник — два километра и четыре снимка, в среду — три и шесть. У каждой прогулки своя пара чисел. Если записывать их рядом, можно задать таблице ясный вопрос: насколько ровно менялось одно вместе с другим?
Эти ровные числа возьмём для первого расчёта: расстояния 1, 2, 3 и снимки 2, 4, 6. По ним легко заранее увидеть ответ: оба показателя растут вместе, причём второй каждый раз вдвое больше первого. Заранее понятный рисунок позволит проверить функцию, прежде чем поручать ей собственные запутанные записи.
Откройте пустую книгу в настольном Excel для Windows. В A1 напишите «Километры», в B1 — «Фотографии», в C1 — «Коэффициент». Названия помогут вспомнить смысл столбцов, когда к файлу вернётесь через неделю.
Заполните клетки так:
| Строка Excel | A: километры | B: фотографии | Что связывает значения |
|---|---|---|---|
| 2 | 1 | 2 | Первая прогулка |
| 3 | 2 | 4 | Вторая прогулка |
| 4 | 3 | 6 | Третья прогулка |
Не включайте заголовки в будущие диапазоны. Данные первого ряда лежат в A2:A4, второго — в B2:B4. Двоеточие означает «от первой указанной клетки до последней включительно».
Если отдельно отсортировать только фотографии, снимки одной прогулки попадут к километрам другой. Можно даже превратить исходное возрастание в убывание, выстроив второй столбец в обратном порядке. Excel исправно посчитает новый набор. Вот только ответит он уже про сочетания чисел, которых в дневнике никогда не было. При сортировке наблюдений перемещайте целые строки вместе с датой и обоими значениями.
В C2 появляется первый ответ
Щёлкните ячейку C2 и вставьте туда:
=КОРРЕЛ(A2:A4;B2:B4)Нажмите Enter. В C2 должно появиться число 1. Положительную единицу Excel обычно показывает без плюса; в объяснении ниже будем писать +1, чтобы она отличалась от отрицательного результата.
У формулы две части в скобках. Первая указывает, где лежат километры, вторая — где фотографии. Точка с запятой разделяет эти два диапазона в показанной русской записи. Функция сопоставляет первое значение первого ряда с первым значением второго, затем вторые значения и так далее.
Если Excel у вас уже открыт с другим языком меню или непривычными настройками, полезен помощник в DeepSeek. Он проведёт по вашей версии и поможет понять ошибку. Начать можно таким сообщением:
Хочу посчитать коэффициент корреляции двух рядов в Excel. У меня A2:A4 содержит 1, 2, 3, а B2:B4 — 2, 4, 6. Нужно ввести в C2 формулу =КОРРЕЛ(A2:A4;B2:B4), получить 1 и сохранить книгу. Сначала уточни моё устройство, версию и язык Excel. Затем давай по одному действию: что открыть → что нажать или куда вставить → какой результат я должен увидеть. Жди моего ответа. Если появится ошибка, разбери её текст и настройки, прежде чем менять формулу.
В англоязычной версии функция называется CORREL. Разделитель аргументов зависит от настроек; если программа отвергает строку, опишите помощнику, на каком месте остановился ввод. Одной жалобы «не считает» мало, а текст ошибки и содержимое строки формул уже позволяют искать конкретную причину. Личные строки таблицы для этого передавать не нужно.
Что означает эта единица
КОРРЕЛ вычисляет коэффициент линейной корреляции Пирсона. Слово «линейной» удобно понять по точкам: каждой паре чисел соответствует точка на плоскости. Когда все точки точно ложатся на восходящую прямую, коэффициент равен +1. У нисходящей прямой получится −1.
В нашем наборе при каждом увеличении первого числа на единицу второе увеличивается на два. Поэтому ответ ожидаем заранее и можем проверить, что формула смотрит в нужные клетки. Единица здесь означает точное линейное соответствие этих трёх пар. Она не означает «одна фотография» или «один процент».
На собственных наблюдениях картина обычно неровнее: за длинную прогулку можно не снять ничего, а у ближайшего подъезда встретить кота, который обеспечит весь семейный фотоархив. Двадцать кадров с левой лапы, тридцать с правой — и вот прогулка длиной в сто метров уже просится на персональную выставку. Коэффициент лежит между −1 и +1. Знак показывает направление линейной связи, а близость к краям — насколько тесно точки держатся около прямой.
Значение около нуля говорит о слабой линейной связи в выбранных данных. Другой рисунок зависимости оно не исключает. Поэтому полезно посмотреть и на сами пары, особенно если среди обычных дней затесался один день с сотней снимков. В руководстве NIST для этого используют точечную диаграмму: по ней видны изгибы, отдельные далеко стоящие точки и группы наблюдений.
Для первого упражнения достаточно единицы в C2. Мы учимся обращаться с формулой и читать её ответ. Из трёх специально подобранных прогулок ещё не получится правило о том, как ведут себя люди с фотоаппаратами.
Перевернём второй ряд и проверим знак
Сохраните первый лист. Нажмите правой кнопкой на его вкладке внизу, выберите «Переместить или скопировать», отметьте «Создать копию» и подтвердите. Теперь можно менять пример на копии, сохраняя исходный набор.
На новом листе замените только B2:B4: вместо 2, 4, 6 введите 6, 4, 2. Столбец A и формулу в C2 оставьте прежними. После ввода последнего числа ожидаемый результат в C2 — −1.
Теперь при росте первого ряда второй ровно убывает. Перед нами такой же тесный линейный рисунок, только направленный в другую сторону. Минус не сообщает, что расчёт испортился или связь «хуже». Он описывает направление.
| Набор | Первый ряд | Второй ряд | Ожидаемый коэффициент | Как прочитать |
|---|---|---|---|---|
| Исходный лист | 1, 2, 3 | 2, 4, 6 | +1 | Точное совместное линейное возрастание |
| Копия листа | 1, 2, 3 | 6, 4, 2 | −1 | Точное линейное убывание второго при росте первого |
На копии мы намеренно меняем числа и наблюдаем ответ функции. С реальным дневником такая перестановка означала бы потерю соответствия прогулок, поэтому копию оставим именно проверкой: «Что сделает формула, если один ряд ровно растёт, а другой ровно убывает?» Вопрос узкий, зато на него уже можно ответить уверенно.
Число получилось странным: куда смотреть
Начните со строки формул. В нашем примере оба диапазона должны заканчиваться четвёртой строкой и начинаться второй. Сдвиг B2:B4 на B3:B5 меняет пары, даже если число клеток осталось тем же. Если случайно захватить соседний столбец, можно получить вполне убедительный ответ совсем на другой вопрос.
Затем проверьте сами значения. По документации Microsoft функция учитывает нули, а текст, логические значения и пустые клетки в переданных массивах игнорирует. Поэтому пустая клетка и настоящий ноль имеют разный смысл. Пропущенное наблюдение стоит сначала восстановить либо исключить целиком вместе с его парой, сохранив соответствие оставшихся строк.
Если число пришло из другого файла и хранится текстом, сначала разберитесь с его форматом. Посмотрите, нет ли единиц вроде «2 км» прямо в клетке с числом. Для вычисления оставьте числовое значение, а единицу перенесите в заголовок. Помощнику можно показать одну обезличенную проблемную строку и спросить, как проверить тип её клеток в вашей версии Excel.
Другая причина ошибки — одинаковые значения во всём одном ряду. Например, 2, 2, 2 вместо разных расстояний. У такого ряда нет разброса, и коэффициент по этой формуле не определяется. Microsoft также указывает ошибки при пустом массиве и несовпадающем числе точек. Подгонять ответ к единице добавлением случайных чисел не требуется: сначала нужно исправить исходные данные.
Если повторяете упражнение, верните простые три пары и проверьте обе крайние точки: сначала +1, затем −1. Когда оба ответа совпали с ожидаемыми, перенесите способ на свой набор. При копировании формулы следите, не сдвинулись ли ссылки. О том, как читать адреса и искать такие сдвиги, есть отдельная инструкция по проверке формул Excel.
Сохранить результат вместе с его смыслом
Переименуйте листы: дважды щёлкните по имени вкладки, введите «Рост» для исходного набора, «Обратный ряд» для копии. Рядом с результатами напишите «Демонстрационные пары; ожидается +1» и «Изменён второй ряд; ожидается −1». Через неделю эти подписи сберегут вам маленькое расследование: откуда в одной книге две противоположные единицы и какой из них верить.
Нажмите Ctrl+S. Если книга сохраняется впервые, выберите папку и понятное имя, например «Проверка корреляции.xlsx». Формат книги Excel сохранит два листа и формулы. Закройте файл, снова откройте и убедитесь, что на первом листе осталась единица, на втором — минус единица, а C2 содержит формулу. Двойной щелчок по C2 или строка формул помогут увидеть её запись.
Теперь можно завести отдельный лист со своими наблюдениями. Сначала сформулируйте вопрос одной фразой: «Связано ли расстояние прогулки с числом моих снимков?» В каждой строке держите дату, расстояние и фотографии одной прогулки. Числовые столбцы сравнивайте только после этой проверки. Для следующего разбора полезнее честный небольшой дневник, чем два длинных столбца с неизвестным происхождением строк.
Сам коэффициент причин не устанавливает. И километры, и снимки могут зависеть от свободного времени: в выходной удаётся и дальше уйти, и чаще останавливаться. Предложите DeepSeek несколько возможных объяснений и попросите для каждого назвать, какие дополнительные наблюдения помогли бы его проверить. Так помощник расширит вопрос, который возник у вас за таблицей.
Если хочется сначала рассмотреть готовый график и научиться отличать видимое изменение от догадки о причине, пригодится разбор диаграммы с нейросетью. Здесь же главный результат уже у вас: книга с согласованными парами, формулой и двумя ответами, смысл которых понятен.
Вопросы и ответы о двух рядах
Можно ли просто выделить два столбца целиком?
Для первой проверки лучше указать точные диапазоны A2:A4 и B2:B4. Так видно, какие наблюдения вошли в расчёт, и легче заметить лишние строки. Когда данных станет больше, замените последнюю строку в обоих диапазонах на фактическую последнюю строку ваших пар.
А если поменять столбцы местами?
При тех же согласованных парах коэффициент останется тем же. Порядок аргументов не назначает причину и следствие. Важно сохранить соответствие значений друг другу, поэтому перестановка двух столбцов целиком и случайное перемешивание одного столбца дают разный результат.
Почему отрицательное значение бывает полезным?
Оно показывает, что при увеличении одного показателя другой имеет тенденцию уменьшаться по линейному рисунку. В нашем втором наборе это видно без вычислений: слева 1, 2, 3, справа 6, 4, 2. Для собственной задачи дальше придётся разобраться, какие именно наблюдения дали такой рисунок.
Нужен ли дополнительный пакет анализа?
Для показанного расчёта достаточно встроенной КОРРЕЛ. Сложная матрица из десятка показателей здесь не требуется. Сначала получите ответ для одной пары столбцов и проверьте его на знакомых числах.
Может ли нейросеть посчитать всё сама?
Помощник может объяснить формулу, предложить проверку диапазонов и помочь прочитать ошибку. В этой задаче итоговый расчёт остаётся в вашей книге: можно открыть C2 и увидеть, откуда взялся ответ. Если просите нейросеть проверить числа отдельно, сопоставьте её объяснение с формулой и исходными парами.
К этой маленькой книге приятно возвращаться: вчера в ней был непонятный коэффициент, сегодня видно, что делают клетки. Дальше можно заняться собственными записями и постепенно научиться задавать им более точные вопросы. В клубе «Первопроходцы» для этого есть практики, вопросы и работа над личными проектами; ближайшее направление — задачи со своими документами.
А конь пока подождёт снаружи. Когда из подарка звенят мечи, пригодится ещё один вопрос к исходным данным.