Технология обработки данных с помощью электронных таблиц
Табличный процессор
Табличный процессор
прикладное программное обеспечение общего назначения, предназначенное
для обработки различных данных, представимых в табличной форме.
Microsoft Excel — программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic for Application). Microsoft Excel входит в состав Microsoft Office и на сегодняшний день Excel является одним из наиболее популярных приложений в мире.
Область применения MS Excel широка:
- благодаря тому, что лист Excel представляет из себя готовую таблицу, Excel часто используют для создания документов без всяческих расчётов, просто имеющих табличное представление (например, прайс-листы в магазинах, расписания);
- в Excel легко можно создавать различные виды графиков и диаграмм, которые берут данные для построения из ячеек таблиц (график снижения веса тела за указанный период от начала занятий спортом);
- его могут использовать обычные пользователи для элементарных расчетов (сколько потратил за этот месяц, что/кому/когда дал/взял);
- Excel содержит многие математические и статистические функции, благодаря чему его могут использовать школьники и студенты для расчетов курсовых, лабораторных работ;
- Excel интенсивно используется в бухгалтерии — во многих фирмах это основной инструмент для оформления документов, расчётов и создания диаграмм. Естественно, он имеет в себе соответствующие функции;
- Excel может даже работать как база данных. Хотя, конечно, до полноценной базы данных ему далеко;
- Excel можно использовать в качестве формы для представления обработанных данных.
Основы работы
с табличным процессором MS Excel
MS Excel, как прикладная программа Windows, выполняется в своем собственном окне приложения. Окно приложения MS Excel может содержать несколько окон рабочих книг (Workbook) — документов MS Excel, поэтому одновременно можно работать с несколькими рабочими книгами. Каждая рабочая книга состоит из нескольких рабочих листов (Worksheets), каждый из которых может содержать самостоятельную информацию.
Интерфейс MS Excel 2010 является дальнейшим развитием пользовательского интерфейса, который впервые был использован в выпуске системы Microsoft Office 2007. При создании интерфейса разработчиками была поставлена задача: сделать основные приложения Microsoft Office удобнее в работе. В результате был создан пользовательский интерфейс Microsoft Office Fluent, который упрощает для пользователей работу с приложениями Microsoft Office и дает возможность быстрее получать лучшие результаты.
Лента
Главный элемент пользовательского интерфейса Microsoft Excel 2010 представляет собой Ленту, которая идет вдоль верхней части окна каждого приложения, вместо традиционных меню и панелей инструментов. С помощью Ленты можно быстро находить необходимые команды (элементы управления: кнопки, раскрывающиеся списки, счетчики, флажки и т.п.). Команды упорядочены в логические группы, собранные на вкладках.
Ввод данных
В ячейки таблицы MS Excel можно вводить данные трех типов:
- текст,
- число,
- формула.
Ввод данных осуществляется непосредственно в текущую ячейку или в строку формул. Вводимые данные в любом случае отображаются как в ячейке, так и в строке формул.
Общие правила ввода данных
Выделите ячейку.
Введите данные с клавиатуры непосредственно в ячейку или в строку формул.
Подтвердите ввод.
Подтвердить ввод можно одним из трех способов:
нажать клавишу Enter или Tab;
нажать кнопку Ввод (галочка) в строке формул;
выделить любую другую ячейку на листе (нельзя использовать при вводе формул).
При вводе данных переводить текстовый курсор в ячейке клавишами клавиатуры нельзя. Это можно сделать только с использованием мыши. Необходимо навести указатель мыши и один раз щелкнуть левой кнопкой мыши.
Для отказа от ввода данных в ячейку следует нажать клавишу Esc или кнопку Отмена (крестик) в строке формул.
Текстовые данные
представляют собой строку символов произвольной длины.
Все, что не может интерпретироваться Excel как число или формула, воспринимается программой как текст.
Текстовые данные автоматически выравниваются по левому краю ячейки. Когда длина текста превышает ширину ячейки, его часть отображается в соседней ячейке, если она пуста. Иначе текст «обрезается», – в этом случае можно увеличить ширину столбца.
Расположить текст несколькими строками в одной ячейке можно несколькими способами (предварительно или после ввода текста):
- <Alt> + <Enter>.
- Вкладка Главная группа Формат кнопка Перенос текста
- Диалоговое окно Формат ячейки на вкладке Выравнивание установить флажок в поле Переносить по словам.
Числовые данные
это целые или вещественные числа.
Дробная часть должна отделяться от целой части запятой.
Числа автоматически выравниваются по правому краю ячейки.
При первоначальном вводе числа (до изменения ширины столбцов) в ячейку может отобразиться число из 11 цифр. При этом ширина столбца может автоматически увеличиться. При вводе большего числа число будет отображено в экспоненциальном формате. Если же ширина столбца была уменьшена и число не помещается в ячейке, то вместо числа в ячейке отображаются символы ####. В этом случае для корректного отображения числа необходимо либо увеличить ширину столбца, либо уменьшить размер шрифта.
Для удобства представления больших чисел группы разрядов при вводе можно отделять пробелами. Например, число 12345678 можно ввести как 12 345 678. В строке формул при этом число будет отображаться без пробелов между разрядами. Группы разрядов числа (за исключением первой группы) должны обязательно включать три знака. В противном случае данные в ячейке не будут восприниматься как число. Формат с разделителем разрядов запоминается в ячейке. После очистки содержимого ячейки и ввода новой числовой информации (включая даты и время) к ней автоматически будет применяться установленный числовой формат. Для возможности ввода другой числовой информации необходимо очистить формат ячейки.
В большинстве случаев следует вводить числа без указания размерности. В противном случае такая информация не воспринимается как число. Исключение составляет:
- обозначение рубля (следует вводить число, а затем букву р с точкой),
- обозначение процентов (следует вводить число, а затем символ процента %).
В отдельных случаях необходимо вводить числа, которые, по сути, являются текстом и не должны распознаваться как числа. Это могут быть, например, номера банковских счетов, номера телефонов, коды и артикулы, почтовые индексы, номера домов и квартир и т. п. Для того чтобы Microsoft Excel автоматически назначал таким числам текстовый формат, перед числом необходимо ввести знак апострофа ('). Этот знак отображается только в строке формул, но не отображается в ячейке.
Дата и время
Microsoft Excel хранит даты в виде последовательных чисел.
По умолчанию дате 1 января 1900 года соответствует порядковый номер 1.
Системы дат 1900 и 1904
Microsoft Excel поддерживает две системы дат: 1900 и 1904. По умолчанию, Microsoft Excel для Windows использует систему дат 1900 (1 января). По умолчанию, Microsoft Excel для Macintosh использует систему дат 1904 (2 января). Чтобы изменить систему дат, в меню Сервис выберите команду Параметры, перейдите к вкладке Правка и установите или снимите флажок система дат 1904.
При открытии документа, подготовленного на другой аппаратной платформе, смена системы дат происходит автоматически.
Поскольку даты и значения времени представляются числами, их можно складывать и вычитать, а также использовать в других вычислениях. При использовании основного формата для ячеек, содержащих дату и время, можно отобразить дату в виде числа или время в виде дробной части числа с десятичной точкой.
Поскольку правила, которые задают способ интерпретации дат в разных вычислительных программах, довольно сложны, следует быть предельно конкретным при вводе дат. Это обеспечит наивысшую точность в вычислении дат.
При вводе даты как текстового значения Microsoft Excel интерпретирует года следующим образом.
- Значение года от 00 до 29 . Двухзначные значения года от 00 до 29 Microsoft Excel интерпретирует как годы с 2000 по 2029. Например, если вводится дата 28.05.19, Microsoft Excel распознает ее как 28 мая 2019 г.
- Значение года от 30 до 99 . Двухзначные значения года от 30 до 99 Microsoft Excel интерпретирует как годы с 1930 по 1999. Например, если вводится дата 28.05.98, Microsoft Excel распознает ее как 28 мая 1998 г.
Для обеспечения правильной интерпретации значений года вводите четыре цифры года (2001 вместо 01). При вводе четырех цифр года Microsoft Excel не будет определять столетие по описанным выше правилам.
В Microsoft Excel можно изменить формат числа, включая дату и время, без изменения формата других чисел. Применяемый числовой формат не влияет на значение в ячейке (отображается в строке формул), которое Microsoft Excel будет использовать при вычислениях.
Формат |
Описание |
|---|---|
Общий |
Этот формат по умолчанию применяется в Excel при вводе чисел. |
Числовой |
Этот формат используется для общего отображения чисел. Можно указать используемое количество десятичных знаков, необходимость использовать разделитель разрядов, а также способ отображения отрицательных чисел. |
Денежный |
Этот формат используется для отображения общих денежных значений и выводит установленный по умолчанию денежный знак вместе с числами. Можно указать используемое количество десятичных знаков, необходимость использовать разделитель разрядов, а также способ отображения отрицательных чисел. |
Финансовый |
Этот формат также используется для денежных значений, но при его использовании осуществляется выравнивание денежных величин по разделителю целой и дробной части в столбце. |
Дата |
Этот формат служит для отображения даты и времени, представленных числами, в виде дат с учетом указанного типа и языка (местоположения). За исключением элементов, помеченных звездочками (*) в списке Тип (вкладка Число диалогового окна Формат ячеек), порядок элементов в применяемых форматах дат не меняется при смене операционной системы. |
Время |
Этот формат используется для отображения даты и времени, представленных числами, в виде времени с учетом указанного типа и языка (местоположения). В применяемых форматах, за исключением помеченных звездочкой (*) в списке Тип (вкладка Число, диалоговое окно Формат ячеек), порядок элементов даты и времени не меняется при смене формата операционной системы. |
Процентный |
Этот формат используется для умножения значения ячейки на 100 и отображения результата с символом процента. Можно указать используемое количество десятичных знаков. |
Дробный |
Этот формат используется для отображения числа в виде дроби с учетом указанного типа дроби. |
Экспоненциальный |
Этот формат используется для отображения чисел в экспоненциальном представлении и замены части числа на E+n, где E (экспонент) равно предыдущему числу, умноженном на 10 в степени n.. Можно указать используемое количество десятичных знаков. |
Текстовый |
При использовании этого формата, содержимое ячейки рассматривается как текст и отображается в том виде, в котором пользователь вводит его, даже при вводе чисел. |
Дополнительный |
Этот формат используется для отображения числа в виде почтового индекса, телефонного номера или табельного номера. |
(все форматы) |
Этот тип формата позволяет изменять копию существующего кода числового формата. Создается настраиваемый числовой формат, который добавляется к списку кодов числовых форматов. Можно добавить от 200 до 250 настраиваемых числовых форматов, в зависимости от того, версия Excel для какого языка была установлена. |
Формулы
Ввод формулы всегда должен начинаться с символа «=» (знак равенства). Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций. Скобки позволяют изменить стандартный порядок выполнения действий.
Вычисления в MS Excel
Ссылки на ячейки
- по виду (стилю)
- А1 (столбцы нумеруются буквами латинского алфавита, а строки - числами, причем сначала указывается номер столбца)
- R1C1 (и строки, и столбцы нумеруются числами, причем сначала указывается номер строки, а потом номер столбца)
- по типу
- относительные (при копировании изменяются относительно передвижению от начальной позиции)
- абсолютные (не изменяются при копировании)
- смешанные
Функции
заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами и разделяемыми точкой с запятой (;), в указанном порядке.
ИМЯ_ФУНКЦИИ([аргумент_1; аргумент_2; … ; аргумент_N])
- СЕГОДНЯ()
- ОСТАТ(10;5)
- SIN(0,5)
Каждая функция характеризуется:
- названием;
- предназначением;
- количеством аргументов (до 255);
- типом аргументов;
- типом возвращаемого значения.
В общем случае, аргументами функции могут быть
- константы,
- ссылки на ячейки/диапазон ячеек,
- именованные ссылки,
- другие функции (до 64 вложений).
Ошибки в формулах
КОД ОШИБКИ |
ПРИЧИНА ВОЗНИКНОВЕНИЯ |
|---|---|
##### (символы заполняют ячейку целиком) |
Данные не помещаются в ячейку по ширине или же при вычислении по формуле из даты вычитается более поздняя. |
#ДЕЛ/0! |
В знаменателе формулы получается нулевое значение или стоит ссылка на пустую ячейку. Последнее может оказаться связано с неверным использованием относительной ссылки. |
#ЗНАЧ! |
У функции пропущен обязательный параметр или же в качестве параметра использована ссылка на пустую ячейку или ячейку с данными, тип которых не соответствует типу нужного параметра. |
#ИМЯ? |
Опечатка в имени функции или имени ячейки (диапазона). Может быть вызвана пропуском двоеточия при указании диапазона или кавычек при указании текстового параметра. |
#ПУСТО! |
В формуле использовано пересечение диапазонов, не включающее ни одной ячейки. |
#ЧИСЛО! |
При вычислении получилось число, слишком большое или слишком маленькое для программы Excel. Как правило, это следствие неправильности формулы. |
#ССЫЛКА! |
Формула указывает на ячейку, которая отсутствует на рабочем листе. Чаще всего такая ошибка возникает вследствие копирования формулы с относительной адресацией влево или вверх, из-за чего возникают ссылки на столбцы, предшествующие столбцу А, и строки, предшествующие первой. |
Диаграммы. Линия тренда
Диаграмма
графическое представление данных, позволяющее быстро оценить соотношение нескольких величин.
Построение диаграммы включает в себя три этапа:
- создание таблицы
- описание диаграммы
- ее последующее использование
Основные типы диаграмм Microsoft Excel
| Тип диаграммы | Пример | |
|---|---|---|
Обычная гистограмма позволяет сравнить значения по категориям. |
|
|
Линейчатая диаграмма аналог гистограммы, повернутой на 90. |
|
|
Диаграмма с областями подчеркивает изменение значение величин с течением времени и может использоваться для привлечения внимания к суммарному значению в соответствии с тенденцией. |
|
|
График показывает тенденцию изменения величин через равные промежутки. |
|
|
Точечная диаграмма показывает отношения между численными значениями в нескольких рядах данных или отображает две группы чисел как один ряд данных с координатами x и y. |
|
|
Пузырьковая диаграмма это специфический тип диаграмм, способных отображать трехмерные данные в двумерном пространстве. Она сравнивает три набора значений, при этом размер пузырька, или маркер данных показывает значение третьей переменной. |
|
|
Круговая диаграмма отображает размер элементов, образующих ряд данных, пропорционально сумме элементов. |
|
|
Кольцевая диаграмма, как и круговая диаграмма, показывает отношение частей к целому, но этот тип диаграммы может включать несколько рядов данных. |
|
|
Лепестковые диаграммы позволяет сравнивать совокупные значения нескольких рядов данных. |
|
|
Поверхностная диаграмма целесообразно использовать для поиска наилучшего сочетания в двух наборах данных. |
|
|
Биржевая диаграмма чаще всего используется для иллюстрации изменений цен на акции. |
|
|
Аппроксимация
процесс подбора эмпирической функции f(х) для установления из опыта функциональной зависимости y=f(х).
Линия тренда
графическое представление направления изменения ряда данных.
Она представляют собой геометрическое отображение средних значений анализируемых показателей, полученное с помощью какой-либо математической функции. Линии тренда используются для анализа ошибок предсказания, что также называется регрессионным анализом.
Линия тренда всегда связана с рядом данных, но не представляет данные этого ряда. Она предназначена для отображения тенденций в существующих данных или прогнозов будущих данных.
Типы диаграмм, поддерживающие линии тренда
- ненормированные плоские диаграммы с областями,
- линейчатые диаграммы,
- гистограммы,
- графики,
- биржевые диаграммы,
- точечные диаграммы,
- пузырьковые диаграммы.
Типы линий тренда:
1. Линейная обычно применяется в простейших случаях, когда экспериментальные данные возрастают или убывают с постоянной скоростью. |
 |
2. Полиномиальная используется для описания экспериментальных данных, попеременно возрастающих и убывающих. |
 |
3. Логарифмическая применяется для описания экспериментальных данных, которые вначале быстро растут или убывают, а затем постепенно стабилизируются. |
 |
4. Степенная полезна для описания монотонно возрастающей либо монотонно убывающей величины. |
 |
5. Экспоненциальная применяется для описания экспериментальных данных, которые быстро растут или убывают, а затем постепенно стабилизируются. |
 |
| 6. Скользящее среднее (линейная фильтрация) позволяет сгладить колебания данных и таким образом более наглядно показать характер зависимости. Такая линия тренда строится по определенному числу точек (оно задается параметром Шаг). Элементы данных усредняются, и полученный результат используется в качестве среднего значения для приближения. |
Степень близости аппроксимации экспериментальных данных выбранной функцией оценивается коэффициентом детерминации (величина достоверности аппроксимации) (R^2).
Коэффициент детерминации для модели с константой принимает значения от 0 до 1. Чем ближе значение коэффициента к 1, тем сильнее зависимость. При оценке регрессионных моделей это интерпретируется как соответствие модели данным..
При аппроксимации данных с помощью линии тренда значение величины достоверности аппроксимации рассчитывается приложением Excel автоматически. При необходимости полученный результат можно показать на диаграмме.
Контрольные вопросы
- Дайте определение электронной таблицы, табличного процессора.
- Перечислите основные возможности табличного процессора MS Excel.
- Какие данные могут быть введены в ячйку рабочего листа MS Excel?
- Перечислите правила ввода числовых, текстовых данных.
- Перечислите основные числовые форматы MS Excel.
- Какие системы дат используются в MS Excel? В чем особенность каждой из них?
- Опишите технологию работы с формулами в MS Excel.
- Что такое функция? Перечислите основные категории функций MS Excel.
- Какие типы ссылок используются в MS Excel?
- Что такое диаграмма? Какие типы диаграмм представлены в MS Excel?
- Опишите технологию построения диаграмм в MS Excel.
- Что такое линия тренда? Каким образом она может быть построена в MS Excel?
- Какие типы диаграмм поддерживают (не поддреживают) возможность добавления линии тренда?