Обзор инструментов и функций программы MS EXCEL, связанных с анализом тренда
Инструменты и функции анализа степени («тесноты») связи случайных величин, то есть методы корреляционного анализа, можно использовать для установления наличия тренда и степени его статистической значимости [28]. Только в этом случае рассматривается связь не произвольных величин, как в корреляционном анализе, а элементов одного временного ряда (см. § 1.1):
— значения времени (или, например, номера обрабатываемой детали), xi;
— значения контролируемой характеристики (размера, свойства, шероховатости и т.д.), соответствующей этому времени или номеру (yi).
Аналитические характеристики «тесноты» связи этих элементов временного ряда (коэффициенты ковариации, коэффициенты корреляции, коэффициенты детерминации) играют ту же роль установления тесноты связи и достоверности связи величин xi и yi, что и при традиционном корреляционном анализе [28]. Но при этом xi и yi — не отдельные случайные величины, а элементы одного ряда.
Функции КОВАР, КОРРЕЛ, ПИРСОН и КВПИРСОН инструменты анализа «Ковариация» и «Корреляция» MS EXCEL [28] позволяют решать эти задачи. При этом если в одном столбце (или строке) приводятся временные характеристики элементов ряда, то в параллельном столбце (или строке) должны стоять соответствующие значения контролируемой характеристики. И эти столбцы (или строки) вводят как массивы исходных данных в аргументы используемых функций или инструментов.
Степень «тесноты» связи двух элементов временного ряда также можно оценивать по шкале Чеддока [28].
Детальное исследования динамики процессов может проводиться с помощью инструмента анализа «Регрессия» и ряда статистических функций, основанных на методе наименьших квадратов [28]. Может рассматриваться линейная регрессия (инструмент «Регрессия», функции ТЕНДЕНЦИЯ, НАКЛОН, ЛИНЕЙН, ПРЕДСКАЗ, ОТРЕЗОК) и нелинейная регрессия (функции ЛТРФПРИБЛ, РОСТ, СТОШУХ). Напомним, что для правильной работы инструмента «Регрессия временные характеристики элементов ряда и значения контролируемой характеристики должны быть обязательно представлены в столбцах. Цели и приёмы использования каждой функции подробно рассмотрены в учебном пособии [28]. Напомним, что если для вывода результатов расчёта функции MS EXCEL требуется не одна ячейка, необходимо предварительно выделить требуемый массив ячеек. После введения всех аргументов необходимо набрать комбинацию клавиш CTRL+SHIFT+ENTER.
При графическом анализе тренда временного ряда его временная характеристика xi и соответствующая контролируемая характеристика yi откладываются по осям абсцисс и ординат и связываются «линией тренда». По близости точек к этой линии можно наглядно судить о степени «тесноты» связи этих элементов, а по углу наклона линии тренда — о степени изменчивости контролируемой характеристики yi.
Построение линии тренда инициируется нажатием правой кнопки мыши на одну из точек ряда данных предварительно построенного графика (рис. 8.6).
Рис. 8.6. Опции, открывающиеся при нажатии правой кнопки мыши на ряд данных
После нажатия «Добавить линию тренда» открываются большие возможности выбора типа регрессионной зависимости (линейного, логарифмического, экспоненциального, степенного, полиномиального до 6 степени включительно), рис. 8.7. Кроме того, появляется возможность использовать опцию «Линейная фильтрация», которая при правильном использовании должна сглаживать отклонения в данных и более четко показывать форму линии тренда. Число точек, образующих линию тренда с линейной фильтрацией (Скользящее среднее. Последовательность средних значений, вычисленных по частям рядов данных. На диаграмме линия, построенная по точкам скользящего среднего, позволяет построить сглаженную кривую, более ясно показывающую закономерность в развитии данных.), равно общему числу точек ряда за вычетом числа, указанного для параметра «Точки». Если линейная фильтрация добавляется к точечной диаграмме, она базируется на порядке расположения значений X в диаграмме. Поэтому для получения нужного результата перед добавлением линейной фильтрации, возможно, потребуется отсортировать значения X.
Рис. 8.7. Элементы окна, открывающегося при выборе опции «Добавить линию тренда»
Для более развёрнутой характеристики выбранной модели и построенной линии тренда служит опция «Параметры». Она позволяет «заказать» для выбранной линии тренда представление на диаграмме уравнения регрессии и коэффициента детерминации R 2 . Опция «Параметры» открывается на второй вкладке окна «линия тренда» (см. рис. 8.7) или вкладке окна «Формат линии тренда» (рис. 8.8), которое открывается нажатием правой кнопки мыши на уже построенную линию тренда.
В данном случае за отсутствием суммы квадратов остатков R 2 — единственная, но действенная характеристика точности аппроксимации. Перебором различных линий тренда с определением их коэффициентов детерминации с учётом физической сущности процесса можно быстро выбрать оптимальную модель. Например, с увеличением степени полинома в полиномиальной модели (см. рис. 8.7) как правило, R 2 увеличивается. Но это не значит, что непременно следует выбрать максимально возможную степень полинома (шестую).
Рис. 8.8. Опция «Параметры» в окне «Формат линии тренда»
Как видно из рис. 8.8, дополнительная опция «Прогноз» позволяет осуществлять экстраполяцию линии тренда на заданное количество единиц по оси абсцисс «вперёд» и «назад».
Название и назначение инструментов интерфейса MS Excel
1. Кнопка Office — расположена в левом верхнем углу окна. При нажатии кнопки отображается меню основных команд для работы с файлами, список последних документов, а также команда для настройки параметров приложения (например, Параметры Excel).
2. Панель быстрого доступа — по умолчанию расположена в верхней части окна Excel и предназначена для быстрого доступа к наиболее часто используемым функциям. По умолчанию панель содержит всего три кнопки: Сохранить, Отменить, Вернуть (Повторить). Панель быстрого доступа можно настраивать, добавляя в нее новые элементы или удаляя существующие.
3. Вкладка – По умолчанию в окне отображается семь постоянных вкладок: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид. Каждая вкладка связана с видом выполняемого действия. Кроме того, можно отобразить еще одну вкладку: Разработчик. Помимо постоянных, имеется целый ряд контекстных вкладок, например, для работы с таблицами, рисунками, диаграммами и т.п., которые появляются автоматически при переходе в соответствующий режим или при выделении объекта или установке на него курсора.
4. Кнопка запуска окна диалога – кнопка для запуска диалогового окна с дополнительными параметрами для группы команд. Расположена кнопка в правом нижнем углу группы команд
5. Лента – главный элемент пользовательского интерфейса Excel 2007 которая идет вдоль верхней части окна каждого приложения. С помощью ленты можно быстро находить необходимые команды (элементы управления: кнопки, раскрывающиеся списки, счетчики, флажки и т.п.). Команды упорядочены в логические группы, собранные на вкладках. Набор команд, который отображается на Ленте зависит от того какая вкладка выбрана.
6. Поле Имя – в этом поле указывается адрес ячейки (например: А2, B5, D9) активной (выделенной) в данный момент или адрес верхней левой ячейки выделенного диапазона ячеек.
7. Строка формул – строка, в которой отображаются и/или в которую вводятся данные, хранящиеся в ячейке. Вызов/удаление строки формул с помощью команды Вид®Строка формул.
8. Строка с указанием имени столбца – имена столбцов обозначаются латинскими буквами, например: C, D, F, R и т.д. до 16834 столбца.
9. Группа – упорядоченная группа команд, объединенная по типу выполняемого действия. Название группы пишется внизу Ленты.
10. Выделенная ячейка –ячейка, которая в данный момент времени является текущей.
11. Горизонтальная и вертикальная полосы прокрутки– позволяют просматривать содержимое всей рабочей области листа MS Excel.
12. Столбец с указанием номера строки – номер строки обозначается цифрами, например: 1, 2, 3, 4 и т.д. до значения 1048576.
13. Ярлыки рабочих листов – используются для переключения между рабочими листами. По умолчанию таких листов три: Лист1, Лист2 и Лист3.
14. Меню выбора режима просмотра книги с ползунком масштаба – используется для выбора масштаба отображения книги..
Настройка интерфейса MS Excel
Вид окна MS Excel 2007 настраивается при помощи элементов управления вкладки Вид.
Элементы управления этого окна позволяют скрыть или отобразить строку формул, строку состояния, формулы в ячейках, вертикальную и горизонтальную полосы прокрутки, ярлыки листов, сетку и т.д.
Структура документа
Документ Microsoft Excel называется книгой (иногда используют термин "рабочая книга"). Книга Microsoft Excel состоит из отдельных листов. Вновь создаваемая книга обычно содержит 3 листа. Листы можно добавлять в книгу. Максимальное количество листов не ограничено. Листы можно удалять. Минимальное количество листов в книге – один.
Листы в книге можно располагать в произвольном порядке. Можно копировать и перемещать листы, как в текущей книге, так и из других книг. Каждый лист имеет имя. Имена листов в книге не могут повторяться. Листы могут содержать таблицы, диаграммы, рисунки и другие объекты. Могут быть листы, содержащие только диаграмму.
Ярлыки листов расположены в нижней части окна Microsoft Excel.
Лист состоит из ячеек, объединенных в столбцы и строки. Лист содержит 16834 столбцов. Столбцы именуются буквами английского алфавита. Заголовок столбца содержит от одного до трех символов. Первый столбец имеет имя А, последний – XFD. Лист содержит 1048576 строк. Строки именуются арабскими цифрами. Каждая ячейка имеет адрес (ссылку), состоящий из заголовка столбца и номера строки. Например, самая левая верхняя ячейка листа имеет адрес А1, а самая правая нижняя – XFD1048576. Кроме того, ячейка (или диапазон ячеек) может иметь имя. Ячейка может содержать данные (текстовые, числовые, даты, время и т.п.) и формулы. Ячейка может иметь примечание.
Режимы просмотра документа
Выбрать режим просмотра листа можно при работе в любой вкладке Excel 2007. Ярлыки выбора основных режимов просмотра книги расположены в правой части строки состояния (рис.1).
По умолчанию для вновь создаваемых документов установлен режим просмотра Обычный. Этот режим используется для выполнения большинства задач Microsoft Excel, таких, как ввод и обработка данных, форматирование данных и ячеек, вычисления, построение диаграмм и т. д.
Режим Разметка страницы позволяет изменять данные и при этом видеть их так, как они будут напечатаны на бумаге. В этом режиме обычно создают и оформляют колонтитулы.
В режиме Страничныйв окне отображается только собственно сама таблица. Остальные ячейки листа не отображаются. Зато отображены границы страниц. Перетаскиванием этих границ можно изменять порядок разделения таблицы между страницами при печати. Кроме того, только в этом режиме можно эффективно работать с разрывами страниц.
Во вкладке Вид в группе Режимы просмотра книги можно выбрать еще один режим просмотра – Во весь экран. Этот режим обеспечивает скрытие большинства элементов окна для увеличения видимой части документа.
Работа с листами
Чтобы быстро вставить новый лист после существующих листов, необходимо щелкнуть по ярлыку Вставить лист в нижней части экрана (рис. 2).
Рис. 2. Добавление нового листа
Чтобы вставить новый лист перед существующим листом, необходимо выбрать ярлык этого листа, в группе Ячейки вкладки Главная щелкнуть по стрелке кнопки Вставить и выбрать команду Вставить лист. Если выделить несколько ярлыков листов, то вставится точно такое же количество новых листов.
Вставленный лист имеет имя "Лист…". После имени "Лист" стоит цифра. Если в книге нет других листов с именем "Лист", то новый лист будет иметь имя "Лист1".
Переименование листа
Для переименования листа надо щелкнуть правой кнопкой мыши по ярлыку листа и в контекстном меню (рис. 3) выбрать команду Переименовать. Имя листа не должно содержать более 31 символа. В именах можно использовать любые символы, кроме / \ : [ ] ? *
Перемещение и копирование листа в пределах книги обычно производят перетаскиванием ярлыка листа вдоль линии ярлыков при нажатой левой кнопке мыши. Для копирования при перетаскивании следует держать нажатой клавишу Ctrl.
Какие инструменты соответствуют ms excel
Таблицы Excel — очень мощный инструмент. В них больше 470 скрытых функций. Поначалу это пугает: кажется, на то, чтобы разобраться со всем, уйдут годы. На самом деле это не так. Всего десятка функций и горячих клавиш уже хватит для того, чтобы сильно упростить себе жизнь. Расскажем о некоторых из них (скоро стартует второй поток курса «Магия Excel»).
Интерфейс
Настраиваем панель быстрого доступа
Начнем с самого простого — добавления самых часто используемых опций на панель быстрого доступа. Чтобы сделать это, заходите в параметры Excel — «Настроить ленту» — и ищите в параметрах «Панель быстрого доступа».
Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.
Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:
Перемещаемся по ленте без мышки
Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:
Нажмите на клавиатуре любую из букв — попадете на соответствующую вкладку на ленте, а там каждый инструмент в свою очередь тоже будет подписан. Так можно быстро вызвать нужные опции, не трогая мышку.
Ввод данных
Теперь давайте рассмотрим несколько инструментов для быстрого ввода данных.
Автозамена
Если вам часто нужно вводить какое-то словосочетание, адрес, емейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах:
Прогрессия
Если нужно заполнить столбец или строку последовательностью чисел или дат, введите в ячейку первое значение и затем воспользуйтесь этим инструментом:
Протягивание
Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО). Задайте Excel одну ячейку с образцом — что хотите получить:
Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).
Проверка ошибок
Проверка данных позволяет избежать ошибок при вводе информации в ячейки.
Какие бывают типовые ошибки в Excel?
- Текст вместо чисел
- Отрицательные числа там, где их быть не может
- Числа с дробной частью там, где должны быть целые
- Текст вместо даты
- Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.
Инструмент проверки данных
Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:
Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.
Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.
Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
Дата и время
За любой датой в Excel скрывается целое число. Датой его делает формат.
Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.
Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:
Вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней.
Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.
Поиск и подстановка значений
Функция ВПР / VLOOKUP
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).
=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:
=ПОИСКПОЗ (что ищем; где ищем ; 0)
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
Получается следующая конструкция:
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
Оформление
Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».
На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:
А самое главное — если вы применили стиль ко многим ячейкам (например, ко всем заголовкам на 20 листах книги Excel) и захотели что-то переделать, щелкните правой кнопкой мыши и нажмите «Изменить». Изменения будут применены ко всем нужным ячейкам в документе.
На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь →
Какие инструменты соответствуют ms excel
Сообщество создано для повышения знаний и навыков работы с Office. Публикуйте информативные и полезные посты, делитесь опытом с другими пользователями, продвигайте знания в массы
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде "пост — отстой", это оскорбление автора и будет наказываться баном.