Ввод, редактирование и форматирование данных.

EXCEL

Лабораторная работа №1

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

Краткие теоретические сведения

Excel является программой для расчетов с помощью электронных таблиц.

Различают пять видов отображения документа Excel – «обычный», «разметка страницы», «страничный режим», «представления» и «во весь экран», которые можно установить в меню Вид.

Параметры страницы настраиваются в меню Разметка страницы/Параметры страницы. Здесь же можно установить Верхний и Нижний колонтитулы на странице. В верхнем колонтитуле можно указать, например, номер группы, в нижнем – ФИО студента. Во вкладке «Лист» можно настроить последовательность вывода страниц.

Рабочая книга. Документ Excel представляет собой рабочую книгу, состоящую из совокупности рабочих листов, сохраняемых на диске в едином файле. По умолчанию в книге 3 листа. Это количество можно изменить. Также в книгу в любое время можно добавить или удалить листы (через контекстное меню с пом. правой кнопки). Листы в книге можно склеить (кн.Shift+щелчки на тех листах, которые надо склеить). Информация, записанная на склеенных листах, одинакова. Например, если надо создать один и тот же шаблон таблицы на нескольких листах, их надо склеить, один раз создать таблицу, затем листы «разгруппировать» через контекстное меню. На всех листах, которые были склеены, будет одна и та же таблица.

Кроме рабочих листов, в рабочей книге можно хранить диаграммы, построенные на основе данных одной или нескольких таблиц, и макросы. Макрос – это программа на языке Visual Basic, обрабатывающая данные таблицы.

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

Рабочий лист. Состоит из электронных ячеек, имеющих адрес: А1, В10 и т.д. Адрес текущей ячейки отображается в поле имени (крайнее левое поле строки формул). Заголовки столбцов – A…Z, AA…AZ, BA…BZ и т.д. (А…XFD). Заголовки строк: 1 до 2²⁰ (1048576).

Данные ячеек. В ячейки можно вводить два вида данных: постоянные значения и формулы. Постоянные значения вводятся непосредственно в ячейку, они не меняются при копировании. Формулы служат для организации вычислений. При копировании формул значения данных в ячейках меняются.

Существует два представления данных ячейки: внутримашинное и экранное. Внутримашинное используется для вычислений, это внутренние значения ячеек, а не отображаемые на экране. Экранное представление определяется форматом ячейки.

В ячейках могут находиться следующие типы данных: числа, текст, дата и время, логические значения, ошибочные значения.

Числа. Числа хранятся в машине с наибольшей точностью. Экранное представление числа определяется форматом: Главная/ Число/ Числовые форматы. Можно вводить целые числа, десятичные дроби или числа в степенной (экспоненциальной) форме. Если ячейка заполняется знаками (диез), это значит, что вводимое число превышает ширину столбца.

Текст. Это любой введенный набор символов, которые Excel не воспринимает как число, дату и время, логическое значение или ошибочное значение. В ячейку можно ввести до 255 символов текста. Чтобы ввести в формулу числа как текст, надо заключить его в кавычки. =”45,00”.

Форматирование текста: Главная/ Выравнивание, Шрифт, в которых также можно настроить команды форматирования Граница, Заливка.

Дата и время. Дата представлена в машине в виде числа, определяемого количеством дней от системной даты (1900) до представленной в

ячейке. Это можно увидеть, если в ячейке с датой выбрать формат «Общий». Дата 22.01.2005 эквивалентна числу 38374 дней от 01.01.1900 , а дата 07.01.2005 – числу38359 дней от 01.01.1900. Поэтому над датами можно производить операции сложения и вычитания (в ячейках с датой «15.01.1900» и с числом «15» находится формула =A1-B1, вычисляющая количество дней между датами «22.01.2005-07.01.2005». Разница равна 15). Время представлено в машине в виде дроби. Это также можно увидеть, если в ячейке со временем выбрать формат «Общий». Время 16:14 эквивалентно дроби 0,6763889.

Экранное представление даты и времени определяется также в меню Главная/Число/Числовые форматы. Чтобы быстро ввести в ячейку текущее время, надо нажать Ctrl+<:>, а для текущей даты – Ctrl+<;>.

Логические значения принимают значения «истина» и «ложь». Данные значения являются результатом выполнения логических операций и операций сравнения.

Ошибочные значения являются результатом ошибочных вычислений. Ошибочные значения начинаются со знака диез : н/д! (недопустимое значение), ссылка! (неверная ссылка), знач!(неверный тип аргумента в функции), имя!(не понимает имя), число!(не может правильно истолковать формулу в ячейке) и т.д.

Диапазон ячеек – группа последовательных ячеек. В ссылках на диапазон используются следующие адресные операции:

: (двоеточие) –позволяет ссылаться на все ячейки между границами диапазона вклю-

чительно (А1:В15);

, (запятая) – оператор объединения диапазонов ячеек или отдельных ячеек

(А2:В5, А6);

ٮ (пробел) – оператор пересечения, который ссылается на общие ячейки диапазонов,

Β5:B15ٮ A7:D7. В этом примере ячейка В7 является общей для двух диапазонов.

Ввод, редактирование и форматирование данных.

Различают непосредственный ввод данных и использование средств автоматизации при вводе.

Непосредственный – непосредственный ввод данных в текущую ячейку. Для завершения ввода в текущей ячейке и для перехода к последующей ячейке надо нажать одну из следующих клавиш

Вниз	Enter
Вверх	Shift+Enter
Направо	Tab
Налево	Shift+Tab

При вводе одних и тех же данных в диапазон надо: Выделить диапазон – Ввести данные в активную ячейку диапазона – нажать Ctrl+Enter.

Автоматизация ввода.

Автозавершение. При вводе текста Excel проверяет, есть ли такой текст выше в столбце по одной первой букве. Если есть слова с начатой буквы, то текст появляется автоматически и если он устраивает Вас, то Enter (автозавершение).
Автозаполнение. В правом нижнем углу рамки текущей ячейки имеется черный квадратик – маркер заполнения .При наведении на него указатель мыши приобретает форму тонкого черного крестика . При перетаскивании левой кнопкой идет операция копирования, если копирование началось с одной заполненной ячейки. А если перетаскивание левой кнопкой началось с двух заполненных ячеек, то идет операция заполнения арифметической прогрессией с шагом, равным разности значений этих ячеек. При перетаскивании правой кнопкой (специальное перетаскивание) появляется контекстное меню: при выборе пункта «Копирование» будет произведено копирование содержимого ячейки; при выборе пункта «Прогрессия» открывается окно «Прогрессия», где можно выбрать тип прогрессии, величину шага и предельное значение; и другие.

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

Редактирование.

Операции редактирования можно разделить на следующие две группы:

Редактирование введенных в ячейку данных. Содержимое ячеек можно редактировать как непосредственно в ячейке (двойной щелчок на ячейке), так и в строке формул (щелчок в правой стороне строки формул), при этом в строке состояния появляется слово «Правка». В этом режиме становятся доступными все средства редактирования.
Редактирование на уровне ячеек, диапазонов, строк, столбцов. В основном это команды редактирования меню Главная/ячейки.

Форматирование.

Все команды форматирования данных, строк, столбцов, листов и др. сосредоточены в меню Главная/число и Главная/ячейки/формат.

Диаграммы в Excel.

Диаграмма включает много объектов, каждый из которых можно выделять и изменять (редактировать и форматировать) отдельно. При перемещении указателя мыши по диаграмме около него появляется всплывающая подсказка, указывающая тип объекта, около которого находится указатель.

Объекты диаграммы. Ось (Х – ось категорий, У – ось значений). Точка данных – один элемент данных, например, з/плата за январь. Ряд данных – совокупность точек данных (хорошо виден на графике – все точки ряда данных соединены одной линией). Легенда – значки, узоры, цвета, используемые для различения рядов данных. Маркер данных – представляет точку данных на диаграмме в виде прямоугольника, сектора, точки и т.д., вид маркера зависит от вида диаграммы; все маркеры одного ряда данных имеют одинаковую форму и цвет. Текст – все надписи (название диаграммы, значения и категории на осях) и подписи (тест, связанный с точками данных); для надписей и подписей надо выделить диаграмму и применить команды «Конструктор/макеты диаграмм» либо «Макет/подписи», либо «Макет/вставить/ надпись»

Правила, используемые Excel по умолчанию при построении диаграмм.

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

2. Если выделен квадратный диапазон ячеек или он занимает по ширине больше ячеек, чем по высоте, то названия категорий будут расположены в верхней строке диапазона. Если больше ячеек по высоте, чем по ширине, то названия категорий идут вниз по левому столбцу. А если в ячейках, которые Excel будет использовать как названия категорий, содержатся числа (не текст и не даты), то Excel предполагает, что в этих ячейках содержится ряд данных, а названия категорий нумерует как 1, 2, 3, 4 и т.д.

3. Excel предполагает, что названия вдоль короткой стороны выделения должны использоваться как метки легенды для каждого ряда данных. Если ряд данных один, то Excel использует это название в качестве заголовка диаграммы. А если в ячейках, которые Excel намерен использовать как метки легенды, содержатся числа (не текст и не даты), то Excel предполагает, что в этих ячейках содержатся первые точки рядов данных, а каждому ряду данных присваивает имя: «Ряд1», «Ряд2» и т.д.

Задания к лабораторной работе №1.

Создать и отформатировать электронную таблицу как на рисунке

Числа в ячейках за Январь, Февраль, Март, Апрель ввести вручную. В ячейке G3 использовать формулу для вычисления суммы ячеек C3:F3, затем эту формулу скопировать в ячейки G4:G11. В ячейке G12 использовать формулу для вычисления суммы ячеек C12:F12.

Построить на базе созданной таблицы две диаграммы:

гистограмму с одной осью Y;

В качестве данных взять данные из ячеек B2:F2 и B12:F12. Сначала маркировать ячейки B2:F2, затем при нажатой клавише Ctrl маркировать ячейки B12:F12. Диаграмму отформатировать как на рисунке. Каждый объект диаграммы форматируется и настраивается отдельно.

диаграмму с основной и со вспомогательной осями Y, два ряда данных при этом представить в виде графиков

В качестве исходных данных используйте данные о заработной плате сотрудников за месяц январь и данные о суммарной плате «Итого», т.е. на одном графике совмещается разнохарактерная информация (для этого сначала маркируйте ячейки C2:C11, затем при нажатой клавише Ctrl маркируйте ячейки G2:G11). Диаграмму отформатировать как на рисунке. Каждый объект диаграммы форматируется и настраивается отдельно. Данные за январь расположить по вспомогательной оси Y (для этого надо выделить ряд данных за Январь и выполнить пункт меню «Макет/формат выделенного ряда» включить переключатель «по вспомогательной оси»).

Построить смешанную диаграмму, в которой один ряд данных представлен в виде гистограммы в абсолютных значениях, а второй ряд данных – в виде графика в относительных значениях. Для этого проделайте следующие действия:

1) в столбце А наберите следующие данные: 100, 105, 110, 130, 130, 150, 157, 175, 180, 190, 195, 200;

2) перед столбцом А вставьте еще один столбец и автоматически заполните его датами. Для этого введите в ячейку А1 дату 01.01.2019. Маркируйте ячейки А1:А12 правой кнопкой мыши, из контекстного меню дайте команду «прогрессия», в окне «прогрессия» включите флажок «месяц». В меню Формат/Ячейки/Число/Числовые форматы выберите формат даты как на рисунке;

3) в ячейке В13 определите сумму значений ячеек В1:В12;

4) в ячейках С1:С12 для каждого значения ячеек В1:В12 рассчитайте соответствующее значение в процентах, для чего в ячейку С1 запишите формулу =В1/$В$13 и скопируйте ее в ячейки С2:С12. Установите для ячеек С2:С12 числовой формат «Процентный» без десятичных знаков;

5)вставьте перед первой строкой еще одну строку, в ячейку В1 введите название «Параметр Х», в ячейку С1 введите название «Процент». Окончательный вид таблицы будет иметь вид как на рисунке:

6) Для построения заданной диаграммы маркируйте область А1:С13. Ряд данных С2:С13 поместите по вспомогательной оси Y и с помощью правой кнопки выберите для него команду «Изменить тип диаграммы для ряда». Отформатируйте и настройте диаграмму так, как показано на рисунке:

Вопросы

Что из себя представляет документ в Excel? Что, кроме рабочих листов, можно хранить в рабочей книге?
Виды отображения документа в Excel.
Как устанавливаются параметры страницы в Excel? Какие это параметры?
Можно ли склеить листы в книге? Если можно, то для чего это нужно?
Расскажите, как устроен рабочий лист в книге? Каково максимальное количество строк и максимальное количество столбцов в рабочем листе книги?
Какие два вида данных можно вводить в ячейки?
Что такое внутримашинное и экранное представление данных ячейки?
Перечислите и кратко опишите типы данных, которые могут находиться в электронных ячейках рабочего листа.
Что означают знаки (диез) в ячейках? Что означают ошибки #число!, #имя!, #знач!, #ссылка, #н/д?
Что такое диапазон ячеек и какие адресные операции используются в ссылках на диапазон ячеек?
Какие средства автоматизации ввода данных имеются в Excel?
На какие группы можно разбить операции редактирования данных?
Как осуществляется форматирование данных, строк, столбцов и рабочих листов книги?
Перечислите и кратко охарактеризуйте основные объекты диаграмм.
Область данных маркирована следующим образом . Нарисуйте диаграмму, которую Excel построит по умолчанию. Что пойдет в ряды данных, в метки легенды, в названия категорий? Что изменится на диаграмме, если маркированная область примет вид

Что изменится на диаграмме, если маркированная область примет вид ?

Область данных маркирована следующим образом . Нарисуйте диаграмму, которую Excel построит по умолчанию. Что пойдет в ряды данных, в метки легенды, в названия категорий? Что изменится на диаграмме, если маркированная область примет вид

Что изменится на диаграмме, если маркированная область примет вид ?

Сформулируйте основные правила, которые Excel использует при построении диаграмм.
Как можно создать диаграмму с двумя шкалами (смешанную и диаграмму со вспомогательной осью)?

Построение формул в Excel.

Формулы подчиняются определенному синтаксису, т.е. правилам, определяющим запись формулы:

· формула всегда начинается со знака « = » ;

· формула активной ячейки отображается в строке формул;

· основными элементами формул могут быть: константы, ссылки на ячейки, имена, операции ( арифметические, сравнения, операция с текстом & ), функции.

Константы – числовые и текстовые величины. Текстовые заключаются в кавычки “Текст”.

Ссылки. Ссылка однозначно определяет ячейку или диапазон ячеек (А4 или А4:С4). Ввод ссылок в формулу можно осуществить непосредственно с клавиатуры, а также с использованием мыши (установить место вставки в формулу, выделить необходимую ячейку или диапазон ячеек, при этом выделенные объекты обводятся подвижной рамкой, а ссылка появляется в формуле).

Различают следующие типы ссылок: относительные, абсолютные, внешние и множественные ссылки. Тип ссылки обнаруживается при операциях копирования с формулами.

По умолчанию ссылки в формулах рассматриваются как относительные. Это означает, что при копировании формул действует правило относительной ориентации ячеек, которое заключается в следующем: адреса в ссылках автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемой копии. Пусть, например, в ячейке В2 имеется ссылка на ячейку А3. В относительном представлении можно сказать, что ссылка указывает на ячейку, которая располагается на один столбец левее и на одну строку ниже данной. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку D3 ссылка будет продолжать указывать на ячейку , расположенную левее и ниже, в данном случае на ячейку С4.

При абсолютнойадресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать кл F4. Элементы номеров ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательном нажатии кл. F4 номер ячейки А1 будет записываться как $А$1, А$1, $А1. В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой – как относительный.

Внешниессылки – это ссылки на ячейки других листов, Например, Лист3!С5.

Множественные ссылки разделяются точкой с запятой, например: A4; C5; C10:E20.

Имена. Имя – идентификатор, который используется для ссылки на ячейку или диапазон ячеек. Преимущества использования имен:

формулы с именами легче воспринимаются и запоминаются, чем формулы со ссылками;
при изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте – определении имени.

Назначение имени – Выделить ячейку или диапазон / дать команду Формулы/Присвоить имя.

Вставка имени в формулу – Установить место вставки в формулу / дать команду Формулы/Использовать имя.

Диапазон действия имен – вся рабочая книга. По умолчанию имена являются абсолютными ссылками.

Операции.

Арифметические - +, -, - ( унарный минус), *, /, % (процент), ^ (возведение в степень). Результатом выполнения арифметической операции всегда будет число.

Сравнения - =, >, <, >=, <=, <> (не равно). Результатом выполнения операций сравнения является логическое значение – «ложь» или «истина».

Текстовый оператор & (амперсант) служит для объединения последовательностей символов в одну последовательность. Результатом выполнения выражения “Результата” & “ расчета” будет “результаты расчета”.

Функции.

Формула может содержать и функции. Каждая функция имеет имя и аргументы. В общем виде функцию можно представить как

имя ( арг1, арг2, …).

Число аргументов может достигать до 30 в Excel 2003 и до 255 в Excel 2007. В качестве аргументов м.б. использованы числа, текст, логические значения (истина или ложь), массивы, значения ошибок (например, Н/Д), ссылки, выражения, другие функции.

В одной формуле допускается вложение функций друг в друга. Это позволяет организовать многоуровневые вычисления. Допускается восьмикратное вложение функций Excel 2003 и до 64 вложений Excel 2007.

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

Математические функции.

АВS(), LOG10(), TAN(), EXP(), LN(), SIN(), COS(), КОРЕНЬ(), ФАКТР(), СУММ(),ГРАДУСЫ(радианы), ОРКРУГЛ(число, чосло_разрядов), ОСТАТ(число, делитель), ПРОИЗВЕД(), РАДИАНЫ (угол), СТЕПЕНЬ() и др.

Статистические функции

СЧЕТ (знач1; знач2; ….) – подсчитывает только числа в списке аргументов, хотя в качестве аргументов м.б. данных других типов;

СЧЕТЕСЛИ (диапазон; критерий) – подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному критерию;

СЧИТАТЬПУСТОТЫ (диапазон) – подсчитывает количество пустых ячеек в диапазоне;

СРЗНАЧ() и многие другие.

Текстовые функции

ДЛСТР (текст) – возвращает количество знаков в текстовой строке;

КОДСИМВ (текст) – возвращает числовой код первого символа в строке;

ЛЕВСИМВ (текст; количество_символов) – возвращает указанное количество символов с начала строки;

ПРАВСИМВ (текст; количество_символов) – возвращает указанное количество символов с конца строки;

СИМВОЛ (число) – возвращает символ с заданным кодом;

СОВПАД (текст1; текст2) – проверяет идентичность двух строк текста, и возвращает значение ИСТИНА или ЛОЖЬ, прописные и строчные символы различаются;

СЦЕПИТЬ (текст1; текст2; …) – объединяет несколько текстовых строк в одну;

ТЕКСТ (значение; формат) – форматирует число и преобразует его в текст.

Логические функции

ЕСЛИ, ИСТИНА, ЛОЖЬ, И(), ИЛИ(), НЕ().

Задания к лабораторной работе № 1.

Вычислить значения функций y = f(a, b) для заданных a и b. При этом вычисление y производить посредством не менее чем трех формул с получением промежуточных значений

№ варианта	Значения
№ варианта	a	b
1	4	0,5
2	2	14,36
3	10	0,5
4	2	11,05
5	3	0,151
6	4	1,31
7	2	200
8	1	12б21
9	3	0,521
10	2	12,11
11	1	20,01
12	3	0,707
13	3	2,712
14	2	19,03
15	4	300,1

Ввести в диапазон A1:F6 постоянные целые числа. Затем очистить ячейки A1, B5, F2, C6, D3. Подсчитать:

· количество пустых ячеек;

· количество заполненных ячеек;

· среднеарифметическое диапазона.

Ввести в диапазоны A1:C5, B3:D6 целые числа. Подсчитать сумму чисел, общих для данных интервалов (использовать знак адресной операции «пробел» и функцию СУММ()).
В ячейки A1:A4 ввести разные тексты. В ячейках Β1:B4 определить количество символов в каждом тексте.
В ячейки С1:С4 ввести разные тексты. В ячейках D1:D4 для каждого текста из ячеек С1:С4 вывести по три символа слева, а в ячейках Е1:Е4 – по два символа справа.
Заполнить 6 любых ячеек числами. Подсчитать количество ячеек, в которых находится число 0.
Определите средствами Excel, какие символы соответствуют десятичным кодам 85,86, 190, 180?
Определите средствами Excel, какой код соответствует первому символу вашего имени?
В две ячейки введите два текста. Определите средствами Excel, совпадают эти тесты или нет?
Проверить результаты следующих логических выражений: И(2+2=4; 2+3=5), ИЛИ(1+1=1; 2+2=5).
Составить таблицу истинности для логических функций И, ИЛИ, НЕ.

Вопросы на защиту:

1. Какой синтаксис используется при написании формул в Excel?

2. Перечислите и кратко охарактеризуйте основные элементы формул в Excel.

3. Поясните, что такое относительные и абсолютные ссылки в Excel?

4. Поясните что такое внешние (трехмерные) и множественные ссылки в Excel?

5. Что такое имя? Как присваиваются имена в Excel? Каковы преимущества использования имен? Как имена вставляются в формулы? Какой ссылкой по умолчанию является имя?

6. Как записывается функция Excel в общем виде? Что может быть использовано в качестве аргументов в функции и каково их максимальное число?

7. Перечислите из каждой категории известные вам встроенные функции и объясните их назначение.

8. Допускаются ли вложения функций в Excel и каков максимальный уровень вложенности функций допускается в Excel?

9. Как можно посчитать количество пустых ячеек? Количество заполненных ячеек? Среднее арифметическое для значений из нескольких ячеек?

10. Покажите на примере, как используется адресная операция «пробел»?

11. Приведите примеры на использование встроенных текстовых функций.

12. Объясните работу логических функций «И», «ИЛИ», «НЕ». Приведите примеры.

Дата добавления: 2022-01-22; просмотров: 18; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!