Основные сведения при работе с MS Excel

1.

ОРОЗ
ЛК №2-3
Тема: Основные сведения при работе с MS
Excel
Вопросы:
1. Возможности MS Excel
2. Ячейки. Стили ссылок.
3. Ссылки. Общие понятия.
4. Абсолютные, относительные и смешанные ссылки
5. Ввод данных в ячейку
6. Присваивание имени ячейке или диапазону. Защита
данных.
1

2.

ОРОЗ
1)
ЛК №2-3
Возможности MS Excel
В качестве базового инструмента организации, сбора ,
хранения, обработки и представления экономической
информации нами будет использован MS Excel и Visual
Basic for Application (VBA)
Мощные встроенные средства MS Excel позволяют
создавать отчетную, финансовую и экономическую
документацию, проводить обработку, анализ данных и
принимать на этой основе оптимальные решения, т.е.
решать достаточно широкий круг задач.
2

3.

ОРОЗ
ЛК №2-3
MS Excel - универсальное средство, обеспечивающее:
1. Широкий набор инструментов по форматированию и
представлению данных.
2. Решение математических задач, в т.ч. экономического
характера, т.е. построение графиков, решение уравнений,
работа с формулами массивов и т.д.
3. Финансовый анализ
4. .Статистический анализ. Для п.3,4 имеется большой
спектр встроенных функций.
5. Поиск решения и решение оптимизационных задач.
6. Работу с табличными базами данных.
7. Работу с применением VBA.
3

4.

ОРОЗ
2)
ЛК №2-3
4
Ячейки. Стили ссылок
Ячейка однозначно идентифицируется своим адресом,
который может быть представлен в одном из двух форматов:
А) Формат адресации ячеек
-- А1
В этом формате адрес ячейки состоит из имени столбца
(А,В,С и т.д. до значения 256) и номера строки (1,2,3 и
т.д. до 65356). Например:
А1, В25, С70.
Б) Формат адресации ячеек
-- R1C1.
В этом формате ячейка идентифицируется номером
строки R и столбца С. Например: R3C2 - это ссылка на
ячейку В3(формат А1).
Этот формат может быть полезен когда нас больше
интересует относительное расположение ячеек на листе,
чем их абсолютные адреса. Это полезно при изучении

5.

ОРОЗ
ЛК №2-3
5
Пример. Поясним вышесказанное и покажем как можно
вводить данные в ячейку в формате R1C1.
В ячейку R10C2(В10) ввести формулу, которая
суммирует значения в ячейках R1C1(А1) и R1C2(В1) , т.е.
В10=А1+В1 Это можно сделать двумя способами:
1. В режиме относительных ссылок
В ячейку В10 ввести
=R[-9]C[-1]+R[-9]C
Здесь отрицательные номера строк и столбцов говорят о
том, что ячейка, на которую указывает ссылка, находится
выше или левее ячейки с формулой, а квадратные скобки
обозначают относительность ссылки. Т.е. эта формула
читается следующим образом: сложить содержимое
ячейки, расположенной на девять строк выше и на один
столбец левее ячейки с формулой, с содержимой ячейки,
находящейся на девять строк выше в том же столбце(2/1).

6.

ОРОЗ
ЛК №2-3
6
2. В режиме абсолютных ссылок.
В ячейку В10 ввести = R1C1 + R1C2. В этом случае
( когда квадратные скобки отсутствуют) Excel считает,
что вы используете абсолютную ссылку на ячейки в
строке 1 и столбцах 1 и 2.
При копировании формул, полученных каждым из
вышеописанных способов происходит следующее:
По 1-ому способу( относительность ссылок)
При копировании ячейки, содержащей формулу
происходит «настройка» формулы, основываясь на ее
положении относительно ячеек на которые ссылается
формула.
Скопируем формулу из ячейки В10 в ячейку С12.
По 2-ому способу( абсолютность ссылок)
Скопируем формулу из ячейки В10 в ячейку С11.

7.

ОРОЗ
ЛК №2-3
7
3)
Ссылки. Общие понятия
Ссылка является идентификатором ячейки или группы
ячеек в книге. Создавая формулу, содержащую ссылки на
ячейки, вы связываете формулу с ячейками
книги.Значение формулы зависит от содержимого ячеек,
на которые указывают ссылки.
Ссылки позволяют использовать в формулах
содержимое других ячеек. Пример. А1= 10, А3=А1, тогда
в ячейке А3 будет число 10.
В общем случае ссылки можно разделить на пять видов.
1. Ссылки на ячейки текущего рабочего листа
=А1
=В5+С2
2. Ссылки на другие листы той же книги
=Лист2!А3
=Экзамен!D5
=‘32эи’!H3
Эти ссылки можно набирать вручную или вводить с
помощью мыши (щелкая по ячейке лев. кн. мыши)

8.

ОРОЗ
ЛК №2-3
8
У 1-го и 2-го вида при формировании ссылки с
помощью выделения ячейки Excel создает относительную
ссылку.
3. Ссылки на листы других книг. Возможно 2 случая:
А) если две книги открыты и ссылка создается путем
выделения ячейки
=[Имя книги.xls]Лист5!$A$3 т.е. по умолчанию
создается абсолютная ссылка
Б) если книга, на которую указывает ссылка закрыта,
то в ссылке необходимо указать полный путь доступа к
папке, где хранится книга
=‘C:\Имя папки\[Имя книги.xls]Лист5’!$A$3
В общем виде формирование ссылок с помощью
выделения поясним на примере.
Пример.
Необходимо в ячейку А1 листа Зачет книги ФПУ
ввести ссылку на ячейку А2 листа Экзамен книги 2-ой
курс .

9.

ОРОЗ
ЛК №2-3
9
Последовательность действий:
• в окне приложения Excel последовательно открыть
документы ФПУ и 2-ой курс
• в меню Окно выбрать команду Расположить и
установить переключатель в одно из положений Слеванаправо, Сверху-вниз и т.д. (на экране будут видны обе
книги)
• выделить ячейку А1 листа Зачет книги ФПУ и ввести
знак равенства
• в любом месте окна книги 2-ой курс для ее активизации
• щелкнуть л.кн. мыши на ярлыке листа Экзамен
• щелкнуть л.кн. мыши по ячейке А2
• нажать клавишу Enter для фиксации ввода формулы.
4.
Объемные ссылки
Объемные (трехмерные) ссылки - это ссылки на ячейки
диапазона листов в книге.

10.

ОРОЗ
ЛК №2-3
10
Поясним это на примере:
Пример.
1. Создано 100 листов в книге
2. Необходимо определить сумму значений
из диапазона В1:В15, расположенных во всех ста листах
Для определения суммы в ячейку необходимо ввести
формулу =СУММ(Лист1:Лист100!В1:В15)
=СУММ(‘29 эи:32 эи’!В1:В15)
При работе с объемными ссылками можно использовать
до 11 функций, например, СУММ, СРЗНАЧ, МАКС, МИН
и т.д.
5. Циклические ссылки
Циклическая ссылка - это ссылка, которая зависит от
своего собственного значения. Наиболее типичной ц.с.
является формула, которая содержит ссылку на ту же
ячейку, в которую она введена. Пример. В ячейку А1
введена формула =В5+С3*А1. Excel выдает сообщение
об ошибке, которую необходимо устранить.

11.

ОРОЗ
ЛК №2-3
11
Многие циклические ссылки могут быть разрешены с
помощью итерационных вычислений ( т.е.
последовательным приближением к конечному
результату с помощью вычислений по циклу)
4) Относительные, абсолютные и смешанные
ссылки
Рассмотрим ссылки, о которых можно вести речь только
при копировании ячейки, содержащей формулу. В
противном случае (перемещение) действие формул,
содержащих эти ссылки одинаково.
Такие ссылки можно разделить на три группы. Они
являются частным случаем рассмотренных выше пяти
видов ссылок.
4.1. Абсолютная ссылка - использует для указания на
ячейку ее фиксированное положение на листе, например,
ячейка находится в столбце А строки 1.
В а.с. перед именем столбца и номером строки ставится
знак $.
( а.с. на ячейку А5
$A$5)

12.

ОРОЗ
Пример.
ЛК №2-3
12
В ячейку А1 введена формула =$B$1
Необходимо скопировать формулу, введенную в ячейку
А1 на диапазон С2:С4. В результате получим.
Т.е. при копировании ячейки В1, ее значение не
изменилось, т.к. она была задана абсолютно.
4.2. Относительная ссылка - указывает на ячейку,
основываясь на ее положении относительно ячейки, в
которой находится формула, например, на две строки
выше.
При копировании ячейки, которая содержит формулу с
относительными ссылками, формула в области вставки и
исходная формула в копируемой области не ссылаются на
одни и те же ячейки. Вместо этого Excel изменяет ссылки,
исходя из позиции вставленной ячейки.
Пример. В ячейку С1 введена формула =А1 (это
означает отн. ссылку на ячейку А1, т.е. ячейка А1 находится левее ячейки С1 на два столбца в той же строке)

13.

ОРОЗ
ЛК №2-3
Скопируем ячейку С1 на диапазон D2:D4. В
результате получим.
4.3. Смешанная ссылка содержит относительную и
абсолютную ссылки.
В смешанной ссылке знак $ ставится перед абсолютной
частью ($A1-а.с. на столбец, G$5 - а.с. на строку ).
Смешанные ссылки используются, чтобы при
копировании зафиксировать только часть ссылки на
ячейку. Т.е. При копировании смешанной ссылки Excel
фиксирует абсолютную часть ссылки и настраивает ее
относительную часть в зависимости от позиции ячейки, в
которую вы копируете формулу.
Пример. В ячейку С1 введена формула =$А1.
Скопируем ячейку С1 на диапазон D2:D4. В результате
получим.
Т. о . мы рассмотрели ссылки, которые позволяют при
копировании изменять исходные формулы.
13

14.

ОРОЗ
ЛК №2-3
5)
Ввод данных в ячейку
В ячейки рабочего листа можно вводить два типа
данных: константы и формулы.
Константы подразделяются на три осн-е категории:
• числовые значения (1230, 456, 1,2Е+12 и т.д.)
• текстовые значения ( метка, слово, номер 12, № 12 и
т.д.)
• значения дат и времени ( 1 янв. 2000, 15:30:35 и т. д.)
Кроме этого в Excel существует два специальных типа
констант:
• логические выражения (ИСТИНА, ЛОЖЬ)
• ошибочные значения (их семь, например, #ДЕЛ/0! ,
#ЗНАЧ! )
Рассмотрим эти категории.
14

15.

ОРОЗ
ЛК №2-3
15
5.1. Числовые значения
Они могут содержать только цифры от 0 до 9 и
специальные символы ( + - Е е () . , $ %). Число может
содержать до 1600 символов. Назначение этих символов
общеизвестно, поэтому отметим некоторые особенности.
• Символ Е или е используется при вводе чисел в
экспоненциальном формате ( 1,2Е4=12000)
• числовые значения заключенные в круглые скобки
интерпретируются как отрицательные (запись
используется в бух. учете)
• точка или запятая используются как разделитель
десятичных знаков. Пробел может использоваться в
качестве разделителя групп разрядов: сотен от тысяч,
тысяч от миллионов и т.д. (1_234_567 в ячейке, а в строке
формул 1234567 т.е. без пробелов).

16.

ОРОЗ
ЛК №2-3
16
• если закончить ввод числа знаком %, то Excel применит
к ячейке процентный формат
• если при вводе числа используется /, то Excel
рассматривает введенное значение как дробь. (Вводим в
ячейку 11_5/8, то в строке формул -- 11,625 и ячейке
«назначится» дробный формат).
Чтобы Excel не интерпретировал дробь как дату, введите перед дробью ноль и пробел (пробел) 0_5/8 или 11_5/8
Отображаемые и хранимые значения
Значения которые появляются в ячейке -отображаемыми
Значения которые хранятся в ячейках и появляются в
строке формул - хранимыми.
Например. В ячейке отображено число 0,145
(отображаемое зн.), а в строке формул - 555( хранимое
зн.)

17.

ОРОЗ
ЛК №2-3
17
В ячейку можно вводить число содержащее до 1600
знаков, однако число отображаемых в ячейке значащих
цифр не более 15.
Excel сохраняет числа с точностью до 15 цифр и
преобразует любые цифры после пятнадцатой в нули (
справедливо для целой части числа). В десятичной дроби
отбрасываются все цифры после пятнадцатой. Кроме
этого возможно округление отображаемых чисел с
выводом символов #, указывающих, что данное число не
помещается в ячейку.Количество выводимых цифр зависит от ширины столбца. Пример. Ширина столбца =8,43
Вводимое значение Отображаемое
значение
123456789012345678
1,23456789012345678
1,23Е+17
1,234568
Хранимое
значение
123456789012345000
1,23456789012345

18.

ОРОЗ
ЛК №2-3
18
5.2.
Текстовые значения
В общем случае ввод текста аналогичен вводу числовых
значений. Отметим особенности ввода текстовых знач-й.
5.2.1 Длинные текстовые значения
При вводе длинных текстовых значений в одну ячейку
возможен перенос текста в следующие строки ячейки без
наложения на другие ячейки.
Способ1. Выделить ячейку и выполнить команду
Формат-Ячейки -вкладка Выравнивание и установить
флажок Переносить по словам.
В этом случае будет осуществляться перенос слов,
которые не помещаются по ширине ячейки.
Способ 2. Выделить ячейку. Перейти в режим
редактирования текста в ячейке. Установить мигающий
курсор в то место текста, который д.б. перенесен на
другую строку и нажать комбинацию клавиш Alt+Enter.

19.

ОРОЗ
ЛК №2-3
19
В этом случае перенос текста будет осуществляться в
указанном нами месте.
5.2.2
Числовой текст
Числовой текст может состоять из текста и чисел или
только чисел ( 123, АВС456) Пример. Необходимо
отобразить в ячейке число +1. Это можно сделать двумя
способами.
А) Начать ввод с апострофа (‘ + 1)
Б) Начать ввод со знака равно, заключив текст(число) в
двойные кавычки
=“ + 1_АВС” .
Над числовым текстом, состоящим только из чисел
можно производить математические операции.
Например. Числовой текст “12345” и “5678”можно
суммировать, делить и т.д.

20.

ОРОЗ
ЛК №2-3
20
5.3.
Значения дат и времени
А)
Даты
Основной единицей измерения времени в Excel
являются сутки. Они представляются последовательными
целыми числами от 1 до 65380.
Базовая дата, представляемая значением 1, - это
воскресенье, 1 января 1900(1904) года.
Дата сохраняется в виде значения, которое равно
количеству дней между базовой и заданной датой.
Пример. 10 февраля 2003 года представляется
значением 37662, т.к. разница между 1 янв. 1900 г. и 10
фев. 2003 г. составляет именно 37662 дня.
Б) Время
Время суток - это десятичная дробь, величина которой
определяется отношением части суток между их началом

21.

ОРОЗ
ЛК №2-3
21
(12:00 ночи) и заданным временем, к общему
времени (т.е. к 24 часам).
Пример. 12 часов дня соответствуют значению 0,5
18 часов --18/24=0,75.
Пример. Определим число соответствующее абсолютному времени. 14 ч 09 мин 03 с в день 23.09.2000 года.
Это число 36822+0,5896=36822,5896, где
24 ч = 86400 с
14:09:03=50943 с , а значение равно 50943/86400=0,5896
Таким образом, назначая числовые значения дня, часам,
минута и секундам Excel позволяет выполнять сложные
вычисления с датами и временем.
Для того, чтобы однозначно сказать, что мы ввели в
ячейки ( число, дату-время или текст) необходимо знать:

22.

ОРОЗ
ЛК №2-3
22
• текст выравнивается по левому краю ячейки
• числа, даты, время выравниваются по правому краю
ячейки
5.4.
Ввод формул в ячейки
Все формулы в Excel должны начинаться со знака
равенства.
=А1+В2 или =СУММ(В1:С5) и т.д.
!!! При вводе формул ставится знак =, а при вводе
числового текста = и двойные кавычки.
Для исключения ошибок при наборе формулы, ее лучше
вводить с помощью мыши. Пример. В ячейку В10
ввести формулу = А9+А10.
Для этого:
• выделить ячейку В10 и ввести знак равно
• щелкнуть лев. кн. мыши на ячейке А9 и ввести знак +
• щелкнуть на ячейке А10 и нажать клавишу Enter.

23.

ОРОЗ
ЛК №2-3
23
5.5. Объединение данных, расположенных в разных
ячейках
Текстовые и числовые значения, значения дат и времени
расположенные в разных ячейках можно объединять.
Для объединения используется оператор & (амперсанд).
Пример. Ячейка А2 содержит текст АБВГД, а ячейка А3
--ЕЖЗИ и в ячейку А1 введена формула =А2&А3, то в
ячейке А1 будет АБВГДЕЖЗИ.
Чтобы вставить пробел или значение между данными
двух объединяемых ячеек, используется формула
=А2&”__”&А3.
С помощью оператора & можно объединять:

24.

ОРОЗ
ЛК №2-3
24
а) числовые значения
А3=123 А4=456 и А1=А3&А4
А1=123456
б) числовое значение и текст
А5=АБСД А6=123 и А7=А5&А6
А7=АБСД123
с) дату и время
А1=21.01.2003 А2=15:55 и А3=А1&А2
А3=376420,66319 ( дата и время переводятся в числовой
формат)
д) текст и дату (время)
А1=Текст
А2=15:55 А3=А1&А2
А1=21.01.2003 А2=Текст А3=А1&А2
А3=Текст0,66319
А3=37642Текст
е) данные хранящиеся в трех и более ячейках
A2&A3&”Текст”&A4&”___”&B5

25.

ОРОЗ
ЛК №2-3
25
6)
Присваивание имен. Защита данных.
6.1 Ячейкам и диапазонам ячеек можно назначать имена и
затем использовать их в формулах. Имена, определенные в
текущем листе, могут использоваться в любых других
листах книги.
Имена (на уровне книги) можно определять 2 способами.
А) Определение имен в поле имени
Для этого щелкните на поле имени в строке формул,
введите Текст и нажмите клавишу Enter. После этого в
поле имени появится слово Текст . Если имя определено
для диапазона ячеек, оно появится в поле имени только
при выделении всего диапазона.
Б) Определение имен с использованием команды Имя
Используется команда Вставка-Имя -Присвоить.

26.

ОРОЗ
ЛК №2-3
26

27.

ОРОЗ
ЛК №2-3
В этом случае можно использовать текст в соседних
ячейках для присвоения имен ячейкам или диапазонам, а
также переопределять существующие имена.
Если в ячейке, расположенной выше или левее
выделенной области ячеек содержится текст, то он будет
предложен в поле Имя в качестве имени( см. сл.26).
Пример 1. Лист 1 ячейка А1=15 и А1 присвоено имя
Расчет.
Лист 2 в ячейку В10 ввести формулу =Расчет, то
получим в ячейке В10=15.
Пример 2. Именованные константы.
В формуле для расчета цены товара использовать
именованную константу - НДС=0,25 (25%). Для этого:
в окне диалога Присвоение имени: в поле Имя ввести
НДС, а в поле Формула - 0,25.
27

28.

ОРОЗ
ЛК №2-3
28
На любом листе набрав =В5*НДС получим =В5*0,25
Пример 3. Имена на уровне листа.
В поле Имя окна диалога Присвоение имени введите
Лист1!Расчет (действие этого имени только на Лист1)
Правила присвоения имен ячейкам и диапазонам:
• имя должно начинаться с буквы, обр. косой черты(\) или
символа подчеркивания ( _ )
• имя должно содержать только буквы, цифры, обр. косую
черту и символ подчеркивания
• нельзя использовать имена, которые могут трактоваться
как ссылки на ячейки
• в качестве имен могут использоваться одиночные буквы
за исключением R и С
• заменяйте пробелы символами подчеркивания

29.

ОРОЗ
ЛК №2-3
6.2. Защита данных
Термин "защита данных" подразумевает защиту от
случайных потерь или изменений, а также ограничение
доступа к данным.
Для пользователей Excel выделим две ступени защиты.
1. Защита файлов
2. Защита данных в Excel
29

30.

ОРОЗ
ЛК №2-3
30

31.

ОРОЗ
ЛК №2-3
Защита файлов
Два типа паролей:
• пароль для открытия файла
• пароль разрешения записи (только для чтения)
Пароль - до 15 символов, с учетом регистра букв.
Чтобы установить пароль необходимо:
1.Выполнить команду Сохранить как, введя имя файла
2. Нажать кнопку Параметры (Сервис)
3. Выбрать нужный вариант защиты и ввести пароль
4. Нажать кнопку ОК
31

32.

ОРОЗ
ЛК №2-3
5. Снова ввести пароль в окне диалога Подтверждение
пароля и нажать кнопку ОК
6. Нажать кнопку Сохранить
Защита данных в Excel
Можно защитить структуру книг, отдельные ячейки,
диаграммы, сценарии и т.д. От несанкционированного
доступа или изменения. После включения защиты
изменить заблокированный элемент невозможно.
А) Защита книги
Выполняется команда Сервис- Защита-Защитить книгу
и заполняется окно диалога Защита книги.
32

33.

ОРОЗ
Защита книги
ЛК №2-3
Защита листа
33

34.

ОРОЗ
ЛК №2-3
структура --позволяет защитить структуру рабочей
книги. Ее установка не позволит пользователю добавить
или удалить, переместить или переименовать ни один
рабочий лист.
окна --пользователь не сможет переместить, изменить
размер, скрыть или сделать видимым, а также закрыть
окно, содержащее рабочую книгу.
Б) Защита листа
содержимому – запрет на внесение изменений в ячейки
рабочего листа или диаграммы
объекты - защита всех графических объектов листа
сценарии - защита установок, сохраненных с помощью
диспетчера сценариев
34

35.

ОРОЗ
ЛК №2-3
35
В) Скрытие данных в рабочих книгах и листах
Скрытие рабочей книги – команда Скрыть меню Окно.
Скрыть рабочий лист – команда Формат-Лист –Скрыть.
Скрыть строку (столбец) – Формат-Строка (Столбец)Скрыть.
Для отображения книги, листа, строки, столбца –
обратные действия с выполнением команды –Отобразить.