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

Кафедра

Кафедра

Темы

информатики ЛЕКЦИИ ПО ИНФОРМАТИКЕ

информатики

УГАТУ

УГАТУ

Для студентов факультетов ИРТ, АТС

Электронные таблицы MS EXCEL

групп МРТ, САУ, СТС, КТО

•

Основные понятия

Составители:

•

Технология работы

доценты кафедры информатики

•

Встроенные функции

Карчевская Маргарита Петровна

•

Диаграммы

Рамбургер Ольга Леонардовна

•

Работа со списками

Информатика курс 1, семестр 1, 2012 г.

1

Информатика

курс 1, семестр 1, 2012 г.

2

Кафедра

Кафедра

Интерфейс EXCEL

информатики

информатики

УГАТУ

УГАТУ

Заголовок окна

Строка ввода

Панели инструментов

Электронные таблицы

меню

столбец

Основные понятия

Диапазон столбцов

строка

Технология работы в MS EXCEL

Диапазон строк

Диапазон ячеек

Ячейка

Листы

Информатика

курс 1, семестр 1, 2012 г.

4

Кафедра

Основные понятия EXCEL

Кафедра

Основные понятия EXCEL

информатики

информатики

УГАТУ

УГАТУ

Строки, столбцы, ячейки и их обозначения.

Диапазон строк обозначается номерами граничных строк,

Рабочая область электронной таблицы состоит из строк и

разделенными двоеточием или точкой, например, 4:7

или 7:4 или 4.7 или 7.4.

столбцов.

Имена строк – это их номера от 1 до 65535.

Вопрос: сколько строк в указанном диапазоне?

Имена столбцов – это символы латинского алфавита от А

Диапазон столбцов обозначается аналогично, но вместо

до Z, затем от АА до AZ, ВА до BZ и т. д.

номеров строк используются обозначения столбцов,

Пересечение строки и столбца образует ячейку таблицы,

например, C:F

Вопрос: сколько столбцов в указанном диапазоне?

которая обозначается <столбец><строка>, например,

А2, СZ4.

Прямоугольный блок (диапазон) ячеек обозначается

Используется и другая нотация (обозначение):

адресами двух диагональных ячеек (в любом порядке),

R<номер строки>C<номер столбца>,

разделенных двоеточием или точкой, например, F5:D4.

Вопрос: сколько ячеек в указанном диапазоне?

например, R2C1, R72C3. Она удобна при программировании

Ответы : 4 строки, 4 столбца, 6 ячеек

в EXCEL

Информатика

курс 1, семестр 1, 2012 г.

5

Информатика

курс 1, семестр 1, 2012 г.

6

Кафедра

Содержимое ячеек в EXCEL

Кафедра

Форматирование ячеек

информатики

информатики

УГАТУ

УГАТУ

1.

Текст.

Возможности форматирования для внешнего

2.

Число. Содержит цифры, запятую и знак числа.

оформления таблиц аналогичны MS WORD:

3.

Формула.

•

Форматирования шрифта

Если в ячейку вводится не число и не формула (или

•

Выравнивание содержимого ячейки

формула с ошибкой), информация воспринимается как

текстовая.

• Заливка и оформление границ ячеек

По умолчанию текст выравнивается по левому краю

• Изменение ширины столбцов и высоты ячеек

ячейки.

Текст может быть большой длины,

•

И т.д.

кажется, что он «занимает» и

соседние ячейки. На самом деле

текст располагается в одной ячейке.

Если в соседнюю ячейку ввести

информацию, текст «обрезается».

Но в ячейке он сохраняется. См.

строку ввода

Информатика

курс 1, семестр 1, 2012 г.

7

Информатика

курс 1, семестр 1, 2012 г.

8

Кафедра

Оформление внешнего вида ячеек

Кафедра

Числа в ячейках EXCEL

информатики

информатики

УГАТУ

УГАТУ

Команда меню

В ячейках могут быть числа

Формат →

•

Целые

Ячейки…

•

Вещественные

Вкладка

«Выравнивание»

позволяет задать:

Не хватает ширины

• выравнивание

столбца для вывода числа

текста внутри

ячейки,

EXCEL предоставляет широкий

• перенос слов

выбор форматов

внутри ячейки,

Экспоненциальная форма

• объединение

представления чисел (не

7,89954*1015

путать с форматированием

ячеек

внешнего вида таблицы!)

Информатика

курс 1, семестр 1, 2012 г.

9

Информатика

курс 1, семестр 1, 2012 г.

10

Кафедра

Числовые форматы ячеек в EXCEL

Кафедра

Числовые форматы ячеек в EXCEL

информатики

информатики

УГАТУ

УГАТУ

Команда

Форматы представления числовых данных:

Формат → Ячейки

•

Общий формат используется по умолчанию, для отображения

или пункт

текстовых и числовых значений произвольного вида

Формат ячеек… в

•

Числовой формат с фиксированным количеством десятичных

контекстном меню

знаков обеспечивает представление чисел в ячейках с

(правая кнопка

заданным количеством цифр после десятичной точки.

мыши)

•

Процентный формат обеспечивает представление введенных

данных в форме процентов со знаком % (в соответствии с

установленным количеством десятичных знаков). Например, в

ячейку, имеющую процентный формат, вводится число 0,15.

Числа выводятся

Будет выведено 15%.

в таблице

•

Денежный формат обеспечивает такое представление чисел,

соответствии с

где каждые три разряда разделены определенным

разделителем. Добавляется также знак валюты страны (р.)

установленным

форматом.

• Время – 8 вариантов формата

Информатика

курс 1, семестр 1, 2012 г.

11

Информатика

курс 1, семестр 1, 2012 г.

12

Кафедра

Числовые форматы ячеек в EXCEL

Кафедра

Формулы в EXCEL

информатики

УГАТУ

информатики

УГАТУ

Дата

Формула записывается в строку и состоит из

дробный

1. Знаков операций (*, /, +, -, ^, >, <, >=, <=, =, );

2. Круглых скобок

3. Операндов (элементов, над которыми выполняются

денежный

операции).

Те же данные, но в

Операндами могут быть:

формате целых

чисел

1. Константы (числовые, текстовые, т.д.)

Количество дней между датами

2. Встроенные Функции

Дата хранится как количество дней, прошедших с 01.01.1900

3. Ссылки на строки, столбцы, ячейки или их диапазоны

12 форматов внешнего представления дат, например :

4. Операции выполняются в соответствии с приоритетами

ДД-МММ-ГГ (04-Янв-95);

МММ-ДД-ГГ(Янв-04-95);

ДД-МММ (04-Янв);

МММ-ГГ (Янв-95).

Информатика

курс 1, семестр 1, 2012 г.

13

Информатика

курс 1, семестр 1, 2012 г.

14

Кафедра

Формулы в EXCEL

Кафедра

Формулы в EXCEL

информатики

информатики

УГАТУ

УГАТУ

Арифметические операции

Ввод формулы в ячейку

начинается со знака «=».

ЭТ переходит в режим ввода

формул.

Адреса ячеек (ссылок) можно не

вводить с клавиатуры, а

указывать ячейки мышкой.

В ячейке, содержащей формулу,

отображается результат

Скобки меняют порядок выполнения операций.

вычисления.

Если отображается не результат,

Опускать знаки операций в формулах нельзя!!!

значит, скорее всего, в формуле

есть ошибка, она

воспринимается как текст.

Информатика

курс 1, семестр 1, 2012 г.

15

Информатика

курс 1, семестр 1, 2012 г.

16

Кафедра

Формулы в EXCEL

Кафедра

Формулы в EXCEL

информатики

информатики

УГАТУ

УГАТУ

Ячейка С3 зависит от

Если установить режим отображения формул,

(Сервис → параметры → вкладка «Вид») в ячейках будут

значений в ячейках А1,

отображаться формулы, а не результаты вычислений.

А2, В1, В2.

С3 называют зависимая

ячейка

А1, А2, В1, В2 называют

влияющие ячейки

При изменении значения

в любой влияющей

ячейке результат

вычислений в C3 тут же

изменится.

Панель инструментов «Зависимости»

Информатика

курс 1, семестр 1, 2012 г.

17

Информатика

курс 1, семестр 1, 2012 г.

18

Кафедра

Кафедра

Относительные ссылки

информатикиОтносительные и абсолютные ссылки

УГАТУ

информатики

УГАТУ

В формулах ЭТ могут использоваться ссылки на ячейки или их

Ссылки на ячейки в формулах указывают на расположение

диапазоны.

ячеек – операндов (влияющих ячеек) относительно

Ссылки могут быть четырех видов:

ячейки, содержащей формулу (зависимой ячейки)

• Относительная ссылка – А1.

• Ссылка А1 указывает на ячейку, которая смещена

•

Абсолютная ссылка

– $A$1.

относительно ячейки С2 на одну строку вверх и на два

•

Смешанная ссылка

– $A1 A$1.

столбца влево;

Абсолютная часть ссылки (за знаком $) не меняется при

копировании ячеек, содержащих формулы.

• Ссылка В4 указывает

Относительная часть ссылки меняется в соответствии с

на ячейку, которая

относительным расположением влияющих и зависимых ячеек

смещена

Ссылка на диапазон (ячейку) другого листа имеет вид, например,

относительно ячейки

Лист2!F5.

С2 на две строки вниз

Ссылка на диапазон (ячейку) другой рабочей книги (файла *.XLS) имеет

и один столбец влево.

вид, например, =[Поверхность.XLS]Лист1!$A$20

Информатика

курс 1, семестр 1, 2012 г.

19

Информатика

курс 1, семестр 1, 2012 г.

20

Кафедра

Разработка таблиц

Кафедра

Копирование ячеек

информатики

информатики

УГАТУ

УГАТУ

При грамотной разработке таблиц пользователь вводит

Автоматическое изменение ссылок при копировании ячейки A3,

при использовании относительных, абсолютных и

не так уж много формул в ячейки. Остальные

смешанных ссылок

формулы получает копированием и/или

До

После

перемещением ячеек.

Рассмотрим поведение относительных и абсолютных

ссылок при копировании и перемещении ячеек

Кроме того, при разработке таблиц используются

вставки и удаления строк, столбцов, ячеек.

Рассмотрим поведение ссылок при вставке и удалении

строк, столбцов, ячеек

Информатика

курс 1, семестр 1, 2012 г.

21

Информатика

курс 1, семестр 1, 2012 г.

22

Кафедра

Перемещение ячеек

Кафедра

Перемещение ячеек

информатики

информатики

УГАТУ

УГАТУ

При перемещении ячеек сохраняются зависимости

До

После

влияющих и зависимых ячеек в формулах как для

относительных, так и для абсолютных ссылок !!!

До

После

После перемещения содержимого трех ячеек A1, A2 и A3

формула в С3 изменилась с учетом перемещения влияющих

ячеек

После перемещения содержимого ячейки A3 в ячейку

После перемещения содержимого ячейки A3 в ячейку С3

СЗ формула не изменилась

формула не меняется, а в зависимой ячейке В1 формула

изменяется

Информатика

курс 1, семестр 1, 2012 г.

23

Информатика

курс 1, семестр 1, 2012 г.

24

Кафедра

Пример копирования ячейки

Кафедра

Пример копирования ячейки

информатики

информатики

УГАТУ

УГАТУ

Автоматическое изменение ссылок при

Абсолютные и смешанные ссылки

копировании формулы с относительными

ссылками из ячейки B2 в ячейки A1, A3, C1,

C4, D2

Если в режиме ввода нажать клавишу F4, изменится тип текущей

ссылки (той, на которой находится курсор). Тип меняется

циклически A1 -> $A$1 -> A$1 -> $A1 -> A1

Информатика курс 1, семестр 1, 2012 г.

25

Информатика курс 1, семестр 1, 2012 г.

26

Кафедра

Еще о копировании

Кафедра

Ошибки в формулах

информатики

информатики

УГАТУ

УГАТУ

Если надо скопировать не формулы, по которым

#####

Столбец недостаточно широк или дата и время являются

проводятся вычисления, а только результат,

отрицательными числами.

#ЗНАЧ!

Данная ошибка возникает при использовании недопустимого

используйте команду Правка → Специальная вставка.

типа аргумента или операнда.

#ДЕЛ/0!

Используется ссылка на пустую ячейку или ячейку,

содержащую 0 в качестве делителя

#ИМЯ?

EXCEL не может распознать имя, используемое в формуле

#Н/Д

Значение недоступно функции или формуле

#ССЫЛКА!

Ссылка на ячейку указана неверно

#ЧИСЛО!

При неправильных числовых значениях в формуле или

функции

#ПУСТО!

Задано пересечение двух областей, которые в

действительности не имеют общих ячеек

Информатика курс 1, семестр 1, 2012 г.

27

Информатика курс 1, семестр 1, 2012 г.

28

Кафедра

Кафедра

Функции EXCEL

информатики

информатики

УГАТУ

УГАТУ

Электронная таблица

EXCEL имеет большой

Электронные таблицы

набор встроенных

функций.

Функции разделены на

группы –

математические,

логические,

Встроенные функции MS EXCEL

статистические,

финансовые, т.д.

Для ввода функций

используют Мастер

функций, который

можно вызвать

кнопкой F(X)

Информатика

курс 1, семестр 1, 2012 г.

30

Кафедра

Функции EXCEL

Кафедра

информатики

УГАТУ

информатикиНекоторые математические функции

УГАТУ

Каждая встроенная функция имеет уникальное имя. При

работе с таблицей используются русские имена

функций, например, ЕСЛИ, СУММ.

При обращении к функции в круглых скобках указывается

список аргументов (фактических параметров), они

разделяются точкой с запятой «;».

Функции могут не иметь параметров, иметь строго

определенное количество параметров, неопределенное

количество параметров, необязательные параметры.

Параметры называют аргументами функций.

Пример: функция ПИ()

(не требует аргументов)

Информатика

курс 1, семестр 1, 2012 г.

31

Информатика

курс 1, семестр 1, 2012 г.

32

информатики

Примеры функций EXCEL

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

Кафедра

Кафедра

УГАТУ

УГАТУ

•

ПИ()

(без аргументов)

Если в списке аргументов (параметров) ячейка

• Корень (<аргумент>) (заданное количество аргументов)

встречается N раз, она N раз используется в

вычислениях.

•

ЕСЛИ( <условие>;<значение_если_истина>;

Список аргументов с повторяющимися ячейками

[<значение_если_ложь>]) (может быть 2 или 3

аргумента);

•

СУММ(А1:A10;C1:C10;G11;100) (до 30 аргументов, но

Результат в D6:

не менее одного);

Сумма диапазона А1:В5 = 8;

Функции могут быть вложенными.

Сумма диапазона В3:С7 = 8;

ЕСЛИ( СУММ(А1:A10) >20; ЕСЛИ(G11<0;1;3);5)

Сумма диапазона А4:В5 = 3;

Максимальный уровень вложений - семь

Итого 19

Информатика курс 1, семестр 1, 2012 г.

33

Информатика курс 1, семестр 1, 2012 г.

34

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

информатики

Функции EXCEL

Кафедра

Кафедра

УГАТУ

УГАТУ

Если диапазоны разделены пробелом, берется

ПРОИЗВЕД

пересечение диапазонов.

НЕ

СУММ

ИСТИНА

Список аргументов с пересечениями диапазонов ячеек

СУММКВ

Логические

ЛОЖЬ

Математические

СУММЕСЛИ

МОПРЕД

И

ИЛИ

Результат в D5:

МОБР

ЕСЛИ

Пересечение диапазонов

МУМНОЖ

А1:C5 А4:С7 – это диапазон

А4:С5

МАКС

Сумма диапазона А4:С5 = 5;

МИН

Ссылки и

ВПР

Статистические

СРЗНАЧ

5

массивы

ГПР

Итого

СЧЕТ