- •Общие сведения о табличном процессоре Excel
- •Рабочая книга Excel и ее элементы
- •Понятие объекта, свойства, методы и события
- •Элементы окна приложения Excel
- •Окно документа
- •Настройка Excel
- •Настройка интерфейса Excel
- •Настройка параметров Excel
- •Создание новой рабочей книги
- •Открытие существующей рабочей книги
- •Сохранение рабочей книги
- •Удаление файла рабочей книги
- •Операции над рабочими листами
- •Связывание рабочих листов таблицы
- •Прямое связывание
- •Операции с элементами рабочего листа
- •Ввод данных и формул в ячейки рабочего листа
- •Выражения и операции Excel
- •Типы данных Excel
- •Ввод данных
- •Ввод формул
- •Организация ссылок
- •Ввод специальных данных
- •Автоматическое вычисление сумм
- •Ввод в ячейки таблицы текущих значений даты и времени
- •Режимы вычислений
- •Выбор ручного или автоматического режима вычислений
- •Вычисления с точностью как на экране
- •Сортировка данных таблицы
- •Корректировка табличных документов
- •Перемещение по табличному документу
- •Выделение фрагментов таблицы
- •Редактирование данных
- •Копирование данных и формул
- •Автозаполнение
- •Оформление табличного документа
- •Изменение ширины столбцов
- •Операции над длинным текстом ячейки
- •Изменение высоты строки
- •Выбор шрифта
- •Ввод затенений и цветов
- •Изменение вида выравнивания по горизонтали
- •Изменение расположения текста относительно вертикали
- •Изменение ориентации вывода текста
- •Подготовка табличного документа к печати
- •Предварительный просмотр таблицы перед печатью
- •Печать
- •Работа с диаграммами
- •Создание диаграмм
- •Объекты диаграммы
- •Форматирование объектов
- •Изменение типа диаграммы
- •Добавление к диаграмме новых данных
- •Справочная система MS Excel
- •Использование встроенных функций Excel
- •Суммирование ячеек, удовлетворяющих определенному критерию
- •Подсчет количества значений в диапазоне
- •Подсчет количества пустых ячеек в диапазоне
- •Расчет среднего значения
- •Определение максимального значения
- •Определение минимального значения
- •Генерация случайного числа
- •Функции прогнозирования
- •Определение ранга числа
- •Определение процентной нормы числа
- •Функции для работы с матрицами
- •Функции даты и времени
- •Функция текущей даты
- •Функция текущей даты и времени
- •Функция определения дня недели
- •Функция определения номера месяца
- •Функция определения количества дней между двумя датами
- •Функция проверки условия
- •Использование функции И / ИЛИ
- •Использование функции И
- •Использование функции ИЛИ
- •Функция поиска данных в некотором диапазоне
- •Оценка ежемесячных выплат
- •Работа с именами ячеек
- •Создание имен
- •Замена адресов ячеек их именами
- •Вставка имен в формулы
- •Использование примечаний
- •Подбор параметра
- •Таблицы автоматической подстановки данных
- •Поиск решения
- •Консолидация данных
- •Консолидация по расположению
- •Консолидация по категориям
- •Удаление результатов ошибочной консолидации
- •Одновременная работа с несколькими табличными документами
- •Обмен данными через буфер
- •Динамический обмен данными DDE
- •Связь и внедрение объектов OLE (Active-X)
- •Общие принципы работы с большими таблицами
- •Фиксация на экране титулов
- •Создание нескольких окон для одного табличного документа
- •Разбиение документа на страницы вручную
- •Печать повторяющихся заголовков
- •Работа со списками (базами данных)
- •Создание списка (базы данных)
- •Поиск записей в списке
- •Редактирование записей с помощью формы
- •Удаление записей с помощью формы
- •Добавление записей
- •Подведение промежуточных итогов в списке
- •Фильтрация списка
- •Выбор элементов списка с помощью автофильтра
- •Функции для работы со списками
- •Сводные таблицы
- •Импорт данных в список Excel из внешних источников
Клавишей Tab, которая последовательно активизирует ячейки одной строки;
Клавишей Enter, которая последовательно активизирует ячейки одной колонки; Клавишами управления курсором, последовательно активизирующими
ячейки в направлении стрелок, изображенных на них;
Комбинацией клавиш <Ctrl> + <Home> активизируется первая ячейка рабочего листа;
Комбинацией клавиш <Ctrl> + <End> активизируется последняя ячейка рабочего листа;
Клавишей <Home> активизируется первая ячейка текущей строки; Комбинацией клавиш <Ctrl> + клавиша управления курсором активизируется первая или последняя ячейка рабочего листа в текущей строке или столбце; Щелчком мыши на выбранной ячейке.
Выделить диапазон можно следующими способами:
выделить одну из угловых ячеек диапазона и удерживая клавишу мыши перевести указатель до противоположного угла;
выделить одну из угловых ячеек диапазона и удерживая клавишу <Shift> клавишами управления курсором указать область диапазона.
7. Ввод данных и формул в ячейки рабочего листа
7.1. Выражения и операции Excel
Excel является универсальным вычислителем, выполняющим операции над данными и использующим принцип программного управления. Для записи программы вычислений служат выражения.
Выражение – это указание на выполнение какого-либо действия. Выражения в Excel служат для записи формул, для записи условий фильтрации. Выражение включает хотя бы один из таких элементов, как оператор, литерал, адресную ссылку, идентификатор или функцию. Литералы, идентификаторы, адресные ссылки и функции, соединенные операторами, называются операндами.
Оператор в выражении указывает, какое действие должно быть выполнено над операндами. Различают арифметические операторы, операторы сравнения, операторы фильтрации, операторы конкатенации, операторы идентификации и адресные операторы. Перечень операторов и их назначение приведен в следующей таблице.
Литералы – это вводимые пользователем значения констант в явном представлении.
Примеры литералов: числовой литерал -256, текстовый литерал - “Кредит”.
16
|
|
Таблица I.2. Операторы Excel |
|
|
|
Оператор |
Выполняемое действие |
Пример |
|
Арифметические операторы |
|
+ (плюс) |
Арифметическое сложение |
12+24 результат 36 |
- (минус) |
Арифметическое вычитание |
36-12 результат 24 |
|
Унарный минус (изменение знака числа) |
|
* (звездочка) |
Арифметическое умножение |
13*3 результат 39 |
/ (слэш) |
Арифметическое деление |
39/13 результат 3 |
^ (крышка) |
Возведение в степень |
9^2 результат 81 |
|
|
9^(1/2) результат 3 |
% (процент) |
процент |
|
Операторы сравнения
Применяются в операциях сравнения двух числовых значений. Результатом операции сравнения являются значения ИСТИНА (True) или ЛОЖНО (False)
= |
равно |
|
|
|
10 = 10 результат True |
|
|
|
|
|
|
10 = 12 результат False |
|
> |
больше |
|
|
|
20>19 результат True |
|
|
|
|
|
|
19>20 результат False |
|
< |
меньше |
|
|
|
15<20 результат True |
|
|
|
|
|
|
20<15 результат False |
|
>= |
Больше или равно |
|
12>=12 результат True |
|
||
|
|
|
|
|
11>= 12 результат False |
|
<= |
Меньше или равно |
|
12<=12 результат True |
|
||
|
|
|
|
|
12<= 11 результат False |
|
<> |
Не равно |
|
|
|
22<> 21 результат True |
|
|
|
|
|
|
22<>22 результат False |
|
|
|
|
Оператор конкатенации (объединения) |
|
||
& (амперсанд) |
Служит |
для |
объединения |
последовательности |
“Иванов” & “Иван” =”Иванов Иван” |
|
|
символьных литералов в один литерал |
|
|
|||
|
|
|
Адресные операторы |
|
|
|
: (двоеточие) |
Оператор диапазона, который ссылается на все |
B10:C11 |
|
|||
|
ячейки между границами диапазона включительно. |
B5:B15 |
|
|||
, (запятая) |
Оператор объединения, который ссылается на |
СУММ(A5:A10, B5:B10) |
|
|||
|
объединение ячеек диапазонов. |
|
Суммируются значения в |
ячейках |
||
|
|
|
|
|
диапазона А5:B10 |
|
(пробел) |
Оператор пересечения, который ссылается на общие |
СУММ(C5:C10 A6:D7) |
|
|||
|
ячейки диапазонов. |
|
В примере ячейки C6 и С7 являются |
|||
|
|
|
|
|
общими для двух диапазонов. Результат |
|
|
|
|
|
|
будет равен сумме значений в |
ячейках |
|
|
|
|
|
С6 и С7 |
|
|
|
|
Операторы идентификации |
|
|
|
! |
Указывает |
на |
принадлежность |
адреса к объекту |
=Лист1!A1*Лист2!B2 |
|
(восклицательный |
(рабочему листу) |
|
Перемножается значение ячейки А1 |
|||
знак) |
|
|
|
|
рабочего листа с именем Лист1 на |
|
|
|
|
|
|
значение в ячейке В2 рабочего листа с |
|
|
|
|
|
|
именем Лист2 |
|
Операторы фильтрации
Применяются для соединения условий с операторами сравнения. Результатом операции являются значения ИСТИНА |
||
|
(True) или ЛОЖНО (False) |
|
ИЛИ |
Логическое сложение |
|
И |
Логическое умножение |
|
Идентификаторы- имена объектов Excel (ячеек, диапазонов, рабочих листов, функций). Идентификаторы могут включать любые латинские символы, символы кириллицы и цифры. Идентификатор должен начинаться с буквы, либо с символа подчеркивания. Внутри идентификатора не должно быть разделительных символов (точки, восклицательного знака, пробела и др.). Идентификатор, создаваемый пользователем, должен быть уникальным в пределах объектов одного класса. Например, нельзя двум рабочим листам в одной рабочей книге присваивать
17
одинаковое имя, также нельзя присвоить одинаковые имена двум различным диапазонам ячеек.
Примеры правильных идентификаторов:
ДатаВыдачи, Дата_выдачи1, _1Список, РабочийЛист1
Примеры неверных идентификаторов:
1Список, Дата.Выдачи, Дата выдачи, 1Рабочий Лист
7.2. Типы данных Excel
Тип данных определяет формат данных хранимых в памяти и способ их отображения в ячейках рабочего листа. Тип данных для активной ячейки или выделенного фрагмента устанавливается командой Ячейки меню Формат, закладка Число. Типы данных Excel приведены в следующей таблице.
Таблица I.3.
Тип данных |
Назначение |
|
|
Пример |
||
Числовой |
Служит для представления чисел с |
451,12 |
-9267,123 |
|||
|
фиксированной запятой |
|
|
|
||
Денежный |
Числовой формат, применяемый для |
451,12р. 9267,12р. |
||||
|
отображения денежных величин |
|
|
|||
Финансовый |
Применяется |
для |
выравнивания |
|
|
|
|
денежных |
величин по |
разделителю |
|
|
|
|
целой и дробной части. |
|
|
|
||
Дата |
Применяется для отображения дат |
16.4 |
16.4.02 апрель 02 |
|||
Время |
Применяется для отображения времени |
14:20 |
4.5.02 12:35 |
|||
Процентный |
Значение ячеек умножается на 100 и |
12% |
|
|||
|
сопровождается символом процента |
|
|
|||
Дробный |
Применяется для отображения чисел в |
3/4 |
|
|||
|
виде обычной дроби |
|
4/7 |
|
||
Экспоненциальный |
Применяется для отображения чисел с |
1,23Е-01, здесь 1,23 – значащая часть (мантисса) |
||||
|
плавающей запятой |
|
Е- основание счисления (10) |
|||
|
|
|
|
|
-01- порядок |
|
|
|
|
|
|
число может быть представлено в виде 1,23*10-1 |
|
Текстовый |
Применяется для отображения текстовой |
|
|
|||
|
информации. Вне зависимости от |
|
|
|||
|
содержания обрабатываются как строки |
|
|
|||
Дополнительный |
Текстовый, |
использующий различные |
123-4567 |
|||
|
шаблоны |
для |
ввода |
специфических |
456500 |
|
|
данных (номер телефона, почтовый |
|
|
|||
|
индекс) |
|
|
|
|
|
Общий |
Служит для отображения текстовых и |
|
|
|||
|
числовых значений произвольного типа |
|
|
7.3. Ввод данных
Любая ячейка таблицы может быть заполнена данными. Для ввода данных в ячейку надо активизировать эту ячейку (ячейка выделяется жирным контуром) и начать ввод. Вводимые символы сразу появляются в текущей ячейке и в строке формул.
По окончании ввода данных в текущую ячейку может быть выполнено одно из следующих действий:
−нажата клавиша <Enter> - данные зафиксируются в текущей ячейке, и выделение переместится на одну ячейку вниз;
−нажата кнопка с «галочкой» на строке формул - данные зафиксируются в текущей ячейке, и выделение останется в этой же ячейке;
18
−нажата любая клавиша со стрелкой - данные зафиксируются в текущей ячейке, и выделение переместится в ячейку в направлении, указанном стрелкой;
−нажата кнопка с крестиком на строке формул или нажата клавиша <Esc> - ввод данных будет отменен.
Для ввода одинаковых данных в диапазон ячеек, следует сначала выделить нужный диапазон ячеек, затем ввести необходимые данные и завершить ввод нажатием комбинации клавиш <Ctrl><Shift><Enter>.
По умолчанию по окончании ввода текстовые данные выравниваются по левому краю ячейки, числовые - по правому. Если выравнивание требуется изменить, нужно воспользоваться командой ФОРМАТ/ЯЧЕЙКИ вкладка ВЫРАВНИВАНИЕ. При вводе нецелочисленных данных десятичные знаки отделяются с помощью запятой. Для изменения десятичного разделителя на точку, надо выполнить соответствующую настройку через кнопку ПУСК (НАСТРОЙКА/ПАНЕЛЬ
УПРАВЛЕНИЯ/ЯЗЫК И СТАНДАРТЫ/вкладка ЧИСЛА).
Пример I.2. В ячейку А6 введите свое имя. В ячейку В6 введите свой рост. В ячейку С6 – свое имя. Объясните, почему в разных ячейках данные выровнены по-разному.
Пример I.3. Выделите любые подряд расположенные ячейки и введите в них слово «Пример», завершив ввод комбинацией клавиш <Ctrl><Shift><Enter>.
7.4. Ввод формул
Ввод формулы обязательно должен начинаться со знака равенства (=).
В составе формул могут быть числа, функции, ссылки на адреса или имена ячеек, операторы (см. Таблица I.2), круглые скобки для задания приоритетности операций, логические функции, а также текст, заключенный в кавычки. Например, =В12+А2*4 или =А1&” “&В1 (результатом выполнения этой формулы будет объединение значений ячеек А1 и В1, разделенных пробелом. Допустим в А1 содержится имя, а в В1 – фамилия. Результатом вычисления формулы будет текст, содержащий имя и фамилию).
Формулу, также как и данные можно вводить сразу в несколько ячеек. Для этого следует сначала выделить нужный диапазон ячеек, затем ввести необходимое выражение и завершить ввод нажатием комбинации клавиш <Ctrl><Shift><Enter>.
После ввода формулы в ячейке появляется вычисленный результат, а сама формула отображается в строке формул. Если необходимо (в ходе выверки таблицы) отобразить в ячейках таблицы именно формулы расчета, а не результаты, то следует задать команду СЕРВИС / ПАРАМЕТРЫ и во вкладке ВИД включить параметр окна
ФОРМУЛЫ.
Если результат вычисления формулы или преобразования формата окажется длиннее ширины столбца, в ячейке появляются символы #######. Для получения числового изображения следует увеличить ширину столбца.
Пример I.4. В ячейку А3 введите любое число. В ячейке В3 вычислите квадрат числа, находящегося в А3 по следующей формуле: =А3^2. Введите в ячейку А3 другое число. Проследите изменение значения в ячейке В3.
19