Методичка еxцел





Изучаем
Excel
















































Издание 2-ое, 19.09.2007, автор учитель информатики МОУ СОШ №9 г. Нерчинска Курилов И.А.
Электронные таблицы. Назначение и основные функции.
Одной из самых продуктивных идей в области компьютерных информационных технологий стала идея электронной таблицы. Многие фирмы разработчики программного обеспечения для ПК создали свои версии табличных процессоров прикладных программ, предназначенных для работы с электронными таблицами. Из них наибольшую известность приобрели Lotus 123 фирмы Lotus Development, Supercalc фирмы Computer Associates, Multiplan и Excel фирмы Microsoft. Отечественные школьные компьютеры также оснащены упрощенными (учебными) версиями табличных процессоров.
Табличные процессоры (ТП) удобный инструмент для экономистов, бухгалтеров, инженеров, научных работников всех тех, кому приходится работать с большими массивами числовой информации. Эти программы позволяют создавать таблицы, которые (в отличие от реляционных баз данных) являются динамическими, т. е. содержат так называемые «вычисляемые поля», значения которых автоматически пересчитываются по заданным формулам при изменении значений исходных данных, содержащихся в других полях.
При работе с табличными процессорами создаются документы электронные таблицы (ЭТ). Электронная таблица (документ) создается в памяти компьютера. В дальнейшем ее можно просматривать, изменять, записывать на магнитный диск для хранения, печатать на принтере.
Среда ТП.
Рабочим полем табличного процессора является экран дисплея, на котором электронная таблица представляется в виде матрицы. ЭТ, подобно шахматной доске, разделена на клетки, которые принято называть ячейками таблицы. Строки и столбцы таблицы имеют обозначения. Чаще всего строки имеют числовую нумерацию, а столбцы буквенные (буквы латинского алфавита) обозначения. Как и на шахматной доске, каждая клетка имеет свое имя (адрес), состоящее из имени столбца и номера строки, например: А1, С13, F24 и т. п.
Но если на шахматной доске всего 8х8=64 клетки, то в электронной таблице ячеек значительно больше. Например, у табличного процессора Excel таблица максимального размера содержит 256 столбцов и 16 384 строки. Поскольку в латинском алфавите всего 26 букв, то начиная с 27-го столбца используются двухбуквенные обозначения, также в алфавитном порядке:
АА, АВ. AC,..., AZ, ВА, ВВ. ВС,..., BZ, СА...
Последний, 256-й столбец имеет имя IV (не путайте с римской цифрой). Значит, существуют ячейки с такими, например, именами: DL67, HZ10234 и т. п.
На экране дисплея видна не вся электронная таблица (документ), а только ее часть. Документ в полном объеме хранится в оперативной памяти, а экран можно считать окном, через которое пользователь имеет возможность просматривать его.
В ТП Excel реализована возможность работы одновременно с несколькими таблицами, расположенными на разных листах. Пользователь может «перелистывать» эти листы, как в книге.
При заполнении таблицы и при ее просмотре большую роль играет табличный курсор прямоугольник, выделенный цветом. Табличный курсор всегда занимает клетку таблицы, которая называется текущей клеткой. Перемещая курсор по таблице, мы тем самым перемещаем «окно» по документу, хранящемуся в оперативной памяти, и делаем видимым ту или иную его часть.
Важным элементом среды табличного процессора является меню команд. С его помощью управляют работой электронной таблицы. Меню команд может быть словесным (в Supercalc) или пиктографическим (в Excel).
Панель диалога обеспечивает взаимодействие пользователя с табличным процессором и может содержать следующие строки: строку состояния, строку запроса, строку ввода и строку помощи, расположенные в нижней части экрана.
Строка состояния предназначена для выдачи информации о текущей клетке.
Строка запроса содержит возможные варианты ответа на запросы табличного процессора. Если пользователь не ведет диалог с табличным процессором, то строка запроса содержит некоторую дополнительную информацию: ширину текущего столбца, объем свободной памяти, размер таблицы с данными (активной таблицы).
Строка ввода предназначена для индикации данных, которые пользователь набирает перед вводом в клетки таблицы.
Строка помощи может содержать расшифровку текущей команды, индикатор состояния табличного процессора (не путать со строкой состояния). Можно выделить несколько основных таких состояний :
«ожидание» ожидание набора данных или команд;
«меню» ожидание выбора команды из меню команд;
«ввод» ввод данных;
«редактирование» редактирование данных в строке ввода.
Данные в электронной таблице.
Все данные таблицы размещаются в ячейках. Содержимым ячейки может быть текст, числовое значение или формула. Табличный процессор должен «знать», какого типа данное хранится в конкретной ячейке таблицы, для того чтобы правильно интерпретировать ее содержимое. Текст и числа рассматриваются как константы. Изменить их можно только путем редактирования соответствующих ячеек. Формулы же автоматически пересчитывают свои значения, как только хотя бы один их операнд был изменен. Вот примеры записи формул:
2.5*А1+В2*СЗ;
(ВЗ-С1)/(ВЗ+С1);
F7/2+G7/3;
(А51)"2
Правила записи формул подобны правилам записи арифметических выражений в языках программирования. Только здесь в качестве идентификаторов переменных выступают имена ячеек таблицы. Кроме арифметических операций формулы могут содержать стандартные функции. У каждого ТП свой набор стандартных функций.
Режимы работы и система команд ТП.
Можно выделить следующие режимы работы табличного процессора:
формирование электронной таблицы;
управление вычислениями;
режим отображения формул;
графический режим;
работа электронной таблицы как базы данных.
Система команд тесно связана с режимами работы электронной таблицы. Как правило, команды реализуются через меню команд или через функциональные клавиши.
Рассмотрим подробнее режимы работы электронных таблиц и команды, связанные с ними.
1. Режим формирования электронных таблиц предполагает заполнение и редактирование документа. Базовые команды формирования таблиц можно разбить на две группы:
команды, изменяющие содержимое клеток (очистить, редактировать, копировать);
команды, изменяющие структуру таблицы (удалить, вставить, переместить).
2. Режим управления вычислениями. Все вычисления начинаются с клетки, расположенной на пересечении первой строки и первого столбца электронной таблицы. Вычисления проводятся в естественном порядке, т. е. если в очередной клетке находится формула, включающая адрес еще не вычисленной клетки, то вычисления по этой формуле откладываются до тех пор, пока значение в клетке, от которой зависит формула, не будет определено.
При каждом вводе нового данного в клетку документ пересчитывается заново реализуется автоматический пересчет. В некоторых табличных процессорах существует возможность установки ручного пересчета, т. е. таблица пересчитывается заново только при подаче специальной команды.
3. Режим отображения формул задает индикацию содержимого клеток на экране. Обычно этот режим выключен и на экране отображаются значения, вычисленные на основании содержимого клеток.
4. Графический режим дает возможность отображать числовую информацию в графическом виде, чаще всего в виде диаграмм. Команды графического режима можно разбить на две группы:
команды описания диаграмм (задают данные, которые будут выведены в графическом виде, задают тип диаграмм и т. д.);
команды вывода диаграмм.
5. Работа в режиме баз данных реализована в профессиональных ТП. Возможность искать и выбирать данные из таблицы позволяет использовать электронную таблицу в качестве несложной базы данных. При работе с базами данных приходится иметь дело с такими понятиями, как файл, запись, поле данных. В электронных таблицах файлом является сама таблица, записями строки таблицы, полями клетки таблицы.
Адресация.
Существует определенная аналогия между структурой электронной таблицы и структурой оперативной памяти ЭВМ. В обоих случаях используется принцип адресации для хранения и поиска информации. Разница состоит в том, что в ОЗУ наименьшей адресуемой единицей является байт, а в таблице клетка (ячейка). Клетку таблицы можно рассматривать как переменную (т. е. А1, С5, G10 имена переменных).
Символические имена переменных являются в то же время их адресами в таблице. Существуют различные способы определения местоположения клетки: абсолютная адресация и относительная адресация. Абсолютная адресация устанавливает адрес клетки независимо от того из какой клетки таблицы ссылаются на данную клетку. Относительная адресация устанавливает адрес клетки в таблице в зависимости от местоположения формулы, в которой этот адрес используется в качестве операнда. По умолчанию в электронных таблицах действует относительная адресация.
Разница в способах адресации становится видна при переносе формул путем копирования или при других преобразованиях таблицы, приводящих к изменению местоположения формул. Относительные адреса в формулах модифицируются в соответствии с их новым местоположением, абсолютные же адреса остаются неизменными. Для многих табличных процессоров в качестве признака «замораживания» адреса, т. е. превращения его из относительного в абсолютный, используется значок «$». Например, адрес ячейки G7 является относительным, а адрес, записанный в виде $G$7, является абсолютным («заморожен» как по строке, так и по столбцу).








Введение: Табличные процессоры: структура документа (ячейка, лист, книга, рабочая область), атрибуты ячейки, типы содержимого ячейки и их способы задания.
Табличные процессоры (ТП) удобный инструмент для экономистов, бухгалтеров, инженеров, научных работников всех тех, кому приходится работать с большими массивами числовой информации. Эти программы позволяют создавать таблицы, которые являются динамическими, т. е. содержат так называемые «вычисляемые поля», значения которых автоматически пересчитываются по заданным формулам при изменении значений исходных данных, содержащихся в других полях.
При работе с табличными процессорами создаются документы электронные таблицы (ЭТ). Электронная таблица (документ) создается в памяти компьютера. В дальнейшем ее можно просматривать, изменять, записывать на магнитный диск для хранения, печатать на принтере.
Структура документа. Основные объекты структуры документа – это ячейка, лист, книга, рабочая область.
Ячейка – место пересечения столбца и строки электронной таблицы, адрес ячейки формируется из заголовка столбца и заголовка строки (см. рис. 1)
Лист – электронные таблицы, с которыми работает пользователь в приложении (см. рис. 1)
Книга – документ из электронных таблиц, включающий несколько листов (см. рис. 1)
Рабочая область – область размещения рабочей электронной таблицы (см. рис. 1)


























Рис.1



Атрибуты ячейки. Типы содержимого ячейки и их способы задания.
Ячейки таблиц имеют атрибуты, которые настраиваются в окне формат ячеек. Пункт меню ФОРМАТ ( ЯЧЕЙКИ или через контекстное меню ячейки (пункт ФОРМАТ ЯЧЕЕК) (см. рис. 2).
13EMBED PBrush1415
Рис. 2
на вкладке число задаются различные форматы ячеек и их типы (хотя основные, такие как денежный, процентный, числовой задаются с помощью панели форматирования
на вкладке выравнивание осуществляем выравнивание по горизонтали, вертикали, меняем ориентацию, а также устанавливаем флажки: перенос по словам, автоподбор ширины и объединение ячеек
на вкладке шрифт осуществляем настройку шрифта (подобно в текстовом редакторе Microsoft Word)
на вкладке границы настраиваем границы ячейки
на вкладке вид настраиваем цвета ячейки или даже узор
на вкладке защита делаем защиту ячейки, а также можно установить флажок скрыть формулы (защита ячеек работает после защиты листа).














Практические занятия: Табличный процессор Microsoft Excel.

Среда ТП Microsoft Excel. Особенности панелей Excel. Режимы работы ТП. Заполнение таблицы. Расчёт суммы. Автосумма.
Рассмотрите интерфейс ТП согласно теории
Рассмотрите панели ТП и найдите общее и отличительное от Microsoft Word!!!
( запишите в тетради).
В режиме формирования электронной таблицы: создайте таблицу (см. рис.). Ширину столбцов подобрать вручную или ФОРМАТ ( столбец ( автоподбор. (Также можно через контекстное меню ячейки).

A
B
C
D

1

Курс доллара
30


2





3

Наименование товара
Цена в $
Цена в руб

4
1
Системный блок
700


5
2
Монитор
230


6
3
Клавиатура
10


7
4
Мышь
5


8
5
Коврик
2


9
6
Принтер
400


10
7
Сканер
100


11


?


В режиме управления вычислениями определите общую цену компьютера в ячейке C11 по формуле «=СУММ(C4:C10)». Попробуйте действие кнопки – автосумма на стандартной панели.
Режим отображения формул можно наблюдать при записи формул, которые вы записываете. Графический режим при построении диаграмм рассмотрите в пункте №5. Работа в режиме баз данных рассматривается далее
вместе с темой «СУБД».

Работа над таблицей. Простые формулы. Относительная и абсолютная адресация. Сортировка. Формат ячеек. Автофильтр. Автоматизация ввода.
Заполнить поле D для всех записей путём ввода формулы расчёта «=$C$1*C4» в ячейке D4 (причём получить формулу можно как простым вводом, так и щелчком мыши на нужных ячейках). Затем её копирования в другие ячейки с помощью буфера обмена (копирование и вставку из буфера лучше производить комбинациями клавиш «Ctrl + Insert» и «Shift + Insert» соответственно) или протягиванием левой кнопкой (копирование и вставка происходит автоматически).
Как можно получить двумя способами итоговую сумму в рублях в ячейке в D11? (Обе формулы запишите в тетрадь.)
Для чего 1-ая переменная в формуле имеет абсолютную адресацию, а 2-ая относительную? (Ответ запишите в тетрадь.)
Рассмотрите, как происходит сортировка данных (панель стандартная), как меняется формат ячеек (панель форматирования).
Рассмотрите работу автофильтра (панель стандартная или пункт меню ДАННЫЕ ( фильтр).
Рассмотрите автоматизацию ввода, поменяв, например, курс доллара.
Задание 1: Составить таблицу подсчета цены партии товара:

товар
Цена за 1 кг(шт.)
Количество товара
цена
*
*

1







2







3







4







5







всего







*какие поля можно добавить по вашему усмотрению (какие это поля: ввода или расчета).

Создание таблицы. Расчёт с помощью статистических. Построение диаграмм для таблиц.
Создать таблицу расчёта среднего бала для нескольких учеников по нескольким предметам (см. рис.), используйте функцию СРЗНАЧ (функции открываются пиктограммой - «13EMBED PBrush1415» стандартной панели (в Word XP в строке формул).


фамилия
1 предмет
2 предмет

N предмет
Средний балл

1







2















n















Найдите наибольшее и наименьшее значение полей с помощью статистических функций МИН и МАКС, справа от данной таблицы (эти функции можно найти в открывающемся списке автосуммы).
Постройте диаграмму для этой таблицы. (Используйте мастер диаграмм на стандартной панели – запуск кнопкой «13EMBED PBrush1415»)
Задание 2: Постройте таблицу расчета среднего балла по предмету информатика для своей группы (сортировку по убыванию)

Самостоятельное практическое занятие.
Задание 3: Построить диаграмму выборов трех кандидатов на должность председателя совета школы в абсолютном и процентном отношении (поля ввода: фамилия и абсолютное число голосов и поле расчета процент от общего числа голосов).
Задание* 4: Создайте таблицу расчёта зарплаты сотрудников (5-10 человек) на 1-ом листе (таблица состоит из столбцов порядкового номера, фамилии, оклада, районного коэффициента, стажевого коэффициента, премиальных, а также строки итоговой суммы). На 2-ом листе находятся 2 таблицы: ставок работников и стажевых коэффициентов.
Пояснения. Зарплата работников рассчитывается по формуле:
Зарплата = оклад + (оклад ( район. коэфф.) + (оклад ( стаж. коэфф.) + премия, где коэфф. – множители < 1 (пример: 0,1;0,3; и т. д.).

Математические (тригонометрические) функции. Построение графиков по таблицам. Редактирование графиков.
Вместе с учителем!
Создать таблицу расчёта COS угла (функции Y=COSX) на промежутке от 0 до 6.28 радиан с шагом 0.5 (копирование функции произведите сразу во все ячейки соответствующих строк). Добавьте ещё 2 строки: после ввода значений X нажмите Enter – пронаблюдайте автоматизацию ввода (и даже расчета).
Постройте график для таблицы. (Используйте мастер диаграмм на стандартной панели – запуск кнопкой «13EMBED PBrush1415», выделяя столбец значений Y.)
Задание 5: Построить график функции Y=x2-2 (или другую функцию по указанию учителя)

Пример: сложная функция y=x3-sin(x)-0,5 (=A6*A6*A6-SIN(A6)-0,5)
x
y










-1
-0,65852902







-0,5
-0,14557446







0
-0,5







0,5
-0,85442554







1,118535
-3,8731E-05







1,5
1,877505013







2
6,590702573







 
 
















































































Пример: точка пересечения двух функций (или нахождение корней уравнения графическим методом)
x
x^2
20-x















0
0
20












1
1
19












2
4
18












3
9
17












4
16
16












5
25
15












6
36
14












7
49
13












8
64
12












9
81
11












10
100
10










































































































































































«Графики функций и надстройки в электронных таблицах» (см. в учебнике Н. Угриновича 10.5 и 10.6). Самостоятельная работа.
Таблица значения функции y=x*x*x-sinx-0,5




x
-1
-0,5
0
0,5
1,1185351
1,5
2

y
-0,65853
-0,14557
-0,5
-0,85443
-3,87E-05
1,877505
6,590703


























































Вложенные условия. Функции «Дата и время».
Рассмотрим задачу: Определение состояния годности продуктов на сегодня
Определите оставшиеся условия по заданным формулам!

=ЕСЛИ(ДНЕЙ360(C2;$F$1)/30>D2;"Не годен";ЕСЛИ(D2*30-ДНЕЙ360(C2;$F$1)<30;"Срочная реализация";D2*30-ДНЕЙ360(C2;$F$1)))
=СЕГОДНЯ()

наименование продукта
дата изготовления
срок годности (мес.)
состояние на
02.04.2005

1
мука
03.09.2004
12
151


2
масло подсолнечное
12.10.2004
6
Срочная реализация


3
масло сливочное
20.12.2004
4
Срочная реализация


4
молоко концентрированное
30.09.2004
12
178


5
сыр колбасный
21.10.2004
5
Не годен


6
колбаса в вакуумной упаковке
16.08.2004
18
314


7
сок натуральный
12.04.2004
24
370


8
напиток газированный
13.03.2005
3
71


9
йогурт
30.12.2004
7
118


10
конфеты подарочные
09.03.2004
14
37


Задание 6: Определить на сегодня износ (амортизацию) 5 автомобилей, по известной дате выпуска и начальной стоимости. Вывести на экран сообщение стоимость 50% от начальной на автомобили более 7 лет, 25% - более 10 лет, СПИСАН – более 15 лет.





Расчёт с помощью логических функций. Сложные функции. Виды функций. Создание таблицы.

Рассмотрите логические функции «И», «ИЛИ», «НЕ»
а
в
результат
таблица истинности логического элемента И





0
0
ЛОЖЬ






0
1
ЛОЖЬ






1
0
ЛОЖЬ






1
1
ИСТИНА















а
в
результат
таблица истинности логического элемента ИЛИ

0
0
ЛОЖЬ






0
1
ИСТИНА






1
0
ИСТИНА






1
1
ИСТИНА















а
результат

таблица истинности логического элемента НЕ

0
ИСТИНА







1
ЛОЖЬ







Задание 7: Постройте таблицы истинности для логических операций &, ( и НЕ (по указанию учителя).
Рассчитайте функцию Y=2*X+2*SIN(2*X+1) на интервале от -5 до 2 и функцию Y=2*X+2*COS(2*X+1) на интервале от 2 до 5 в одной таблице, таким образом, чтобы при добавлении любого числа в таблицу расчёт происходил согласно вышеуказанному условию.
Пояснения. В данном случае нужно использовать логическую функцию ЕСЛИ: =ЕСЛИ(лог. выражение, значение_если_истина, значение_если_ложь).
Пример: =ЕСЛИ(A14>0;COS(A14);0)
На рисунке изображено пример диалогового окна ввода функций:
13EMBED PBrush1415
Рассмотрите, какие категории функций существуют в программе. Открыть с помощью значка вставка функции (см. пункт 4).
Задание 8: Расчет функции не определенной на всей числовой прямой (например, y=(x2-1)/x или 13EMBED Equation.31415 на промежутке от 0 до10)

Самостоятельное практическое занятие.
Задание 9: Рассчитайте функцию 13EMBED Equation.31415 на промежутке от –2 до2 (шаг расчета 0,5).
Задание 10: Даны координаты вершин прямоугольника (x1,y1), (x1,y2), (x2,y2), (x2,y1) расположенного в первом квадранте, со сторонами параллельными осям координат (0< x1
A
B
C
D
E
F
G

1
X1
X2
Y1
Y2
X
Y
Точка внутри прямоугольника

2








*Создание таблицы. Работа с несколькими листами. (Абсолютные и относительные адреса.) Другие функция (ссылки ВПР и ГПР).
ВПР - ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Функция ВПР используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных. Буква «В» в имени функции ВПР означает «вертикальный». (Функция ВПР относится к категории «ссылок и массивов»).
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
Искомое_значение   это значение, которое должно быть найдено в первом столбце [ Cкачайте файл, чтобы посмотреть ссылку ]. Искомое_значение может быть значением, ссылкой или текстовой строкой.
Таблица  таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например БазаДанных или Список. Номер_столбца   это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «номер_столбца» больше, чем количество столбцов в аргументе «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!.
Интервальный_просмотр   это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
Пример:
ВПР(C3;$AE$3:$AG$20;3) - данный фрагмент формулы ищет значение С3 в 3-ем столбце таблицы $AE$3:$AG$20
=ВПР(D18;Лист1!$A$5:$B$22;2)*Лист1!$A$2
D18 – относительная ссылка на данную ячейку;
Лист1!$A$5 – абсолютная ссылка.

Задание 11: Расчет зарплаты сотрудников предприятия (см. задание 4). Усовершенствовать данную таблицу: добавить другие процентные надбавки, налоги, полем ввода сделать разряд. Оклад определять с помощью функции ВПР из таблицы разрядных ставок, находящейся на другом листе.**
Контрольное занятие.








13PAGE 14215



13EMBED PBrush1415

Листы

Рабочая область

Ячейки




Приложенные файлы


Добавить комментарий