Слайд 1ЭТ Excel
Ячейки
Данные
Формулы
Слайд 2Первоначальная цель разработки электронных таблиц (ЭТ) – автоматизация бухгалтерской деятельности.
В настоящее время ЭТ позволяют решать задачи, связанные с
инженерно-техническими и
научными расчетами;
моделированием и прогнозированием;
методами оптимизации;
анализом рынков, ценных бумаг, …
Слайд 3Рабочее пространство ЭТ Excel
Стандартные элементы окна W-приложений:
строка заголовка;
строка меню;
адресная
строка;
панели инструментов;
строка состояния.
Специфические элементы окна ЭТ Excel:
окно рабочей книги;
строка формул;
поле
имени.
Слайд 4Рабочая книга
Рабочая книга отождествляется с файлом (имеет имя), служит для
хранения информации.
РК состоит из 256 листов. Рабочий лист представляет собой
сетку из столбцов и строк. Элемент находящийся на пересечении столбца и строки назовем - ячейкой (клеткой). Каждая ячейка образуется пересечением строки и столбца и имеет свой уникальный адрес (ссылку).
Размер рабочего листа:
16384 столбца х 1048576 строк (2007-2010)
Слайд 5МОДЕЛЬ ЯЧЕЙКИ В EXCEL
Каждая ячейка таблицы имеет следующие характеристики:
Адрес
Содержимое (в строке
формул)
Изображение (в ячейке)
Формат
Имя (в поле имени)
Примечание
Слайд 6Адрес ячейки - номер столбца и строки. Excel позволяет использовать 2
стиля ссылок: стиль А1 и стиль R1C1.
«Cтиль R1C» -
альтернативная система адресации. В этой системе и строки и столбцы обозначаются цифрами. Адрес ячейки B3 в такой системе будет выглядеть как R3C2 (R=row=строка, C=column=столбец).
Выбор стиля ссылок:
Офис (Файл) - Параметры Excel - Формулы
Слайд 7Выделение ячеек и интервалов
Интервал – это группа ячеек, образующих
прямоугольную область.
Интервал имеет адрес: A1:B20, C2:C5, E3:M3.
Множественный интервал -
произвольная область рабочего листа, включающую несколько интервалов. Выделяется в следующем порядке:
первоначально выделяется любой из интервалов;
нажимается и удерживается клавиша CTRL, или включается режим добавления – SHIFT+F8;
Выделяются все остальные интервалы;
Выключается клавиша CTRL, или, соответственно режим добавления клавишей ESC.
Слайд 8Адреса
адрес активной ячейки
ячейка B2
диапазон B2:С7
Ссылки в формулах:
=B2+2*C3 =A2+2*СУММ(B2:C7)
B2
С7
Слайд 9Выделить строку – щелкнуть по индикатору (по номеру) строки
Выделить столбец
– щелкнуть по индикатору (по букве) столбца
Выделить лист – щелкнуть
по индикатору в верхнем левом углу (пересечение индикаторов строк и столбцов)
Слайд 10Перемещение по рабочему листу
1. Стандартные средства W-приложений:
линейки прокрутки;
клавиши перевода
курсора.
2. Команда системного меню:
- Главная. Найти и выделить. (Exc
2007)
3. На строке формул в списке имен ячеек адрес ячейки.
Слайд 11Ввод данных в ЭТ.
Тип данных характеризует множество данных
и
определяет набор операций, которые можно производить над этими данными.
Основные типы
данных MS Excel:
- константы:
- формулы.
Константы делятся на:
числовые; главные
текстовые; категории
значения даты и времени суток; констант
логические; константы
значения ошибок. специального вида
Слайд 12Ввод числовых значений
Числовые значения – это данные, состоящие из
цифр от 0 до 10 и некоторых специальных символов +
- Е е ( ) , . % / $ пробел
Десятичное число (с фиксированной точкой) имеет целую и дробную части, которые разделяются запятой.
Число с плавающей точкой имеет мантиссу и порядок.
Слайд 13При вводе десятичных чисел целая и дробная части разделяются ,
Символы
Е(е) используются для ввода очень маленьких или больших чисел: 4,3е-5
Пробел
используется для 1) разделения разрядов 1 000 000; 2) для ввода обыкновенных дробей 2 ½
При вводе текста, состоящего из цифр перед числом добавляется апостроф ‘123
Слайд 15Ввод формул
Любая формула в ЭТ Exсel начинается со знака =
:
=10+5
При создании формул приняты стандартные правила вычисления выражений и приоритетов
операций.
Если формула содержит адреса ячеек, то результат вычислений зависит от содержимого этих ячеек.
Слайд 16Формула в Excel - последовательность символов, включающая константы, адреса ячеек (ссылки
на ячейки), знаки операций, имена, функции
Относительная ссылка указывает на ячейку,
согласно ее положения относительно ячейки, содержащей формулу. Обозначение относительной ячейки - А1.
Абсолютная ссылка указывает на ячейку, местоположение которой неизменно. Обозначение абсолютной ячейки - $A$1.
Смешанная ссылка содержит комбинацию относительной и абсолютной ссылок - $A1, A$1.
Для быстрого изменения типа ссылки используется клавиша F4.
Слайд 17Типы ссылок
относительные (меняются так же, как и адрес формулы )
формула
«переехала» на один столбец вправо и на одну строку вниз;
абсолютные
(не
меняются)
смешанные
(меняется только относительная часть)
имя столбца на 1
номер строки на 1
Слайд 19Форматирование ячеек РЛ
Параметры форматирования для ячеек РЛ устанавливаются командами
системного меню: Главная.
Область действия команды – выделенный интервал.
Параметры устанавливаются
на вкладках диалогового окна (ДО):
Число. Позволяет представить числа в различных форматах.
Выравнивание. Дает возможность определенным образом располагать данные в ячейках РЛ. В том числе:
!!! а) объединение ячеек;
б) создание многострочного текста;
в) центрирование по выделенному.
Шрифт.
Граница. Используется для оформления данных в виде таблиц
Вид. Управляет цветом и штриховкой выделенных ячеек.
Защита.
Слайд 20Управление шириной столбца и высотой строки
Манипуляции указателем мыши.
Команды системного
меню:
Главная. Ячейки. Формат. Размер ячейки
!!! Команда Видимость позволяет скрывать
столбцы (строки) на РЛ.
Для восстановления скрытых столбцов (строк) необходимо правильно произвести их выделение.
Слайд 21Оформление ячеек
все свойства
размер
направление
в несколько
строк
денежный
формат
количество знаков
в дробной части
Слайд 22Редактирование рабочего листа ЭТ Excel.
1. Очистка ячеек.
Выполняется с помощью
команды системного меню:
Главная. Редактирование. Очистить. (Exc 2007).
Возможности команды:
Все.
Форматы.
Устанавливается формат Основной, стиль Обычный).
Содержимое. (Сохраняются все связанные с ячейкой форматы).
Примечания.
2. Вставка
Позволяет вставить новые ячейки в любое место РЛ.
Выполняется с помощью команды системного меню:
Главная. Ячейки. Вставить. (Exc 2007).
А) Вставка строк (столбцов).
Выделить строку снизу или столбец слева и выполнить вставку.
Б) Вставка ячеек.
Выполняется с помощью команды вставки, но в ДО необходимо указать, куда нужно сдвинуть уже имеющиеся ячейки:
• со сдвигом ячеек вправо;
• со сдвигом ячеек вниз.
Слайд 233. Удаление ячеек.
Выполняется с помощью команды:
-Главная. Ячейки. Удалить. (Exc
2007).
А) Удаление целых строк и столбцов.
Для этого выделяют лишние строки
(столбцы) и выполняют указанную команду.
!!! Если на РЛ имеются формулы, ссылающиеся на удаляемые ячейки, Exc эти формулы пересчитывает.
!!! Нельзя одной командой удалять несмежные строки и столбцы
Б) Удаление ячеек.
Выполняется с помощью той же команды , но в ДО необходимо указать, каким образом заполнить освободившиеся ячейки:
• со сдвигом ячеек влево;
• со сдвигом ячеек вверх.
Слайд 24Операции со строками и столбцами
размеры
высота строк
ширина
столбцов
добавление, удаление
ПКМ
Слайд 25Специальные средства редактирования рабочего листа ЭТ Excel
Создание рядов.
Ряд
- упорядоченная последовательность из чисел, текста, чисел и текста, даты
и времени.
Создавать ряды можно, используя:
средства быстрого редактирования
команду системного меню
Слайд 26I. Создание рядов с помощью маркера заполнения (МЗ)(Автозаполнение).
Позволяет создавать
только арифметическую прогрессию.
Порядок действий:
Указать в ячейках два первых значения ряда.
Выделить
обе ячейки.
Активизировать МЗ и протянуть его вниз или вправо.
Арифметическая прогрессия с шагом = 1:
Указать в ячейках первое значение ряда.
Выделить ячейку.
Активизировать МЗ и протянуть его вниз или вправо при нажатой клавише Сtrl.
Ввод одинаковых значений в соседние ячейки
Слайд 27Маркер заполнения - контрольная точка в правом нижнем углу выделенной
ячейки. При наведении на МЗ указатель мыши приобретает форму тонкого
черного крестика.
МЗ можно включать или отключать:
Office(Файл)-Параметры-Дополнительно: флажок Разрешить маркеры заполнения и перетаскивание ячеек (Excel 2007-2010)
Функция Автозаполнение позволяет заполнять данными область ячеек по определенным правилам.
Слайд 28Заполнение рядов
арифметическая прогрессия
маркер заполнения
копирование формул
ЛКМ
даты
списки
время
ЛКМ
Слайд 29II. Создание рядов с помощью системного меню.
Дает возможность создавать:
арифметическую и геометрическую прогрессии;
автоматически определяет количество членов ряда.
Выполняется с
помощью команды системного меню:
Главная. Редактирование. Заполнить. Прогрессия. (Exc 2007).
Слайд 30Порядок действий.
А) Известно заранее число членов ряда.
Ввести в ячейку
начальное значение ряда.
Выделить интервал по числу членов ряда.
Выполнить команду .
Установить
параметры ряда.
Б) Заранее число членов ряда неизвестно или ряд достаточно велик.
Установить параметры ряда, в том числе предельное значение ряда.
Слайд 31Создание пользовательских списков.
Использование списков.
Если список существует, то:
На РЛ вводят
значение из списка;
Протягивают МЗ по всему интервалу.
Создание списков
1. Офис-Параметры
Excel-Основные- Изменить список (Exc 2007)
Файл-Параметры-Дополнительно- Изменить список (Exc 2010)
2. В окне Списки выбрать пункт Новый список.
3. В окне Элементы списка напечатать содержимое списка столбиком (исп. ENTER).
4. Кнопка Добавить. ОК.
Слайд 32Специальная вставка.
Порядок действий.
1. Выделяется исходный интервал.
2. Главная. Копировать. (Exc
2007).
3. Указывается единственная угловая ячейка нового интервала или весь
интервал.
4. Главная. Вставить. Специальная вставка (Exc 2007).
Слайд 33Разделы ДО Специальная вставка.
1. Раздел Вставить.
Определяет режимы вставки выделенного
интервала: все, формулы, значения, форматы…
2. Раздел Операция.
Выполняет действия над двумя
интервалами, а результат записывает в один из исходных.
При вычитании копируется вычитаемый интервал.
При делении копируется интервал-делитель.
3. Флажки
Транспонировать.
Изменяет ориентацию таблицы на РЛ.
Пропускать пустые ячейки.
Позволяет при вставке выделенного интервала игнорировать содержащиеся в нем пустые ячейки.
Слайд 35Функции Excel
Функция Excel - это заранее определенная формула, которая по одному
или нескольким аргументам возвращает результат – одно или несколько значений.
Пример:
=А1+А2+…+А10 =СУММ(А1:А10)
Стандартный формат записи функций
= имя функции (список аргументов)
В качестве аргументов функций можно использовать
Константы (числовые, текстовые, логические, дата и время)
ссылки на ячейки и диапазоны ячеек,
имена,
математические выражения,
массивы значений,
другие функции
Слайд 36Аргументы отделяются друг от друга точкой с запятой.
Список аргументов функции
может быть пуст, т.е. функция может не иметь аргументов.
Примеры
таких функций:
=ПИ( ) - возвращает число π с точностью до 15 цифр,
=СЕГОДНЯ ( ) - возвращает текущую дату
=СЛЧИС( ) - возвращает случайное число в
диапазоне от 0 до 1.
Даже если у функции нет аргументов, наличие скобок после имени функции обязательно.
Слайд 37Функции
ввод в ячейке
ввод в строке редактирования
диапазон
ячейка
мастер функций
Слайд 38Ввод функций на рабочий лист
Вводить функцию можно:
1. Печатая имя
и аргументы непосредственно на клавиатуре.
2. Используя команду системного меню
-
Формулы. Вставить функцию. (Exc 2007).
Далее открываются Диалоговые окна программы Мастера функций (МФ).
Слайд 391-е Диалоговое окно:
Определяет выбор категории функций.
Если категорию определить затрудни-тельно,
можно выбрать категорию Все.
В категорию 10 недавно использовавшихся входят функции,
находящиеся в работе.
Далее в алфавитном списке выбирают по имени функцию.
Слайд 402-е Диалоговое окно:
В полях ввода ДО необходимо ввести аргументы. Если
аргументом является интервал, его можно выделить при открытом ДО, если
аргумент функция – снова вызов МФ
Слайд 41Математические функции
Позволяют выполнять разнообразные математические расчеты.
1. Суммирование =СУММ
(числа)
числа – список из не более, чем 30-ти аргументов,
каждый из которых число, формула или ссылка на ячейку, содержащую числовое значение.
Кнопка на ПИ – Σ – Автосуммирование позволяет упростить выполнение суммирования.
2. Произведение =ПРОИЗВЕД(число1;число2...) перемножает все числа, задаваемые ее аргументами. Может иметь до 30 аргументов. Excel игнорирует любые пустые ячейки, текстовые и логические значения
Слайд 423. Тригонометрические функции: sin(число), cos(число), atan(число),…
Число – угол в радианах
Если
угол задан в градусах, то его преобразуют в радианы двумя
способами:
А) число = угол * ПИ()/180
Б) число = РАДИАНЫ(угол)
4. Абсолютное значение =ABS(число) – возвращает абсолютное значение числового (скалярного) аргумента.
Слайд 435. Округление
ЦЕЛОЕ – округляет до ближайшего меньшего целое
ОКРУГЛ –
округляет число по правилам.
ОКРУГЛВВЕРХ – округляет всегда с избытком
ОКРУГЛВНИЗ –
округляет с недостатком.
=ОКРУГЛ(число; десятичные знаки)
число – округляемое значение;
десятичные знаки – целое число определяет разряд округления. Может быть:
>0 – округление происходит в дробной части числа;
=0 - число округляется до целого;
<0 - округление происходит в целой части числа.
Пример: =ОКРУГЛ(32,675;1) = 32,7
=ОКРУГЛ(32,675;-1) = 30
Слайд 44
6. Остаток от деления =ОСТАТ(число;делитель)
Если число точно делится на делитель,
функция возвращает 0. Если делитель равен 0, функция ОСТАТ возвращает
ошибочное значение.
Пример: =ОСТАТ(33;4)= 1
7. Корень =КОРЕНЬ(число)
Аргумент число должен быть положительным числом.
Если число отрицательное, КОРЕНЬ возвращает ошибочное значение.
8. Степень =СТЕПЕНЬ(число;степень)
Слайд 456. СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Диапазон - содержит числа, имена, массивы или ссылки
на числа. Пустые ячейки и ячейки, содержащие текстовые значения, пропускаются.
Условие -
в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Например, 32, ">32", B5, "32", "яблоки" или СЕГОДНЯ().
Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки ("). Можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" (~).
Диапазон_суммирования Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон.
Слайд 47Статистические функции
Используются для обработки данных.
1. =СРЗНАЧ(числа) - вычисляет среднее
арифметическое для последовательности чисел. Игнорирует пустые, логические, текстовые ячейки. Может
содержать до 30-ти аргументов.
2. =МАКС(числа) и =МИН(числа) соответственно возвращает максимальное и минимальное значения для заданной последовательности чисел.
Слайд 483. =МОДА(числа) – возвращает наиболее часто встречающееся значение во множестве
чисел.
4. =СЧЕТ(интервал) и =СЧЕТЗ(интервал)
СЧЕТ вычисляет в заданном интервале
количество ячеек, содержащих числа, даты, формулы.
СЧЕТЗ вычисляет количество заполненных ячеек.
5. =СЧЕТЕСЛИ(интервал; критерий) подсчитывает количество ячеек, содержимое которых удовлетворяет критерию поиска.
=СЧЁТЕСЛИ(A1:A8;">5")
Слайд 49 =СЧЕТЕСЛИ(интервал; критерий) подсчитывает количество ячеек, содержимое которых удовлетворяет критерию
поиска.
Слайд 50Логические функции
1. ЕСЛИ
Используется, когда, в зависимости от значения
логического выражения, выполняются те или иные вычисления.
=ЕСЛИ (логическое выражение;
значение «истина»; значение «ложь»)
Использование функции ЕСЛИ наделяет формулу способностью “принимать решения”
Слайд 51Логические выражения используются для записи условий, в которых сравниваются числа,
функции, формулы, текстовые или логические значения. логическое выражение должно содержать
хотя бы один оператор сравнения
Операторы сравнения: =, >, <, >=, <=, <> (не равно).
Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
Второй и третий аргумент функции Если: константы, выражения, функции.
Слайд 522. И, ИЛИ, НЕ – аргументами функций являются логические выражения;
функции возвращают значения логических констант: ИСТИНА и ЛОЖЬ.
И, ИЛИ,
НЕ используются для построения сложных логических выражений.
Логическую функцию И называют конъюнкцией (лат. – объединение), логическую функцию ИЛИ – дизъюнкцией (лат. – разъединение, разница), а логическую функцию НЕ – отрицанием
Слайд 54Вложенные функции ЕСЛИ – при использовании сложных условий. Число вложенных функций ЕСЛИ
не должно превышать семи.
Пример: функция задана таким образом:
2х – 5, если х < –2 или х > 10,
y = 3х + 1, если 2 х 3,
не существует при других значениях х
и значение х находится в ячейке А5, то вычислить ее значение можно по такой формуле:
=ЕСЛИ (ИЛИ(A5<–2;A5>10); 2*A5–5; ЕСЛИ(И(A5>=2; A5<=3); 3*A5+1; "функция не определена")).
Слайд 55Функции даты и времени.
Основная единица времени в ЭТ Excel
– 1 день.
Каждый день имеет порядковый номер от 1 (1
января 1900) до ...
Excel хранит дату в виде порядкового номера.
Пример: Дата создания документа – 11.11.2009.
Этой дате соответствует порядковый номер – 40128.
Слайд 56Время суток хранится в виде числа – десятичной дроби. Дробь
равна доле суток, прошедшей от их начала до данного момента
(доля от 1).
Полдень – 12:00 соответствует 0,5.
8:00 соответствует 0,333333.
10:35 соответствует 0,44097
Такой способ хранения даты и времени дает возможность проводить вычисления с датами и временем и использовать их в формулах как обычные числа.
Слайд 57Арифметические действия с датой и временем.
1. Определение интервала времени
между двумя датами.
="01.01.2018"-"04.05.2017"
2. Определение даты, отстоящей от заданной на данное
количество дней.
3. Определение числа недель между двумя датами.
4. Аналогичные действия со временем.
Слайд 58Функции
1. Дата(год; месяц; число)
Аргументами могут быть результаты каких-либо расчетов,
поэтому аргументы месяц и число могут иметь значения, выходящие за
обычные пределы.
2. Сегодня() возвращает текущую дату.
Функция не имеет аргументов; используется в расчетах, привязанных к текущей дате.
3. ТДАТА() возвращает текущую дату и время.
4. ДЕНЬНЕД(дата;тип) используется для вычисления порядкового номера дня недели
Слайд 595. РАЗНДАТ
вычисляет разность между двумя датами. Этого оператора нет в
перечне формул Мастера функций,, его значения приходится вводить вручную:
=РАЗНДАТ(нач_дата;кон_дата;единица)
качестве
аргументов «Начальная дата» и «Конечная дата» выступают даты, разницу между которыми нужно вычислить.
В качестве аргумента «Единица» выступает конкретная единица измерения этой разности:
Год (y);
Месяц (m);
День (d);
Разница в месяцах (YM);
Разница в днях без учета годов (YD);
Разница в днях без учета месяцев и годов (MD).
Слайд 60Функция ГОД - Возвращает год как целое число (от 1900
до 9999), который соответствует заданной дате. В структуре функции только
один аргумент – дата в числовом формате.
Функция МЕСЯЦ
Функции ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ
По умолчанию функция считает воскресенье первым днем недели
неделя начинается с понедельника (второй день недели).
Слайд 61ТЕКСТОВЫЕ ФУНКЦИИ
ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков
с начала ячейки;
ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество
знаков с конца ячейки;
ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо
ДЛСТР(текст) – возвращает количество знаков в текстовой строке
=ДЛСТР("задача") = 6
Слайд 62Для объединения значений из нескольких ячеек в одну строку используется
оператор амперсанд (&) или функция СЦЕПИТЬ.
Получаем в одной ячейке объединенные
значения:
![6. СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])Диапазон - содержит числа, имена, массивы или ссылки на числа. Пустые ячейки и ячейки, содержащие](/img/tmb/7/634749/0a30b2ccd22dbc249a3b6dd2f989a18c-800x.jpg "ЭТ Excel 6. СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])Диапазон - содержит числа, имена, массивы или ссылки на")
