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

Август 12, 2022

Главная
Информатика
Основные сведения при работе с MS Excel

Содержание

2. ЛК №2-3 1) Возможности MS Excel В качестве базового инструмента организации, сбора , хранения, обработки и
3. ЛК №2-3 MS Excel - универсальное средство, обеспечивающее: 1. Широкий набор инструментов по форматированию и представлению
4. ЛК №2-3 2) Ячейки. Стили ссылок Ячейка однозначно идентифицируется своим адресом, который может быть представлен в
5. ЛК №2-3 Пример. Поясним вышесказанное и покажем как можно вводить данные в ячейку в формате R1C1.
6. ЛК №2-3 2. В режиме абсолютных ссылок. В ячейку В10 ввести = R1C1 + R1C2. В
7. ЛК №2-3 3) Ссылки. Общие понятия Ссылка является идентификатором ячейки или группы ячеек в книге. Создавая
8. ЛК №2-3 У 1-го и 2-го вида при формировании ссылки с помощью выделения ячейки Excel создает
9. ЛК №2-3 Последовательность действий: в окне приложения Excel последовательно открыть документы ФПУ и 2-ой курс в
10. ЛК №2-3 Поясним это на примере: Пример. 1. Создано 100 листов в книге 2. Необходимо определить
11. ЛК №2-3 Многие циклические ссылки могут быть разрешены с помощью итерационных вычислений ( т.е. последовательным приближением
12. ЛК №2-3 Пример. В ячейку А1 введена формула =$B$1 Необходимо скопировать формулу, введенную в ячейку А1
13. ЛК №2-3 Скопируем ячейку С1 на диапазон D2:D4. В результате получим. 4.3. Смешанная ссылка содержит относительную
14. ЛК №2-3 5) Ввод данных в ячейку В ячейки рабочего листа можно вводить два типа данных:
15. ЛК №2-3 5.1. Числовые значения Они могут содержать только цифры от 0 до 9 и специальные
16. ЛК №2-3 если закончить ввод числа знаком %, то Excel применит к ячейке процентный формат если
17. ЛК №2-3 В ячейку можно вводить число содержащее до 1600 знаков, однако число отображаемых в ячейке
18. ЛК №2-3 5.2. Текстовые значения В общем случае ввод текста аналогичен вводу числовых значений. Отметим особенности
19. ЛК №2-3 В этом случае перенос текста будет осуществляться в указанном нами месте. 5.2.2 Числовой текст
20. ЛК №2-3 5.3. Значения дат и времени А) Даты Основной единицей измерения времени в Excel являются
21. ЛК №2-3 (12:00 ночи) и заданным временем, к общему времени (т.е. к 24 часам). Пример. 12
22. ЛК №2-3 текст выравнивается по левому краю ячейки числа, даты, время выравниваются по правому краю ячейки
23. ЛК №2-3 5.5. Объединение данных, расположенных в разных ячейках Текстовые и числовые значения, значения дат и
24. ЛК №2-3 а) числовые значения А3=123 А4=456 и А1=А3&А4 А1=123456 б) числовое значение и текст А5=АБСД
25. ЛК №2-3 6) Присваивание имен. Защита данных. 6.1 Ячейкам и диапазонам ячеек можно назначать имена и
26. ЛК №2-3
27. ЛК №2-3 В этом случае можно использовать текст в соседних ячейках для присвоения имен ячейкам или
28. ЛК №2-3 На любом листе набрав =В5*НДС получим =В5*0,25 Пример 3. Имена на уровне листа. В
29. ЛК №2-3 6.2. Защита данных Термин "защита данных" подразумевает защиту от случайных потерь или изменений, а
30. ЛК №2-3
31. ЛК №2-3 Защита файлов Два типа паролей: пароль для открытия файла пароль разрешения записи (только для
32. ЛК №2-3 5. Снова ввести пароль в окне диалога Подтверждение пароля и нажать кнопку ОК 6.
33. ЛК №2-3 Защита книги Защита листа
34. ЛК №2-3 структура --позволяет защитить структуру рабочей книги. Ее установка не позволит пользователю добавить или удалить,
36. Скачать презентацию

Слайд 2

ЛК №2-3
1) Возможности MS Excel
В качестве базового инструмента

организации, сбора , хранения, обработки и представления экономической информации нами будет использован MS Excel и Visual Basic for Application (VBA)
Мощные встроенные средства MS Excel позволяют создавать отчетную, финансовую и экономическую документацию, проводить обработку, анализ данных и принимать на этой основе оптимальные решения, т.е. решать достаточно широкий круг задач.

Слайд 3

ЛК №2-3

MS Excel - универсальное средство, обеспечивающее:
1. Широкий

набор инструментов по форматированию и представлению данных.
2. Решение математических задач, в т.ч. экономического характера, т.е. построение графиков, решение уравнений, работа с формулами массивов и т.д.
3. Финансовый анализ
4. .Статистический анализ. Для п.3,4 имеется большой спектр встроенных функций.
5. Поиск решения и решение оптимизационных задач.
6. Работу с табличными базами данных.
7. Работу с применением VBA.

Слайд 4

ЛК №2-3
2) Ячейки. Стили ссылок
Ячейка однозначно идентифицируется своим адресом,

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

Слайд 5

ЛК №2-3
Пример. Поясним вышесказанное и покажем как можно вводить

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

Слайд 6

ЛК №2-3
2. В режиме абсолютных ссылок.
В ячейку В10

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

Слайд 7

ЛК №2-3
3) Ссылки. Общие понятия
Ссылка является идентификатором ячейки или

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

Слайд 8

ЛК №2-3
У 1-го и 2-го вида при формировании ссылки

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

Слайд 9

ЛК №2-3
Последовательность действий:
в окне приложения Excel последовательно открыть документы

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

Слайд 10

ЛК №2-3
Поясним это на примере:
Пример. 1. Создано 100 листов

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

Слайд 11

ЛК №2-3
Многие циклические ссылки могут быть разрешены с помощью

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

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

Слайд 12

ЛК №2-3
Пример. В ячейку А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. В результате получим.
Т. о . мы рассмотрели ссылки, которые позволяют при копировании изменять исходные формулы.
Отличие перемещения от копирования.

Слайд 14

ЛК №2-3
5) Ввод данных в ячейку
В ячейки рабочего

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

Слайд 15

ЛК №2-3
5.1. Числовые значения
Они могут содержать только цифры от

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

Слайд 16

ЛК №2-3
если закончить ввод числа знаком %, то

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

Слайд 17

ЛК №2-3
В ячейку можно вводить число содержащее до 1600

знаков, однако число отображаемых в ячейке значащих цифр не более 15.
Excel сохраняет числа с точностью до 15 цифр и преобразует любые цифры после пятнадцатой в нули ( справедливо для целой части числа). В десятичной дроби отбрасываются все цифры после пятнадцатой. Кроме этого возможно округление отображаемых чисел с выводом символов #, указывающих, что данное число не помещается в ячейку.Количество выводимых цифр зави-сит от ширины столбца. Пример. Ширина столбца =8,43

Слайд 18

ЛК №2-3
5.2. Текстовые значения
В общем случае ввод текста аналогичен

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

Слайд 19

ЛК №2-3
В этом случае перенос текста будет осуществляться в

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

Слайд 20

ЛК №2-3
5.3. Значения дат и времени
А) Даты
Основной

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

Слайд 21

ЛК №2-3
(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

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

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

Слайд 23

ЛК №2-3
5.5. Объединение данных, расположенных в разных ячейках
Текстовые и

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

Слайд 24

ЛК №2-3
а) числовые значения
А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 А3=Текст0,66319
А1=21.01.2003 А2=Текст А3=А1&А2 А3=37642Текст
е) данные хранящиеся в трех и более ячейках
A2&A3&”Текст”&A4&”___”&B5

Слайд 25

ЛК №2-3
6) Присваивание имен. Защита данных.
6.1 Ячейкам и

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

Слайд 26

ЛК №2-3

Слайд 27

ЛК №2-3
В этом случае можно использовать текст в соседних

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

Слайд 28

ЛК №2-3
На любом листе набрав =В5НДС получим =В50,25 Пример

3. Имена на уровне листа.
В поле Имя окна диалога Присвоение имени введите Лист1!Расчет (действие этого имени только на Лист1)
Правила присвоения имен ячейкам и диапазонам:
имя должно начинаться с буквы, обр. косой черты(\) или символа подчеркивания ( _ )
имя должно содержать только буквы, цифры, обр. косую черту и символ подчеркивания
нельзя использовать имена, которые могут трактоваться как ссылки на ячейки
в качестве имен могут использоваться одиночные буквы за исключением R и С
заменяйте пробелы символами подчеркивания

Слайд 29

ЛК №2-3
6.2. Защита данных
Термин "защита данных" подразумевает защиту от

случайных потерь или изменений, а также ограничение доступа к данным.
Для пользователей Excel выделим две ступени защиты.
1. Защита файлов
2. Защита данных в Excel

Слайд 30

ЛК №2-3

Слайд 31

ЛК №2-3
Защита файлов
Два типа паролей:
пароль для открытия файла
пароль разрешения

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

Слайд 32

ЛК №2-3
5. Снова ввести пароль в окне диалога

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

Слайд 33

ЛК №2-3

Защита книги Защита листа

Слайд 34

ЛК №2-3
структура --позволяет защитить структуру рабочей книги. Ее

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