Табличный процессор MS EXCEL

Идея создания электронной таблицы возникла у студентов Гарвардского университета
Дэна Бриклина и Боба Франктсона в 1979 г.

VisiCalc – первая программа электронной таблицы, которая первоначально предназначалась для компьютеров типа Apple II, а позднее была трансформирована для компьютеров всех типов

Резкое повышение уровня продаж компьютеров типа Apple в то время связано именно с возможностью использования табличного процессора VisiCalc,
затем SuperCalc

1982г.
Появление электронной таблицы
Lotus 1-2-3
Первый табличный процессор, в состав которого дополнительно вошли:
- графика;
- возможность работы с СУБД

Lotus 1-2-3 был разработан для компьютеров типа IBM
После выпуска процессора объем продаж компании Lotus в 1-й год возрастает до $50 млн.

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

Рынок электронных таблиц
Quattro и VP Planner – компания Borland International
SuperCalc – фирма Computer Associates WingZ – компания Informix
Lotus 1-2-3 – фирма Lotus Development
Excel – компания Microsoft

Электронная таблица – компьютерный вариант обычной таблицы, в ячейках которой записаны данные различных типов:
текст, числа, даты, формулы

Табличный процессор – комплекс программ для управления электронной таблицей

Назначение:
обработка структурированной информации
- проведение расчетов
- визуальное представление данных и результатов их обработки

Рабочая область таблицы состоит из именованных ячеек
Адрес ячейки определяется названием (номером) столбца и номером строки
Ссылка – способ (формат) указания адреса ячейки

Основной объект Excel – рабочая книга - сохраняется как целостный объект в едином файле:
по умолчанию тип файла (2003г.)
*.XLS
- шаблон *.XLТ

Типы листов:
Рабочий лист
Лист диаграмм
Лист модуля

Новые форматы Excel 2007

Excel поддерживает стиль ссылок R1C1
Row – строка и Column - столбец
Адрес ячейки А1 в данном стиле ссылок отображается как R1C1
Адрес ячейки А2 – R2C1,
В3 – соответственно R3C2

Для переключения стиля ссылок выполняют:
Сервис, Параметры, Общие

Диапазон ячеек (Range) –
прямоугольная группа ячеек
Адресация диапазона:
А1:С7
К2:Z10
А:А
3:3

Выделение диапазона
CTRL+A – выделение рабочего листа
F8 – функция ВДЛ
Shift+F8 – функция ДОБ
Shift+пробел – выделение строки
Сtrl+пробел - выделение столбца

Полный формат адреса ячейки
[Имя_файла]<имя_листа>!<адрес_ячейки>
[Книга1.xls]Лист1!F10
[Table.xls]Январь!F10

Ячейкам (диапазонам) можно присваивать собственные имена:
Выделить ячейку и выполнить:
Вставка, Имя, Присвоить
Указать имя ячейки (диапазона), начинающееся с буквы

После ввода данных в ячейку следует нажать клавишу:
Enter – для перехода в текущем столбце на ячейку ниже
ТАВ – для перехода в следующую ячейку текущей строки

Или воспользоваться клавишами управления курсором
Или выполнить щелчок левой кнопкой мыши по любой ячейке

Типы данных Excel
Символьные (текстовые) – строка текста произвольной длины
Числовые – не могут содержать алфавитных или специальных символов, кроме знака числа и десятичной запятой

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

Для назначения формата числа выполняется команда:
Формат, Ячейки, Число

Числовой (Number)– обеспечивает представление чисел с заданной точностью, установленным пользователем количеством знаков после запятой

Процентный (Percentage) –
выводит число умноженной на 100 со знаком процента с заданной точностью

Денежный (Currency) – обеспечивает представление чисел с разделением разрядов с заданной точностью,добавляет знак денежной единицы

Финансовый (Accounting) – отличается от денежного тем, что число выравнивается по десятичной запятой

Экспоненциальный (Scientific) – число отображается в виде аEb
а – мантисса
b – порядок
Запись аEb используется в программировании для представления чисел с плавающей точкой вида а*10b
1,34*10-2 записывается 1,34Е-2

Дата (Date) – позволяет вывести дату в одном из 12 форматов
Дата вычисляется как порядковый номер числа в ряду дат от 1.01.1900

Текстовый (Text) – применение данного формата к числовому значению позволяет рассматривать как текст

Дополнительный (Special) – содержит 4 дополнительных формата

Все форматы (Custom) – позволяет создавать пользовательские форматы

ДДДД ДД ММММ ГГГГ
# ##0_рублей_.

Форматирование символьных данных в ячейках
Выравнивание
Форматирование шрифта

Форматирование данных – это выбор формы представления числовых или символьных данных в ячейке

Формулы Excel
Формула – это выражение, состоящее из операндов и арифметических операторов, расположенных в определенном порядке, начинающееся со знака « = »

Операнды:
- числа
- логические выражения
- ссылки на ячейки (диапазоны)
- встроенные функции

Арифметические операторы :
+ сложение
- вычитание
* умножение
/ деление
^ возведение в степень

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

Арифметические формулы могут содержать операторы сравнения:
= равно
> больше
< меньше
>= не менее <= не более
< > не равно

Логические формулы могут содержать операторы сравнения и специальные логические операторы:
NOT – логическое отрицание «НЕ»
AND - логическое «И»
OR – логическое «ИЛИ»

Логические формулы определяют, выражение истинно или ложно.
Истинным выражениям присваивается численная величина 1 («Истина»),
а ложным 0 («Ложь»)

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

Копирование формул
1. Выделить ячейку с формулой
2. Выполнить Правка, Копировать или CTRL+C, или нажать
кнопку
3. Выделить ячейку для вставки формулы
4. Выполнить Правка, Вставить
или CTRL+V, или нажать

Копирование с помощью маркера автозаполнения
Выделить ячейку с формулой
Навести указатель мыши на нижний правый угол выделенной ячейки, и при нажатой левой кнопке мыши протянуть вниз по столбцу

Маркер Автозаполнения

Типы ссылок в формулах MS Excel
Относительной ссылкой называется изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходные данные
А1, В4, С1:С15

При копировании формулы = А1 + В1
из С2 в D2
адреса А1 и В1 изменятся и формула будет
иметь вид: =В1+С1 (изменится адрес столбца),
при копировании формулы из С2 в С3,
формула примет вид: =А2+В2 (изменится адрес строки)

Абсолютной ссылкой называется не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходные данные

Для обозначения признака абсолютной ссылки в адресе используется знак $,
который можно ввести с клавиатуры или воспользоваться функциональной клавишей F4

Примеры абсолютных ссылок:
$D$1 - абсолютный адрес ячейки
D$1 - абсолютный адрес (номер) строки в адресе ячейки
$D1- абсолютный адрес столбца в адресе ячейки
$D$1:$D$15 - абсолютный адрес диапазона ячеек

При копировании формулы из С2 в D2 – адрес А1 изменится на В1, т.к. в формуле
ссылка А1 - относительная,
адрес В1 не меняется, т. к. в формуле ссылка $B$1 – абсолютная,
и формула будет иметь вид: =В1+$В$1,
при копировании формулы из С2 в С3,
формула примет вид: =А2+$В$1

Сообщения об ошибках
###### - выводится в случае, когда ячейка содержит число, дату или время, число знаков которого больше ширины ячейки, или в том случае, если ячейка содержит дату и/или формулу, которая выводит отрицательный результат

#ПУСТО! - задано пересечение двух областей, которые в действительности не имеют общих ячеек
# ЗНАЧ! – при вычислении ссылка на ячейку с текстом

#ДЕЛ/0! – в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение
#Н/Д – сокращение «Неопределенные Данные».
Предотвращает использование ссылки на пустую ячейку.

#ССЫЛКА! - используется недопустимая ссылка на ячейку
#ЧИСЛО! – некорректное использование чисел в формуле или функции
#ИМЯ? - Microsoft Excel не может распознать имя, используемое в формуле

Функции рабочего листа Excel – встроенный вычислительный инструмент, позволяющий производить сложные расчеты, предоставляет дополнительные возможности по созданию и манипулированию текстовыми строками

Excel содержит более 300 встроенных функций:
математические,
статистические,
набор функций для управления БД,
функции даты и времени,
логические,
текстовые и др.

Функция вводится в активную ячейку
Все функции имеют одинаковый формат записи:
Имя_функции(аргумент)

Функцию в ячейку
можно ввести выполнив:
Вставка, Функция
Или использовать кнопку панели инструментов

В окне Мастер функций укажите категорию и выберите функцию
В окне диалога функции аргументы

Функции делятся :
- Без аргументов,
например, ПИ(), Сегодня()
- С одним аргументом,
SIN(число); ABS (число); ЕХР(число)

- С фиксированным числом аргументов
Если(лог_выражение;истина;ложь)
Мумнож(массив1; массив2)
Сумм(значение1,… значение30)
Макс(значение1 ,… значение30)

- С необязательными аргументами
Кпер(норма;выплата;НЗ;БС;Тип)
Линейн(Изв_знач_y; Изв_знач_х; Константа; Стат)

Для ручного ввода функции непосредственно в ячейке:
ввести в ячейке знак =
ввести с клавиатуры имя функции
открыть скобку
ввести аргументы функции
закрыть скобку

Формула, использующая любую функцию содержит
1. знак =
2. имя функции
3. один или несколько аргументов – чисел или ссылок на ячейки, которые перечисляются через точку с запятой.

Например,
=Сумм(число1; … ;число30)
=Сумм(А1:А15) –аргумент – ссылка на диапазон
=Сумм(А1:А15;А45:А30) – аргумент является ссылкой на 2 диапазона
=Сумм(А1:А15;А45;30) – аргумент – ссылка на диапазон, ячейку, число

Аргументы функции могут быть:
- числового типа,
- даты,
- время,
- текст, - массивы и др.

Например,
=срзнач(250;300;366)
=макс(А10:В15)
=sin(2*A2)+cos(пи())
= Год(Сегодня()-Год(А1))
=Римское (15)

Логические функции

Функция Если()
Возвращает одно из двух значений:
- ИСТИНА, если заданное условие при вычислении дает верное значение
ЛОЖЬ, если другое значение
Используется при проверке условий для значений и формул.

Синтаксис функции
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

Например,
A10=100 – логическое выражение
если в ячейке A10 хранится число 100, то выражение принимает значение ИСТИНА.
В противном случае — ЛОЖЬ.

До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования более сложных проверок

Пример:
Начислить премию
Работникам 1-го разряда - 900р.

Пример:
Начислить премию
Работникам 1-го разряда - 900р.
Работникам 2-го разряда- 500р.
Работникам 3-го разряда- 300р.

=ЕСЛИ(B2=1;900;ЕСЛИ(B2=2;500;300))

ФУНКЦИЯ И()
Синтаксис:
И(логическое_значение1; логическое_значение2;… логическое_значение30)
от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Аргументы должны быть:
логическими значениями (ИСТИНА или ЛОЖЬ),
массивами или ссылками, содержащими логические значения.

Пример:
Начислить премию
Работникам 1-го разряда - 900р.
Работникам 2-го разряда- 900р.
Работникам 3-го разряда- 500р.

=ЕСЛИ(И(B2=1; B2=2); 900; 500)

Если аргумент в виде ссылки на ячейку или массив, содержит текст или пустые ячейки, то такие значения игнорируются.
Если указанный интервал не содержит логических значений, то И() возвращает значение ошибки #ЗНАЧ!

ФУНКЦИЯ ИЛИ()
Синтаксис:
ИЛИ(логическое_значение1;…
логическое_значение30)
от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ

Аргументы должны принимать логические значения (ИСТИНА или ЛОЖЬ) или быть массивами или ссылками, содержащими логические значения.

Если аргумент, который является ссылкой или массивом, содержит текст или пустые ячейки, то такие значения игнорируются.
Если заданный интервал не содержит логических значений, то функция ИЛИ возвращает значение ошибки #ЗНАЧ!.

Дополнительные функции Excel для анализа данных с использованием условий
СЧЁТЕСЛИ() - считает количество ячеек, с учетом заданного критерия
СУММЕСЛИ() - суммирует ячейки, заданные критерием