Электронные таблицы Буга Л.В

1.

Лекция № 13
Электронные таблицы

2.

ЭЛЕКТРОННЫЕ ТАБЛИЦЫ. НАЗНАЧЕНИЕ И
ОСНОВНЫЕ ФУНКЦИИ.
• Для автоматизации табличных расчетов используется
специальный вид прикладного программного обеспечения,
называемый табличными процессорами или электронными
таблицами.
Электронная таблица – это программа обработки
числовых данных, хранящая и обрабатывающая данные в
прямоугольных таблицах
Программа MS Excel – одна из самых популярных программ
для работы с электронными таблицами на сегодняшний
день, работающая под управлением системы Windows.

3.

• Табличный процессор EXCEL разработан в
1987 году фирмой Microsoft
Табличный процессор – это программа,
обеспечивающая создание и
автоматическую обработку электронной
таблицы.
Создателем электронных
таблиц как отдельного
БОБ ФРЭНКСТОН
класса ПО является Дэн
Бриклин, совместно с Бобом
Фрэнкстоном (1979 г.)
ДАНИЭЛЬ БРИКЛИН

4.

Табличные процессоры обеспечивают:
- Ввод, хранение и корректировку данных
- Автоматическое проведение вычислений по
заданным формулам
- Наглядность документов
- Построение различного рода диаграмм и графиков
на основе табличных данных
- Оформление и печать таблиц
- Работу с электронными таблицами как с базами
данных

5.

• Главное достоинство электронной таблицы – это
возможность мгновенного пересчёта всех
данных, связанных формульными зависимостями
при изменении значения любого операнда.
Файл электронной таблицы называется
КНИГОЙ.
КНИГА состоит из ЛИСТОВ, (листов по
умолчанию 3)
Каждый лист представляет собой таблицу.

6.

• Каждый рабочий лист имеет свой ярлык, на
котором написано название листа
• Ярлык листа расположен слева от
горизонтальной полосы прокрутки.

7.

• Для выбора нужного листа, надо щёлкнуть мышью
по ярлыку с его именем или воспользоваться одной
из 4-х стрелок, находящихся слева от 1-ого
листа:
- в начало книги
- на предыдущий лист
- на следующий лист
- в конец книги.

8.

С помощью ярлычков можно переключаться к другим
рабочим листам, входящим в ту же самую рабочую
книгу.
Листы можно переименовывать, добавлять, удалять,
перемещать, копировать, изменять цвет ярлычка (для
этого надо дважды щелкнуть на его ярлычке).

9.

10.

• Электронная таблица, как и любая
прямоугольная таблица, состоит из строк и
столбцов.
• Количество строк и столбцов определяется
возможностями табличного процессора и
компьютера.

11.

• На каждом листе 256 столбцов (из которых в
новой таблице на экране видны, как правило,
только первые 10 или 11 (от А до J или К)) и 65
536 строк (из которых обычно видны только
первые 15-20).

12.

• Если умножить 256 на 65 536, то
получится, что на каждом листе
рабочей таблицы к вашим услугам
16 777 216 пустых ячеек. ( почти 17 млн.)

13.

• Каждая строка и столбец имеет своё имя.
Имя строки – это её номер в таблице.
Строки пронумерованы цифрами: 1,2,3,...
Имя столбца – это прописные буквы латинского
алфавита: A,B,C,…Z, AA, AB,…,AZ

14.

• Пересечение строки и столбца образует
ячейку, имеющую своё имя, называемое
адресом ячейки.
Адрес ячейки – это местоположение ячейки в
таблице.
• Он определяется именем (буквенным) столбца и
именем (цифровым) строки, на пересечении
которых находится ячейка.
• Например: A1, Z23, BT5 и т.д.

15.

16.

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

17.

Можно обрабатывать одновременно несколько
ячеек – диапазон ячеек.
Для обозначения диапазона ячеек используется
двоеточие.
Например: В3:D7

18.

Ячейка может содержать следующие типы данных:
числовые, текстовые, формулы
Формула всегда начинается с символа “=” (знака
равенства) и содержит числа, ссылки на адреса ячеек,
встроенные
функции,
соединенные
знаками
математических операций.
Если ячейка содержит формулу, то в рабочем листе
отображается результат вычисления этой формулы.
Если сделать ячейку текущей, то формула
отображается в строке формул.

19.

• В Excel различают относительные и
абсолютные адреса ячеек или ссылки.

20.

Типы ссылок
Относительная
Абсолютная
Смешанная
При изменении
позиции ячейки
с формулой
изменяется
и ссылка
При изменении
позиции ячейки
с формулой
ссылка не
изменяется
При изменении
позиции ячейки
с формулой
изменяется
относительная
часть адреса
А1; В4; С2; Р12
$А$1; $В$4;
$С$2; $Р$12
$А1; В$4;
С$2; $Р12

21.

Относительный адрес ячейки – это
изменяющийся при копировании или при
перемещении адрес ячейки, содержащей
исходную формулу.

22.

Абсолютный адрес ячейки – это не изменяющийся
при копировании или при перемещении адрес
ячейки, содержащей исходную формулу.
Для указания абсолютной адресации, в адрес
ячейки вводится символ - $ (доллар).
Например: $B$5 - это полная абсолютная ссылка или
$C9 или D $12 – это частичная абсолютная ссылка

23.

Смешанные ссылки
Смешанная ссылка содержит либо абсолютно адресуемый
столбец и относительно адресуемую строку ($A1), либо
относительно адресуемый столбец и абсолютно адресуемую
строку (A$1).
При копировании или заполнении формулы вдоль строк и
вдоль
столбцов
относительная
часть
ссылки
автоматически корректируется, а абсолютная - не
корректируется.

24.

25.

26.

Формат данных – это способ представления данных,
который отражает их внешний вид в соответствии
с их назначением

27.

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

28.

ДАННЫЕ ЭЛЕКТРОННОЙ ТАБЛИЦЫ
Общий формат
Общий формат используется по умолчанию
и позволяет вводить любые данные (числа,
текст, даты, время и т.д.), которые
распознаются и форматируются
автоматически.
9

29.

Текстовый тип данных
Текстовые данные представляют собой некоторый
набор символов. Если первый из них является буквой,
кавычкой, апострофом или пробелом, либо цифры
чередуются с буквами, то такая запись
воспринимается как текст.
Действия над текстовыми данными производятся
аналогично действиям над объектами в текстовом
процессоре.
Пример текстовых данных:
Расписание занятий
‘’236
001 счет

30.

Числовой тип данных
Числовые данные представляют собой
последовательность цифр, которые могут быть
разделены десятичной запятой и начинаться с цифры,
знака числа (+ или -), или десятичной запятой.
Над числовыми данными в электронной таблице
могут производиться различные математические
операции.
Пример числовых данных: 232,5
-13,7
+100
,345
10

31.

Тип данных – даты
Этот тип данных используется пи выполнении
таких функций, как добавление к дате числа,
получение разности двух дат, при пересчете даты.
Например вперед или назад. Пересчет чисел в даты
производится автоматически в зависимости от
заданного формата.
Табличный процессор позволяет представлять
вводимые числа как даты несколькими способами.
Пример. Представление дат в разных форматах:
4 июня 1989; 06.98; Июнь 2001; 4 июня; 04.06. ; Июнь
11

32.

Процентный формат данных
Процентный формат обеспечивает представление
числовых данных в форме процентов со знаком %.
Например: если установлена точность в один
десятичный знак, то при вводе числа 0.257 на
экране появится 25.7%, а при вводе числа 257 на
экране появится 25700.0%.
12

33.

Денежный формат
Денежный формат обеспечивает такое
представление чисел, при котором каждые три
разряда разделены пробелом, а следом за
последним десятичным знаком указывается
денежная единица размерности – «р» (рубли).

34.

Пользователь может задать определенную
точность представления (с округлением до целого
числа (0 десятичных знаков), или с заданным
количеством десятичных знаков.
Например: число 12345 будет записано в ячейке как
12345 р. ( с округлением до целого числа) и
12345.00р (с точностью до двух десятичных
знаков).

35.

Встроенные функции
Встроенные функции - заранее определённые формулы.
В электронных таблицах реализовано несколько сотен
встроенных
функций,
подразделяющихся
на:
математические, статистические, логические, текстовые,
финансовые и др.
Имя функции
Действие функции
СУММ (SUM)
Суммирование аргументов
МИН (MIN)
Определение наименьшего значения из
списка аргументов
МАКС (MAX)
Определение наибольшего значения из
списка аргументов
СЧЁТ
Подсчитывает количество чисел в
аргументе

36.

Правила ввода функций
1. Выделить ячейку, где будет введена функция
2. Вставка - Функция ( или fx на панели инструментов)
3. Выбрать функцию из списка
4. В окне Число ввести диапазон исходных данных
5. Ок

37.

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

38.

Форматирование ячеек
Выравнивание данных в ячейках
По умолчанию, текст выравнивается по левому краю (по
горизонтали), а числа -- по правому (по горизонтали). По
вертикали данные выровнены по нижнему краю.
Выравнивание по горизонтали можно поменять с помощью
кнопок на панели инструментов "Форматирование"
которую можно вызвать сочетанием кнопок ctrl 1:
Здесь (слева направо) кнопки: по левому краю, по центру и по
правому краю.

39.

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

40.

Объединение ячеек
• Крайне часто возникает необходимость создать
одну большую ячейку, которая находится на
пересечении нескольких столбцов и нескольких
строк:

41.

Это можно сделать двумя способами:
Кнопка "Объединить и поместить в центре" на
панели инструментов "Форматирование" ;
Галочка "объединение ячеек" в окне "Формат
ячеек", закладка "Выравнивание".

42.

• Сначала выделяем ячейки, которые хотим
объединить (можно выделять несвязные
диапазоны), затем выполняем одно из
вышеуказанных действий.
• Если в выделенном диапазоне была (одна) ячейка
с текстом, текст выровняется по центру
объединенной ячейки (только по горизонтали).
• Если в выделенном диапазоне было несколько
ячеек с текстом, то сохранится только
значение, которое было верхним левым и тоже
выровняется.

43.

• При активной объединенной ячейке на панели
инструментов "Форматирование« будет
вдавлена кнопка "Объединить и поместить в
центре".
• Если при этом нажать ее, объединенная
ячейка опять распадется на "родные"
маленькие, а значение перенесется в верхнюю
левую ячейку.
• Адресом объединенной ячейки станет адрес
верхней левой ячейки, остальные ячейки
выделенного диапазона Excel будет
рассматривать как пустые.

44.

Очистка данных в ячейках и удаление
ячеек
• Выделяем "нужные" ячейки
и нажимаем "Delete";
Выделяем "нужные"
ячейки, щелкаем правой
кнопкой и выбираем
"Очистить содержимое";

45.

• Выделяем "нужные" ячейки, заходим в меню "Правка" →
"Очистить" → "Выбираем нужный вариант";
Клавиша ← очищает содержимое активной (одной!) ячейки
и включает режим редактирования.
• Ее удобно использовать при необходимости удалить одно (не
все, как по "Delete") значение из выделенного диапазона;
Если "родную" неотформатированную ячейку протащить с
помощью маркера автозаполнения по отформатированным,
произойдет своего рода удаление;
пункт меню "Правка" → "Удалить…" и пункт
контекстного меню "Удалить…" приведет к открытию
окна "Удаление ячеек".

46.

• В отличие от предыдущих способов
(очистка), при этом способе данные из
ячеек не просто стираются, а со
смещением всех соседних ячеек, строк и
столбцов.
• Меняется строение всей таблицы.

47.

Заливка ячеек цветом
Есть два способа изменить цвет заливки
выделенных ячеек:
• Кнопка "Цвет заливки" на панели инструментов
"Форматирование" ;
• Окно "Формат ячеек", закладка "Вид":

48.

Добавление границ ячеек
Лист Excel по умолчанию представляет из себя таблицу,
но границы таблицы автоматически не выделяются.
Существует три способа добавить границы к
выделенным ячейкам:
1) Кнопка "Границы" на панели инструментов
«Форматирование»

49.

2) Окно "Граница", вызываемое из кнопки
"Границы" --> "Нарисовать границы...":

50.

3) Окно "Формат ячеек", закладка "Граница":

51.

Формат представления данных в ячейках
Формат ячеек по умолчанию ("Общий")
По умолчанию после создания документа все ячейки
находятся в формате "Общий".
Этот формат имеет ряд хитростей:
числа выравниваются по правому краю, а текст -- по левому;
Если, изменяя ширину столбца, сделать ее меньше
определенной, то число в ячейках заменяется на
символы "#####".
Это не ошибка. Это означает, что нужно сделать столбец
пошире;

52.

Если число очень большое ("6000000000000") или
очень маленькое ("0,00000000000001"), оно
автоматически преобразуется в
экспоненциальный (научный) формат ("6E 12" и
"1E-14« соответственно);
При изменении ширины столбца округляются
десятичные дроби.
Например, если написать "3,1415", затем
изменить ширину так, чтобы "5« перестала
помещаться, в ячейке отобразится "3,142".

53.

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

54.

Есть 3 способа изменить формат представления
данных в ячейках:
Автоматически после ввода определенных
данных в ячейку Excel сам сменит формат
ячейки;
С помощью кнопок на панели инструментов
"Форматирование".
С помощью окна "Формат ячеек";

55.

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

56.

Дата.
Если в ячейке написать "1.2.3« или "1/2/3",
Excel заменит это на "01.02.2003" (первое
число второго месяца третьего года).
Если написать "1.2« или "1/2", то Excel
заменит это на "01.фев".
При этом формат ячейке будет
автоматически преобразован в "Дата";

57.

Процентный.
Если в ячейке написать "1%", формат ячейки
автоматически сменится на "Процентный";
Время.
Если в ячейке написать "13:46:44" или
"13:46", формат ячейки автоматически
сменится на "Время";

58.

Изменение формата ячеек с помощью кнопок на
панели инструментов "Форматирование"
На панели инструментов "Форматирование"
находятся 5 кнопок, с помощью которых можно
быстро изменить формат выделенных ячеек.
Описание кнопок (слева направо):
Денежный формат.
Будет использована денежная единица по
умолчанию;

59.

Процентный формат.
Если в ячейке уже будет находится число, то
Excel домножит его на 100 и добавит знак "%".
Формат с разделителями (числовой формат).
В этом формате будут отделяться пробелом
группы разрядов (сотни, сотни тысяч и т.д.) и
будет добавлено 2 знака после запятой;

60.

61.

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

62.

Формула в электронных таблицах – это
совокупность арифметических операций, адресов
клеток и обращений к функциям.
Ввод формулы начинается со знака = и
осуществляется с помощью мыши (курсором
мыши выделяются те ячейки, адреса которых
нужно записать в формуле).
Если в клетку введена формула, то в самой
клетке после нажатии клавиши ввода будет
виден результат, а в строке ввода сама
формула, по которой получен этот результат
Например: =А1*2

63.

Знаки арифметических операций:
+ - сложение
- - вычитание
* - умножение
/ - деление
^ - возведение в степень
( ) – повышение приоритета операции
Дробные числа записываются через - (,)

64.

В программе MS EXCEL для выполнения расчетов
можно использовать широкий спектр
разнообразных функций.
Функция записывается в виде имени сокращенном
до 3 – 4 букв и аргумента в круглых скобках.
Например: СУММ(А1:А10)
СУММ – имя функции
(А1:А10) – аргумент, записанный в виде диапазона
клеток

65.

Для вызова функции можно использовать кнопку
панели инструментов “Мастер функций”,
выполняя два шага:
1) Выбор имени функции;
2) Оформление аргумента функции.

66.

При вводе функции непосредственно в ячейку
не добавляйте пробелы между знаком равенства,
именем функции и аргументами, заключенными
в круглые скобки.
Некоторые функции используют несколько
параметров.
В этом случае разделите все параметры
запятыми (но не пробелами).

67.

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

68.

Excel содержит много функций, но следует
обратить внимание на следующие:
МАКС(список), например: МАКС(A1;A2;A3;...;An) –
возвращает из списка максимальное число.
МИН(список), например: МИН(B1;B2;B3;...;Bn) возвращает минимальное значение списка
аргументов.
СРЗНАЧ (список) – возвращает среднее
арифметическое своих аргументов.
СУММ(число1;число2;...) – возвращает сумму
указанного списка, суммирует аргументы.
EXP(X) – возвращает экспоненту заданного
числа.

69.

LN(X) – возвращает натуральный логарифм
заданного числа.
LOG10(X) - возвращает десятичный логарифм
заданного числа.
LOG(X; a) - возвращает логарифм заданного
числа X, по заданному основанию а.
SIN(X) – вычисляет синус угла Х, измеренного в
радианах.
COS(X) – вычисляет косинус угла Х,
измеренного в радианах.
TAN(X) – вычисляет тангенс угла Х,
измеренного в радианах.

70.

Ошибки при обработке
электронных таблиц.
Перечислим некоторые ошибки при вводе данных
в ячейки таблицы и при проведении расчетов.
Большую часть этих ошибок программа
обнаруживает и сообщает о них либо
информационным окном, либо краткой пометкой,
которая находится в ячейке, содержащей ошибку
(например, #ИМЯ?).

71.

Ошибки при обработке
электронных таблиц.

72.

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

73.

При появлении сообщения об ошибке
непосредственно в ячейке выберите команду
[Справка-Справка по Microsoft Excel] (или
нажмите кнопку панели инструментов
“Стандартная”) и найдите подробное описание
всех возможных причин ошибки.
Некоторые ошибки Excel предлагает исправить
самостоятельно.

74.

75.

Автоматическое заполнение ячеек
Во многих рабочих таблицах, создаваемых в
Excel, требуется ввести ряд последовательных
дат или чисел.
Например, нужно дать столбцам наименование
месяцев (январь-декабрь) или пронумеровать строки
(1-100).
Средство Автозаполнение справляется с этой
задачей легко и быстро.
При этом требуется только ввести начальный
элемент ряда.

76.

Удачи на
практической работе

77.

Построение
диаграмм

78.

1. Пометить строку с подписями для оси Х и
строки с данными

79.

Создание диаграмм
1. Выделите диапазон, содержащий исходные данные.
2. В меню Вставка щелкните Диаграмма.
3. В открывшемся диалоговом окне Мастера диаграмм
перейдите на вкладку
Стандартные.
Мастер диаграмм предоставляет набор рисунков с
типами диаграмм.
В списке Тип выберите Гистограмма, в правой части
окна - вид диаграммы.

80.

81.

4. Щелкните кнопку Далее.
5. На вкладке Диапазон данных открывшегося
диалогового окна Мастер диаграмм: источник
данных диаграммы представлен макет
диаграммы.
Вы можете проверить, правильно ли мастер
интерпретирует исходные данные.
Используя переключатель Ряды в можно
задать расположение рядов данных в строках
или в столбцах.

82.

83.

6. Щелкните кнопку Далее.
В открывшемся диалоговом окне Мастер
диаграмм: параметры диаграммы перейдите на
вкладку Заголовки.

84.

7. В поле Название диаграммы введите заголовок
диаграммы.
8. В поле Ось Х введите название оси Х.
9. В поле Ось Y введите название оси Y.
10. На вкладке Подписи данных в категории Подписи
значений установите флажок Значения (у каждого
графика диаграммы будут выставлены цифровые
значения).
11. Мастер диаграмм автоматически размещает легенду
в поле диаграммы и располагает ее справа.
Если Вы хотите удалить легенду или расположить ее
в другом месте, на вкладке Легенда установите или
сбросьте соответствующие флажки.
12. После внесения всех изменений щелкните кнопку
Далее.

85.

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

86.

14. Щелкните кнопку Готово.
90
80
70
60
50
40
30
20
10
0
Восток
Запад
Север
1 кв
2 кв
3 кв
4 кв

87.

Удачи на
практической работе