Меню

Как создать линейную таблицу excel



Функция ЛИНЕЙН

В этой статье описаны синтаксис формулы и использование функции LINEST в Microsoft Excel. Ссылки на дополнительные сведения о диаграммах и выполнении регрессионного анализа можно найти в разделе См. также.

Описание

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

Уравнение для прямой линии имеет следующий вид:

y = m1x1 + m2x2 +. + b

если существует несколько диапазонов значений x, где зависимые значения y — функции независимых значений x. Значения m — коэффициенты, соответствующие каждому значению x, а b — постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив . Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис

ЛИНЕЙН(известные_значения_y; [известные_значения_x]; [конст]; [статистика])

Аргументы функции ЛИНЕЙН описаны ниже.

Синтаксис

Известные_значения_y. Обязательный аргумент. Множество значений y, которые уже известны для соотношения y = mx + b.

Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x. Необязательный аргумент. Множество значений x, которые уже известны для соотношения y = mx + b.

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

Если массив известные_значения_x опущен, то предполагается, что это массив <1;2;3;. >, имеющий такой же размер, что и массив известные_значения_y.

Конст. Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

Если аргумент конст имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.

Если аргумент конст имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.

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

Если статистика имеет true, то LINEST возвращает дополнительную регрессию; в результате возвращается массив .

Если аргумент статистика имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

Дополнительная регрессионная статистика.

Стандартные значения ошибок для коэффициентов m1,m2. mn.

Стандартное значение ошибки для постоянной b (seb = #Н/Д, если аргумент конст имеет значение ЛОЖЬ).

Коэффициент определения. Сравнивает предполагаемые и фактические значения y и диапазоны значений от 0 до 1. Если значение 1, то в выборке будет отличная корреляция— разница между предполагаемым значением y и фактическим значением y не существует. С другой стороны, если коэффициент определения — 0, уравнение регрессии не помогает предсказать значение y. Сведения о том, как вычисляется 2, см. в разделе «Замечания» далее в этой теме.

Стандартная ошибка для оценки y.

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

Степени свободы. Степени свободы используются для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Дополнительные сведения о вычислении величины df см. ниже в разделе «Замечания». Далее в примере 4 показано использование величин F и df.

Регрессионная сумма квадратов.

Остаточная сумма квадратов. Дополнительные сведения о расчете величин ssreg и ssresid см. в подразделе «Замечания» в конце данного раздела.

На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика.

Замечания

Любую прямую можно описать ее наклоном и пересечением с осью y:

Наклон (m):
Чтобы найти наклон линии, обычно записанной как m, возьмите две точки на строке (x1;y1) и (x2;y2); наклон равен (y2 — y1)/(x2 — x1).

Y-перехват (b):
Y-пересечение строки, обычно записанное как b, — это значение y в точке, в которой линия пересекает ось y.

Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.

Читайте также:  Просмотр структуры таблицы sql

Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:

Наклон:
=ИНДЕКС( LINEST(known_y,known_x’s);1)

Y-перехват:
=ИНДЕКС( LINEST(known_y,known_x),2)

Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель ЛИНЕЙН. Функция ЛИНЕЙН использует для определения наилучшей аппроксимации данных метод наименьших квадратов. Когда имеется только одна независимая переменная x, значения m и b вычисляются по следующим формулам:

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

Функции ЛИННЕСТРОЙ и ЛОГЪЕСТ могут вычислять наилучшие прямые или экспоненциальное кривой, которые подходят для ваших данных. Однако необходимо решить, какой из двух результатов лучше всего подходит для ваших данных. Вы можетевычислить known_y( known_x) для прямой линии или РОСТ( known_y, known_x в ) для экспоненциальной кривой. Эти функции без аргумента new_x возвращают массив значений y, спрогнозируемых вдоль этой линии или кривой в фактических точках данных. Затем можно сравнить спрогнозируемые значения с фактическими значениями. Для наглядного сравнения можно отобразить оба этих диаграммы.

Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений y (без вычитания среднего значения y из частного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal — ssresid. Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента определения r 2 — индикатор того, насколько хорошо уравнение, выданное в результате регрессионного анализа, объясняет связь между переменными. Значение r 2 равно ssreg/sstotal.

В некоторых случаях один или несколько столбцов X (предполагается, что значения Y и X — в столбцах) могут не иметь дополнительного прогнозируемого значения при наличии других столбцов X. Другими словами, удаление одного или более столбцов X может привести к одинаковой точности предсказания значений Y. В этом случае эти избыточные столбцы X следует не использовать в модели регрессии. Этот вариант называется «коллинеарность», так как любой избыточный X-столбец может быть выражен как сумма многих не избыточных X-столбцов. Функция ЛИНЕЙН проверяет коллинеарность и удаляет все избыточные X-столбцы из модели регрессии при их идентификации. Удалены столбцы X распознаются в результатах LINEST как имеющие коэффициенты 0 в дополнение к значениям 0 se. Если один или несколько столбцов будут удалены как избыточные, это влияет на df, поскольку df зависит от числа X столбцов, фактически используемых для прогнозирования. Подробные сведения о вычислении df см. в примере 4. Если значение df изменилось из-за удаления избыточных X-столбцов, это также влияет на значения Sey и F. Коллинеарность должна быть относительно редкой на практике. Однако чаще всего возникают ситуации, когда некоторые столбцы X содержат только значения 0 и 1 в качестве индикаторов того, является ли тема в эксперименте участником определенной группы или не является ее участником. Если конст = ИСТИНА или опущен, функция LYST фактически вставляет дополнительный столбец X из всех 1 значений для моделирования перехвата. Если у вас есть столбец с значением 1 для каждой темы, если мальчик, или 0, а также столбец с 1 для каждой темы, если она является женщиной, или 0, последний столбец является избыточным, так как записи в нем могут быть получены из вычитания записи в столбце «самец» из записи в дополнительном столбце всех 1 значений, добавленных функцией LINEST.

Вычисление значения df для случаев, когда столбцы X удаляются из модели вследствие коллинеарности происходит следующим образом: если существует k столбцов известных_значений_x и значение конст = ИСТИНА или не указано, то df = n – k – 1. Если конст = ЛОЖЬ, то df = n — k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.

При вводе константы массива (например, в качестве аргумента известные_значения_x) следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть другими в зависимости от региональных параметров.

Читайте также:  Размер обуви 255 таблица

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

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

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

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

Помимо вычисления статистики для других типов регрессии с помощью функции ЛГРФПРИБЛ, для вычисления диапазонов некоторых других типов регрессий можно использовать функцию ЛИНЕЙН, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:

работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:

y = m1*x + m2*x^2 + m3*x^3 + b

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

Значение F-теста, возвращаемое функцией ЛИНЕЙН, отличается от значения, возвращаемого функцией ФТЕСТ. Функция ЛИНЕЙН возвращает F-статистику, в то время как ФТЕСТ возвращает вероятность.

Примеры

Пример 1. Наклон и Y-пересечение

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

Источник

Как создать таблицу в Excel: пошаговая инструкция

Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизировать этот процесс , ответит наша статья.

Советы по структурированию информации

Перед тем, как создать таблицу в Excel, предлагаем изучить несколько общих правил:

  • Сведения организуются по колонкам и рядам. Каждая строка отводится под одну запись.
  • Первый ряд отводится под так называемую «шапку», где прописываются заголовки столбцов.
  • Нужно придерживаться правила: один столбец – один формат данных (числовой, денежный, текстовый и т.д.).
  • В таблице должен содержаться идентификатор записи, т.е. пользователь отводит один столбец под нумерацию строк.
  • Структурированные записи не должны содержать пустых колонок и рядов. Допускаются нулевые значения.

Как создать таблицу в Excel вручную

Для организации рабочего процесса пользователь должен знать, как создать таблицу в Экселе. Существуют 2 метода: ручной и автоматический. Пошаговая инструкция, как нарисовать таблицу в Excel вручную:

  1. Открыть книгу и активировать нужный лист.
  2. Выделить необходимые ячейки.
  3. На панели инструментов найти пиктограмму «Границы» и пункт «Все границы».
  4. Указать в таблице имеющиеся сведения.

II способ заключается в ручном рисовании сетки таблицы. В этом случае:

Источник

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

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

Для какой цели используется линейная диаграмма

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

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

На оси абсцисс, как правило, показывается временная характеристика явления. На оси ординат – значение показателя.

Как построить линейную диаграмму в Excel

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

Создадим таблицу в Excel «Численность населения России за 2000-2016 гг.»:

Численность населения.

  1. Все ячейки в колонках должны быть заполнены.
  2. Данные имеют одинаковый формат.
  3. В одном столбце – значения одного типа (даты, численность).
Читайте также:  Таблица сад и дад

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

Можно выбрать «с накоплением», «нормированный», «с маркерами» и т.п. Щелкаем по иконке выбранной разновидности графика левой кнопкой мыши. Получаем:

График.

Такой график нас не устраивает – он построен не совсем правильно. Поэтому щелкаем по области построения, чтобы активировалась вкладка «Работа с диаграммами». Нажимаем кнопку «Выбрать данные».

Выбрать данные.

В окне «Выбор источника данных» мы сначала изменим подписи горизонтальной оси.

Выбор источника данных.

После нажатия кнопки «Изменить» откроется поле для назначения диапазона подписей. Выбираем столбец с датами.

Столбец с датами.

Нажимаем ОК. Программа возвращает нас к диалоговому окну «Выбор источника данных».

В «Элементы легенды» попали данные столбца «Год» и «Численность населения». «Год» удалим.

Год удалить.

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

Формат оси.

В открывшемся диалоговом окне переходим на вкладку «Выравнивание». Здесь мы можем изменить направление текста.

Выравнивание.

Получаем график вида:

Пример.

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

Линейчатая диаграмма

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

Каждый столбик (прямоугольник) показывает величину уровня в анализируемом статистическом ряду. Сравниваемые данные имеют одинаковые единицы измерения. Поэтому удается проанализировать рассматриваемый процесс.

На основе уже имеющихся данных по численности населения построим линейчатую диаграмму.

Линейчатая.

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

Линейчатая диаграмма в бок.

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

Ряды и категории.

Значения для категорий и рядов должны остаться теми же.

Источник

Пошаговая инструкция: как создать таблицу в EXCEL

Что такое EXCEL? Это одна из программ популярного софта Microsoft Office. Программа Microsoft Excel считается мощнейшим математическим редактором, функции которого широки и многогранны. Здесь мы рассмотрим самое простое назначение этой программы. С её помощью легко и просто создавать любые таблицы с подсчётом результатов. Статья рассчитана на новичка в использовании программы, но предполагает, что пользователь имеет представление о том, что такое компьютер и как работает мышка.

Итак, мы рассмотрим как создать таблицу в excel, пошаговая инструкция. Работаем в версии Microsoft Excel 2000. От версии софта зависит внешний вид интерфейса, а принцип и основные функции идентичны, поэтому поняв принцип работы можно легко освоить любой интерфейс.

Создание таблицы в Excel

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

Верхняя строка показывает меню, с которым следует обязательно ознакомиться. В нашем примере нам достаточно будет изучить вкладку ФОРМАТ. Нижняя строка показывает листы, на которых можно работать. По умолчанию представлено 3 листа, тот который выделен, называется текущим. Листы можно удалять и добавлять, копировать и переименовывать. Для этого достаточно мышкой встать на текущее название Лист1 и нажать правую кнопку мыши для открытия контекстного меню.

Редакторы сайта рекомендуют ознакомиться с возможностями функции ВПР в Excel.

Шаг 1. Определение структуры таблицы

Мы будем создавать простую ведомость по уплате профсоюзных взносов в виртуальной организации. Такой документ состоит из 5 столбцов: № п. п., Ф.И.О., суммы взносов, даты уплаты и подписи сдающего деньги. В конце документа должна быть итоговая сумма собранных денег.

Для начала переименуем текущий лист, назвав его «Ведомость» и удалим остальные. В первой строке рабочего поля введём название документа, встав мышкой в ячейку A1: Ведомость по уплате профсоюзных членских взносов в ООО «Икар» за 2017 год.

В 3 строке будем формировать названия столбцов. Вносим нужные нам названия последовательно:

Источник

Adblock
detector