МЕТОДИЧЕСКИЕ УКАЗАНИЯ
ПО ИЗУЧЕНИЮ ОФИСНОГО ПРИЛОЖЕНИЯ MS Excel
для дисциплин «Информатика», «Информатика и математика» и «Информационные технологии в профессиональной деятельности»
Составитель доц. Л.В.Петрова
НОВОСИБИРСК, 2012
Кафедра автоматизированной обработки информации
Методические указания по изучению офисного приложения MS EXCEL / Новосиб. гос. аграр. ун-т; Сост.: Петрова Л.В. - Новосибирск, 2012. – 34 с.
Методические указания предназначены для аудиторной и самостоятельной работы по дисциплинам Информатика, Информатика и математика, Информатика и информационные технологии в профессиональной деятельности студентов Юридического факультета очной и заочной формы обучения.
Утверждены и рекомендованы к изданию методической комиссией Экономического факультета (протокол № ___ от ____________________ 2012 г.).
Рецензент: доц. каф. АОИ Черношейкина Н.В.
© Новосибирский государственный аграрный университет, 2012
Введение
Основной целью изучаемых дисциплин «Информатика», «Информатика и математика», «Информатика и информационные технологии в профессиональной деятельности» является формирование у студентов основ информационной культуры, адекватной современному уровню и перспективам развития информационных процессов и систем, а также формирование у студентов знаний и умений, необходимых для свободной ориентировки в информационной среде и дальнейшего профессионального самообразования в области компьютерной подготовки.
Офисное приложение MS EXCEL изучается в указанных дисциплинах и предназначено для приобретения практических навыков работы с электронными таблицами, создания новых таблиц, создание новых документов с использованием стандартных шаблонов, редактирование уже существующих таблиц любой сложности.
Изучение офисного приложения MS EXCEL начинается с описания типа данных, что является первой задачей при создании электронной таблицы. Во второй части методических указаний описаны основные операции с данными, что повышает эффективность работы с электронными таблицами. Третья часть посвящена описанию инструментов для оформления таблиц, что создает читабельность таблиц. В четвертой части описаны приемы управления листами. В пятой части приведено краткое описание построения диаграмм, необходимых для наглядного воспроизведения табличных данных.В конце методических указаний приведены приложения, в которых для закрепления материала предлагаются варианты таблиц.
Методические указания могут быть использованы для аудиторной и самостоятельной работы.
Рекомендации по проведению занятий:
В конце каждого раздела методических указаний даны задания. Все задания студенты сохраняют в отдельной папке.
После выполнения заданий студентам предлагается создать:
- Таблицу 1 (Приложение 2),
- Таблицу 2 (Приложение 3),
- Таблицу по заданному варианту (Приложение 4),
- Таблицу 4 (Приложение 5),
- Таблицу 5 (Приложение 6).
Типы данных
Excel позволяет вводить в ячейки три типа данных: числа, текст, формулы. Текст может использоваться для заголовков таблиц, объяснения или пометок на рабочем листе. Для отображения текста используется Текстовый формат. Если Excel не распознает тип данных как числовой или как формулу, то данные воспринимаются как текст.
Числа используются для представления цифровой информации и могут быть введены в различных форматах:
- общий формат – используется по умолчанию. В большинстве случаев введенные числа при общем формате отображаются так, как вводятся;
- денежный формат – используется для обозначения денежных значений. При этом рядом со значением отображается символ денежной единицы. В его настройках можно задать количество отображаемых знаков после запятой, применение разделителя групп разрядов и способ отображения отрицательных чисел;
- финансовый формат – используется для отображения денежных значений, но при этом выравнивает в столбце значения денежных единиц и десятичные разделители;
- процентный формат – используется для отображения числа со знаком процента;
- дробный формат – этот формат отображает число в виде дроби в соответствии с заданным типом дроби;
- экспоненциальный формат – используется для отображения числа в экспоненциальном представлении.
Дата и время могут также рассматриваться как числа.
Формулы, введенные в ячейку, производят вычисления, управляют работой базы данных, проверяют свойства и значения ячеек и используются для задания связи между ячейками и массивами с помощью адресных ссылок.
Ввод данных
Данные набираются непосредственно в активной ячейке Excel, при этом они отображаются в строке формул. Также данные могут набираться и в строке формул.
Последовательность действий при вводе чисел, текста, даты или времени суток:
· укажите ячейку, в которую необходимо ввести данные;
· наберите данные и нажмите клавишу ENTER или клавишу табуляции;
· для отмены ввода нажмите клавишу ESC;
· при вводе даты используйте точку или дефис в качестве разделителя, например, 09.05.96 или Янв-96.
Примечание 1: при вводе в строке формул щелкните клавишу ENTERдля подтверждения, а для отмены ввода нажмите клавишу ОТМЕНА.
Примечание 2: нажатие клавиш перемещения курсора или щелчок по другой ячейке таблицы Excel будет всегда приводить к сохранению набранных данных в активной ячейке перед перемещением к следующей.
Ввод текста
В Microsoft Excel текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов; например, приведенные ниже записи интерпретируются как текст:
10AA109, 127AXY, 12-976, 208 4675.
Введенный текст выравнивается в ячейке по левому краю. Чтобы изменить выравнивание, выделите ячейки и на вкладке Главная выберите в группе Выравнивание необходимые параметры.
Примечание: окно Формат ячейки открывается при нажатии кнопки в нижнем правом углу группы Выравнивание на вкладке Главная.
Ввод чисел
В Microsoft Excel число может состоять только из следующих символов:
0 1 2 3 4 5 6 7 8 9 + - (), / $ %. E
Стоящие перед числом знаки плюс (+) игнорируются, а запятая интерпретируется как разделитель десятичных разрядов.
Перед отрицательным числом необходимо вводить знак минус (-).
Числа, которые слишком велики, чтобы быть показанными внутри текущей ячейки, будут отображаться как последовательность символов # # # #. Чтобы показать числовое значение в ячейке, надо увеличить ширину столбца таблицы Excel.
Для изменения формата ячейки используйте группу Число на вкладке Главная.
Введенные числа выравниваются в ячейке по правому краю. Чтобы изменить выравнивание, выделите ячейки и на вкладке Главная выберите в группе Выравнивание необходимые параметры.
Примечание: окно Формат ячейки открывается при нажатии кнопки в нижнем правом углу группы Выравнивание на вкладке Главная.
œ Задание 1:
1. введите в ячейки A1-A10 любые числа
2. введите в ячейки B1-B10 числа от 1 до 10, используя автозаполнени е:
- укажите первую ячейку заполняемого диапазона и введите начальное значение
- выделите ячейку, содержащую начальное значение
- при нажатой правой клавиши мыши перетащите маркер заполнения (небольшой черный квадрат в углу выделенного диапазона ) через заполняемые ячейки, используя команду Заполнить в контекстном меню
3. в ячейке А11 введите текущую дату;
4. в ячейку B11 введите текущее время.
Ввод формул
Формула является основным средством для анализа данных. С помощью формул можно складывать, умножать и сравнивать данные, а также объединять значения. Формулы создаются с помощью констант, операторов и ссылок на ячейки. Формулы могут ссылаться на ячейки текущего листа, листов той же книги или других книг.
Пример: =(B4+25)/СУММ(D5:F5)
В данном примере складывается значение ячейки B4 с числом 25, полученный результат делится на сумму ячеек D5, E5 и F5, а B4,D5:F5 называются ссылками. Ссылки используются чтобы сослаться на данные, хранящиеся в указанных ячейках. Например, ссылка на ячейку D5 возвращает значение, хранящееся в этой ячейке и использует его в вычислении.
Последовательность действий при вводе формул:
· укажите ячейку, в которую необходимо ввести формулу;
· введите = (знак равенства)
(если нажать кнопку Вставить функцию, автоматически вставляется знак равенства);
· введите формулу;
· нажмите клавишу Enter.
œЗадание 2:
· в ячейке С1 найдите разность ячеек А1 и В1;
· в ячейке С2 найдите сумму ячеек А2 и В2;
· в ячейке С3 вычислите значение по формуле (А3/В3+А3^2).
Использование функций
Функции задаются с помощью формул, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке, называемом синтаксисом. Например, функция СУММ складывает значения группы ячеек.
Список аргументов может состоять из чисел, текста, логических величин или ссылок. Необходимо следить за соответствием типов аргументов. Кроме того, аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции.
Написание функции начинается со знака равенства(=), за которым следует имя функции, затем вводится открывающая скобка, указываются аргументы, отделяющиеся запятыми, а затем — закрывающая скобка. В процессе создания формулы, содержащей функцию, можно использовать панель формул.
Последовательность действий при создании формул с помощью функций:
· укажите ячейку, в которую нужно ввести формулу;
· чтобы начать формулу с функции, нажмите кнопку Вставить функцию в строке формул (знак равенства (=) будет вставлен автоматически);
· выберите необходимую функцию. Можно ввести поисковый запрос в поле Поиск функции или выполнить поиск по категориям в поле Категория;
· введите аргументы.
œЗадание 3:
· в ячейки D1,D2,D3,D4,D5 введите любые числа и просуммируйте их в ячейке D6, используя функцию СУММ;
· в ячейку A12 введите число 15, в ячейку B12 введите число 7, в ячейке С12 запишите формулу = ЦЕЛОЕ(А12/В12), в ячейке С13 запишите формулу = ОСТАТ(А12;В12). Проанализируйте результаты.
Логическая функция ЕСЛИ
Функция ЕСЛИ относится к категории логических функций. Функция ЕСЛИ проверяет указанное условие на истинность. Если условие истинно, возвращается один результат, если оно ложно — другой.
Для записи условий используются логические выражения, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения. Ниже представлен список операторов сравнения Excel
= Равно
> Больше
< Меньше
>= Больше или равно
<= Меньше или равно
<> Не равно
Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
Функция ЕСЛИ имеет следующий синтаксис:
=ЕСЛИ(логическое_выражение;истина;ложь)
Например,
=ЕСЛИ(А1>3;10;20)
Результатом работы данной формулы будет значение 10, если значение в ячейке А1 больше 3, а в противном случае – 20.
В функции ЕСЛИ можно использовать текстовые аргументы.
Например:
=ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал")
Аргумент «логическое_выражение» функции ЕСЛИ может содержать текстовое значение.
Например: =ЕСЛИ(А1="Динамо";10;290)
Результатом работы данной формулы будет 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.
В качестве аргументов функции ЕСЛИ можно использовать другие функции. Часто используют вложенные функции ЕСЛИ. Например, в следующей формуле используются две функции ЕСЛИ:
=ЕСЛИ(А1>=80;ЕСЛИ(А1>=100;1;0);-1)
Если значение в ячейке А1 меньше 80, то результатом работы данной формулы будет -1, если значение в ячейке А1 попадет в интервал от 80 до 100, результат 0, если больше 100, то 1. Всего допускается до 7 уровней вложения функций ЕСЛИ.
œЗадание 4:
в ячейку А15 введите положительное число, в ячейку В15 введите отрицательное число, в ячейке С15 составьте формулу, результатом которой будет слово «ПЛЮС», если сумма значений в ячейках А15 и В15 положительная или слово «МИНУС», если сумма значений в ячейках А15 и В15 отрицательная. При составлении формулы в ячейке С15 используйте функцию ЕСЛИ.
Операции с данными
Копирование
1 способ
· выделите ячейки, содержащие данные, которые необходимо скопировать,
· перетащите маркер заполнения [1]через заполняемые ячейки.
Все существовавшие ранее значения или формулы в заполняемых ячейках будут замещены.
2 способ
· выделите данные, которые необходимо скопировать,
· на вкладке Главная нажмите кнопку Копировать,
· выберите позицию, в которой данные должны находиться,
· на вкладке Главная нажмите кнопку Вставить.
3 способ
· выделите данные, которые необходимо скопировать,
· в контекстном меню выберите команду Копировать,
· выберите позицию, в которой данные должны находиться,
· в контекстном меню выберите команду Вставить.
Перемещение
1 способ
· выделите ячейки, содержащие данные, которые необходимо переместить,
· установите указатель мыши к краю выделенных ячеек, он примет вид белой толстой стрелки и перетащите ячейку
2 способ
· выделите данные, которые необходимо переместить,
· на вкладке Главная нажмите кнопку Вырезать,
· выберите позицию, в которой данные должны находиться,
· на вкладке Главная нажмите кнопку Вставить.
3 способ
· выделите данные, которые необходимо переместить,
· в контекстном меню выберите команду Вырезать,
· выберите позицию, в которой данные должны находиться,
· в контекстном меню выберите команду Вставить.
œЗадание 5:
· скопируйте заполненную ячейку вниз;
· переместите заполненную ячейку вправо;
· заполните 2 колонки числами, в третьей колонке найдите произведение первых чисел и скопируйте формулу до конца колонки.
Различия между относительными и абсолютными ссылками ячеек
При создании формулы выражение может содержать ссылки ячеек, задаваемые обычным образом. Такие ссылки называются относительными.
При копировании формулы, содержащей относительные ссылки, и вставке ее в другое место, ссылки будут указывать на другие ячейки.
Например, в ячейках А12-А15 находятся числа, в ячейке В12 формула: =А12*2; при копировании формулы в ячейки В13-В15 ссылки в формуле изменятся на А13-А15.
Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, используют абсолютные ссылки. Чтобы создать абсолютную ссылку поставьте знак доллара ($) перед той частью, которая не должна изменяться.
Например, в ячейках А12-А15 находятся числа, в ячейке В12 формула: =$А$12*2; при копировании формулы в ячейки В13-В15 ссылки в формуле не меняются, т. е. в ячейках В13-В15 остается формула =$А$12*2.
œЗадание 6:
· в ячейки E1-E5 введите числа;
· в ячейку G1 введите формулу=E1*100 (в данной формуле используются относительные ссылки);
· в ячейки G2-G5 скопируйте формулу из ячейки G1;
· в ячейку H1введите формулу =$E$1*100 (в данной формуле ссылка на ячейку E1 является абсолютной);
· в ячейки H2-H5 скопируйте формулу из ячейки H1.
Оформление таблиц
Параметры вставки
В Excel можно вставлять ячейки, строки и столбцы.
Пустые ячейки можно вставлять сверху или слева от активной ячейки на листе. При этом остальные ячейки будут смещены вниз в этом же столбце или вправо в этой же строке. Таким же образом можно вставлять строки над выделенной строкой или столбцы слева от выделенного столбца. Ячейки, строки и столбцы можно также удалять.
Чтобы вставить одну строку, выделите всю строку или ячейку в строке, над которой нужно вставить новую строку. Чтобы вставить несколько строк, выделите строки, над которыми требуется вставить новые строки. Выделите столько же строк, сколько нужно вставить. Например, чтобы вставить три новых строки, необходимо выделить три строки. Чтобы вставить несмежные строки, удерживайте нажатой клавишу CTRL при выделении несмежных строк.
Параметры вставки находятся в группе Ячейки на вкладке Главная.
œЗадание 7:
вставьте первую строку и введите заголовок для выполненных заданий