Типы и формат данных. Математическая обработка числовых данных
Лекция № 18. Табличный процессор MS Excel: назначение и возможности.
План
Электронные таблицы: назначение, основные функции.
Основные элементы: ячейка, столбец, лист, книга.
Типы и формат данных. Математическая обработка числовых данных.
Система статистического учёта (бухгалтерский учёт, планирование и финансы, статистические исследования).
Электронные таблицы: назначение, основные функции.
Прикладная программа Microsoft Excel 2007, которая является одним из компонентов Microsoft Office 2007, предназначена для работы с электронными таблицами данных. Excel часто называют табличным процессором.
ОПРЕДЕЛЕНИЕ:Электронные таблицы (табличные процессоры) — прикладное программное обеспечение общего назначения, предназначенное для обработки различных данных, представимых в табличной форме. Электронная таблица (ЭТ) позволяет хранить в табличной форме большое количество исходных данных, результатов, а также связей (алгебраических или логических соотношений) между ними.
Первые ЭТ появились в 1979 году.
Назначение. ЭТ предназначены для экономистов, бухгалтеров, инженеров, научных работников – всех тех, кому приходится работать с большими массивами числовой информации.
Основные элементы: ячейка, столбец, лист, книга.
Основным понятием табличного процессора является понятие электронной таблицы(ЭТ), которая представляет собой лист с клетками.Наиболее близкий аналог таблицы – это тетрадный лист в клеточку. Этиклетки называют ячейками, поэтому листможно определить как совокупность ячеек.
|
|
|
Ячейка(cell) – это область, которая может содержать текст, формулу, число или дату, обладает именем, которое также называют адресом ячейки или ссылкой.
Рабочее поле состоит из строк и столбцов. Строки нумеруются числами от 1 до 65536. Столбцы обозначаются латинскими буквами: А, В, С, …, АА, АВ, … , IV , всего – 256. На пересечении строки и столбца находится ячейка. Каждая ячейка имеет свой адрес: имя столбца и номер строки, на пересечении которых она находится. Например, А1, СВ234, Р55.
В ячейки могут вводиться данные, на которые можно ссылаться по именам ячеек. К данным относятся: числа, даты, время суток, текст или символьные данные и формулы.
К обработке данных относится:
· проведение различных вычислений с помощью формул и функций, встроенных в Excel
· построение диаграмм
· обработка данных в списках Excel (Сортировка, Автофильтр, Расширенный фильтр, Форма, Итоги, Сводная таблица)
· решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии "что - если" и т.д.)
|
|
|
· статистический анализ данных (инструменты анализа из надстройки "Пакет анализа")
Объектамиэлектронной таблицы являются также:
· Диапазон– прямоугольная область ячеек. Адресуется от верхнего левого до нижнего правого угла с разделителем “двоеточие” – например: M17:AD22.
На рис. 2 выделен диапазон B3:C5. Обратите внимание на то, что первым всегда идет адрес верхней левой ячейки, а вторым адрес нижней правой ячейки диапазона.
Итак, Excel является приложением, которое имеет различные инструменты (меню и панели инструментов) для создания и обработки электронных таблиц. При запуске Excel на экране отображается окно приложения, в котором открывается новая чистая рабочая книга:Книга1, можно создавать книги и на основе шаблонов, встроенных в редактор.
Рабочая книга Excel состоит из рабочих листов, каждый из которых является электронной таблицей. По умолчанию открывается три рабочих листа, переход к которым можно осуществить, щелкая на ярлычках, расположенных внизу книги. При необходимости в книгу можно добавить рабочие листы или удалить их из книги.
Книга с входящими в нее рабочими листами сохраняется на диске в виде отдельного файла с уникальным именем. Файлы книг имеет расширение xls.
|
|
|
Типы и формат данных. Математическая обработка числовых данных.
Данные в ячейках могут отличаться по типу. Важно понимать, что означает тип, или формат, данных, поскольку ошибочно форматированные ячейки могут вызывать погрешности и ошибки в вычислениях.
Ячейки рабочего листа электронной таблицы могут содержать:
· исходные или первичные данные – константы;
· производные данные, которые рассчитываются с помощью формул или функций.
Данные в ячейках таблицы могут относиться к одному из следующих типов: текст, числа, даты, формулы и функции.
Текст – последовательность букв, иногда цифр или некоторых специальных символов.
Числа могут включать цифры и различные символы: знак процента, знак мантиссы, круглые скобки, денежные обозначения, разделители и др. Например: 5; 3,14.
Формат числа может быть:
· целым или с дробной частью (например, 123, –456 или –123,45);
· дробным (например, 1/4 или 23/45);
· денежным (например, 123 р., –123,45р., 123,4512 р. или 123,4 р.);
· датой (например, 15.11.99 или 21 мар 98);
· временем (например, 14:30 или 2:30:53 PM).
Для целых и дробных чисел могут применяться разделители (разрядов) при вводе больших чисел (точки, пробелы и т.д.). Для целого, дробного денежного форматов чисел можно задать количество знаков в дробной части (после запятой).
|
|
|
Формат числа может быть задан принудительно: щелкните правой кнопкой мыши на данной ячейке, откроется контекстное меню, выберите Формат ячеек
· Денежный. Этот формат используют для ввода различных денежных величин. По умолчанию число округляется до двух знаков после запятой (до копеек), но это количество знаков можно изменить. Кроме того, в конце значения указывается валюта. По умолчанию добавляется обозначение валюты, используемое в Windows, то есть в русской версии к числу добавляется р.. Можно изменить это обозначение на любое другое (английский фунт, американский доллар, японская йена и т. д.). Конечный формат данных выглядит как, например, 1 200,00 р., причем р. программа вводит автоматически. Вам нужно указать только число.
· Финансовый. Такой же формат, что и денежный, но данные в столбцах выравниваются по разделителю целой и дробной части.
· Дата. В ячейках можно указывать даты в различных форматах, например 12.01.2007 или 12 января 2007 г. Причем вам достаточно ввести дату в формате 12/01/2007, а программа сама преобразует этот текст в выбранный вами формат. Даты могут участвовать в формулах. Например, если к дате 12/01/2007 добавить единицу, мы получим 13/01/2007.
· Время. Формат вывода данных в ячейках такого типа можно выбрать. Например, время отображается в формате 13:15, 1:15PM, 13:15:37 (здесь добавлены секунды) и т. д. Ячейки в формате Время также могут участвовать в формулах. Если к значению времени добавить 0,000012, мы увеличим время на 1 секунду. Число 0,000012 является приблизительным, потому что вычисление времени ведется относительно суток, то есть за единицу времени берутся сутки. Отсюда получаем: сутки/количество часов в сутках/количество минут в часе/количество секунд в минуте, то есть 1/24/60/60 ≈ 0,000 012.
· Процентный. Данный формат обладает всеми свойствами формата Числовой. При этом число умножается на 100, а в конец добавляется символ %.
· Дробный. Данные в ячейку вводят обычным способом, но по окончании вода (или вычисления) в ячейке отображается обычная (не десятичная) дробь. Например, если мы введем в ячейку число 2,5, то это число отобразится как 2 Ѕ (две целые и одна вторая).
· Экспоненциальный. Введенное число указывается в ячейке в экспоненциальном виде. Иными словами, если вы введете число 25000, в ячейке отобразится значение 2,50E+04, что означает 2,5, умноженные на 10 в степени 4. Такой формат данных широко используется в научных вычислениях при оперировании большими или очень малыми числами.
· Дополнительный. Данный формат включает в себя несколько видов данных: Почтовый индекс, Номер телефона и Табельный номер. При вводе номера телефона последние цифры отображаются как номер телефона. Если количество введенных цифр превышает 7, то первые цифры отображаются в скобках, как код города. Например, если ввести в ячейку число 5555555555, оно отобразится как (555) 555-5555.
Формула – это арифметическое или логическое выражение, по которому производятся расчеты в таблице. Формулы всегда начинаются со знака равенства (=) и вводятся в латинском регистре. Например: =А5*2/В1
Строка формул
В Excel 2007 удобнее, чем в предыдущих версиях организована работа со строкой формул.
Для просмотра и редактирования содержимого выделенной ячейки можно увеличить высоту строки формул. Для этого щелкните по кнопке Развернуть строку формул.
MS EXCEL располагает очень большим набором встроенных функций. С их помощью можно вычислять сумму или среднее арифметическое значений из некоторого диапазона ячеек, вычислять проценты по вкладам и т. д.
Функцияпредставляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов. Функции могут вводиться в таблицу в составе формул либо отдельно. Например, функция суммирования имеет вид =СУММ(А1:А2)
Для существа формул с функциями обыкновенно применяют категорию Библиотека функций вкладки Формулы.
Аргументы функции Excel - задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Аргументами функции могут быть: числа; ссылки на ячейки и диапазоны ячеек; имена; текст; другие функции; логические значения и др. Например, в формуле «=СУММ(A2;A9)», СУММ — это имя функции, а A2 и A9 — ее аргументы.
При копировании или перемещении формулы в другое место таблицы необходимо организовать управление формированием адресов исходных данных. Поэтому в электронной таблице при написании формул наряду с введенным ранее понятием ссылки используются понятия относительной и абсолютной ссылок, а также смешанной.
Формулы, реализующие вычисления в таблицах, для адресации ячеек используют так называемые ссылки. Ссылка на ячейку Excel может быть относительной или абсолютной.
Ссылки в Excel бывают трех типов:
- Относительные ссылки; например, A1;
- Абсолютные ссылки; например, $A$1;
- Смешанные ссылки; например, $A1 или A$1 (они наполовину относительные, наполовину абсолютные).
Использование относительных ссылок в Excel аналогично указанию направления движения по улице - "идти три квартала на север, затем два квартала на запад". Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.
Обычно ссылки на ячейки описываются и используются как относительные (формат записи А1). Когда формула, содержащая эти ссылки, копируется, происходит изменение формулы для поддержания относительности ссылок. Относительные ссылки создаются по умолчанию.
Например, формула, которая суммирует числа в столбце или строке, затем часто копируется для других номеров строк или столбцов. В таких формулах используются относительные ссылки.
«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной (рис. 2А). Здесь в ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше. При «протаскивании» формулы, например, в ячейку А7 (рис. 2Б) формула самопроизвольно изменяется.
Рис. 2. Относительные ссылки
В Excel абсолютная ссылка на ячейку или область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.
Абсолютная ссылка может быть создана только при наборе формулы, перед адресом строки и столбца вводится знак доллара - $.
Знак $ перед буквой или цифрой в обозначении ячейки говорит о том, что эта часть обозначения является абсолютной, то есть не будет изменяться при изменении ячейки, из которой делается ссылка. Сравните, как ведут себя формулы на рис. 2 и рис. 3. При «протаскивании» формула не меняется: и из ячейки А6, и из ячейки А7 ссылка идет на ячейки С2 и С3.
Рис. 3. Абсолютные ссылки
Для создания абсолютной ссылки в Excel удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.
Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).
Смешанные ссылкиявляются наполовину абсолютными и наполовину относительными. Иногда возникает необходимость закрепить адрес ячейки только по строке или только по столбцу. В таких случаях на помощь приходят смешанные ссылки. Рассмотрим их подробнее.
Например, нам требуется рассчитать отпускную стоимость товара при различных наценках, с учетом, что закупочная цена фиксирована (рис. 4).
Рис. 4. Расчет значений в таблице с использованием смешанных ссылок; цена за штуку – закупочная цена; в столбцах С, D и E показаны отпускные цены при различных наценках.
Нам необходимо записать в ячейку D4 такую формулу, которая бы при копировании в ячейки диапазона D4:F6 рассчитывала стоимость с учетом разных значений наценки.
При «протаскивании» формулы по столбцам нам необходимо, чтобы столбец С был зафиксирован. Аналогично, при «протаскивании» формулы по строкам, нам необходимо зафиксировать строку 3. В ячейке D4 таким образом получилась формула =$C4*(1+D$3); абсолютные ссылки я выделил жирностью и цветом. При протаскивании по диапазону D4:F6 такая формула дает правильные значения в каждой ячейке диапазона.
Система статистического учёта (бухгалтерский учёт, планирование и финансы, статистические исследования).
Пакет анализа. В состав MicrosoftExcel входит набор средств анализа данных (так называемый пакет анализа), предназначенный для решения сложных статистических и инженерных задач. Для проведения анализа данных с помощью этих инструментов следует указать входные данные и выбрать параметры; анализ будет проведен с помощью подходящей статистической или инженерной макрофункции, а результат будет помещен в выходной диапазон. Другие средства позволяют представить результаты анализа в графическом виде.
Excel содержит большой набор встроенных функций, которые можно использовать в формулах. Помимо обычных функций, таких как СУММ или СРЗНАЧ, в этот набор включены функции для выполнения более сложных операций, которые трудно или невозможно выполнить другим способом. Например, функция КОРРЕЛвычисляет коэффициент корреляции между двумя наборами данных. С помощью макроязыка VBA вы можете разработать свои функции (это не так трудно, как кажется).
Параметры вставки в документ встроенных функций находятся на вкладке Формулыв группе
Библиотека функций. Функции распределены по категориям в зависимости от типов задач, для
решения которых они нужны. О назначении той или иной функции можно прочесть во всплывающей подсказке, которая появляется при наведении указателя мыши на имя функции в меню (рис.).
Если вы хотите просмотреть полный список встроенных функций Excel, нажмите кнопку Вставить функцию, которая находится в строке формул. В открывшемся окне Мастера функций выберите в раскрывающемся списке Категорияпункт Полный алфавитный переченьи в списке ниже щелчком выделите имя функции, чтобы прочитать о выполняемых ею действиях.
Название встроенной функции можно ввести с клавиатуры (что крайне нежелательно ввиду
высокой вероятности ошибки), вставить из соответствующего меню кнопок, расположенных
в группе Библиотека функцийна вкладке Формулы, или же из окна Мастера функций. О двух последних вариантах будет рассказано в разделе «Построение графиков и диаграмм».
Часто применяемые на практике функции вынесены в меню кнопки 
, которая находится
в группе Редактированиена вкладке Главная. Рассмотрим задачи, связанные с их использованием.
Простейшие расчеты
Функция суммирования данных является самой востребованной, именно поэтому задействовать ее в Excel проще всего. Щелкнув на стрелке кнопки 
вы раскроете список команд, вызывающих функции, которые можно задействовать так же быстро, как и функцию суммирования. Схема действий
при их использовании не отличается от последовательности шагов для функции суммирования. Ниже приводится краткое описание функций, вызываемых командами кнопки.
Среднее— вызывает функцию =СРЗНАЧ(), с помощью которой можно подсчитать арифметическое среднее диапазона ячеек (просуммировать все данные, а затем разделить на их количество.
Число— вызывает функцию =СЧЕТ(), которая определяет количество ячеек в выделенном
диапазоне.
Максимум— вызывает функцию =МАКС(), с помощью которой можно определить самое
большое число в выделенном диапазоне.
Минимум— вызывает функцию =МИН()для поиска самого маленького значения в выделенном
диапазоне.
Результат работы некоторых из перечисленных функций можно видеть, не обращаясь непосредственно к ним самим. Выделите интересующий вас диапазон и посмотрите вниз
на строку состояния Excel. Слева от регулятора масштаба появятся значения суммы,
количества ячеек в диапазоне и среднего арифметического (рис. 19.9).
Комплексные расчеты
Вы изучили примеры простейших расчетов в Excel. Теперь попробуйте разобраться с комплексной задачей, требующей комбинации действий, рассмотренных ранее.
Задача 1.
Выбрать оптимальный тарифный план при подключении к сети сотовой связи, если в месяц планируется 2,5 часа разговоров внутри сети и 0,5 часа разговоров 12 с абонентами городской сети и других сотовых операторов. Цены на услуги представлены в таблице на рис. без учета НДС.
После выполнения всех операций таблица с расчетом должна принять примерно такой вид
Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения. Ниже представлен список операторов сравнения Excel
= Равно
> Больше
< Меньше
>= Больше или равно
<= Меньше или равно
<> Не равно
Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
Функция ЕСЛИ
Функция ЕСЛИ (IF) имеет следующий синтаксис:
=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)
Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:
=ЕСЛИ(А1>3;10;20)
В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:
=ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал")
Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0.
Например:
=ЕСЛИ(СУММ(А1:А3)=30;А10;"")
Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:
=ЕСЛИ(А1="Динамо";10;290)
Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.
Функции И, ИЛИ, НЕ
Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:
=И(логическое_значение1;логическое_значение2...)
=ИЛИ(логическое_значение1;логическое_значение2...)
Функция НЕ имеет только один аргумент и следующий синтаксис:
=НЕ(логическое_значение)
Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.
Приведем пример. Пусть Excel возвращает текст "Прошел", если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:
=ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")
Не смотря на то, что функция ИЛИимеет те же аргументы, что и И, результаты получаются совершенно различными. Так, если в предыдущей формуле заменить функцию И на ИЛИ, то ученик будет проходить, если выполняется хотя бы одно из условий (средний балл более 4 или пропуски занятий менее 3). Таким образом, функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.
Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.
Вложенные функции ЕСЛИ
Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Например, в следующей формуле используются три функции ЕСЛИ:
=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))
Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.
Функции ИСТИНА и ЛОЖЬ
Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:
=ИСТИНА()
=ЛОЖЬ()
Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:
=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")
В противном случае формула возвратит "Стоп".
Функция ЕПУСТО
Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:
=ЕПУСТО(значение)
Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.
Дата добавления: 2018-02-28; просмотров: 5147; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!