- •Введение
- •Экран, лист, книга и элементарные приемы перемещения по ним
- •Основные понятия
- •Строка формул и элементарные приемы перемещения
- •Мышь, экранные курсоры и строка состояния
- •Вопросы для самопроверки
- •Контрольные вопросы
- •Основные средства и команды управления данными Соглашения и обозначения
- •Пример 2.2. ПиО_лкм а2:в6. Цель действий: выделение блока а2:в6 (о выделении см. Ниже). Перечисленная последовательность действий означает выполнение следующих пошаговых инструкций:
- •Типы меню. Основные форматы. Команда Формат/Ячейки
- •Вопросы для самопроверки
- •Контрольные вопросы
- •Выделение ячеек, строк, столбцов, диапазонов и листов
- •Вопросы для самопроверки
- •Контрольные вопросы
- •Задания для лабораторных работ
- •Ввод, редактирование и форматирование текста
- •4.1. Типы данных и константные категории
- •4.2. Практические приемы работы с текстовыми данными
- •Пример 4.2. Исходная ситуация: текст «Выравнивание» выровнен по левому краю ячейки а1, но выходит за правую границу столбца а.
- •Пример 4.3. Исходная ситуация: .
- •Пример 4.4. Цель: До фиксации ввода (т.Е. В процессе ввода или редактирования) данных осуществить перенос текста внутри ячейки, изменяя ее высоту, при неизменной ширине.
- •Пример 4.5. Исходная ситуация: .
- •Пример 4.6. Цель: Осуществить копирование содержимого ячейки а1 в ячейку с1. Исходная ситуация: .
- •Пример 4.10. Цель: в ячейку в1 ввести текст «Мероприятие», в ячейку с1 – текст «Полученная прибыль» (без кавычек). Настроить ширину ячеек по ширине введенного текста.
- •Пример 4.11. Цель: в ячейку в1 ввести текст «Мероприятие», в ячейку с1 – текст «Полученная прибыль» (без кавычек). Отформатировать введенные данные без изменения ширины ячеек.
- •Пример 4.15. Цель: сформировать и отформатировать таблицу.
- •Пример 4.17. Постановка задачи: Предположим, что в готовой таблице (например, в последней) по каким-либо причинам нужно изъять какой-либо столбец (например, столбец е). Как это сделать?
- •Вопросы для самопроверки
- •Контрольные вопросы
- •Задания для лабораторных работ
- •Ввод, редактирование и форматирование чисел
- •Пример 5.1. Цель: Ввести в ячейку число 0,112.
- •Пример 5.2. Цель: Отформатировать введенное число полужирным шрифтом и выровнять его по центру ячейки.
- •Пример 5.3. Цель: Уменьшить разрядность введенного в примере 5.2 числа на единицу.
- •Пример 5.4. Цель: в диапазон а1:с3 ввести набор числовых значений, выровнять их по центру ячеек, назначить диапазону внешние и внутренние границы.
- •Пример 5.5. Цель: Ввести в ячейку рациональную дробь 3/7.
- •Пример 5.9. Цель: Подсчитать число дней между датами 11.11.67 и 13.10.00.
- •Вопросы для самопроверки
- •Контрольные вопросы
- •Автозаполнение
- •Пример 6.1. Цель: Ввести в строку числовой ряд 1, 2, 3, … по его первому значению.
- •Пример 6.3. Цель: Ввести в строку числовой ряд 3, 5, 7, 9 … по первым двум значениям данных.
- •Т.Е. Не к наращиванию ряда, а к по-парному копированию значений, находящихся в ячейках а1 и в1.
- •Решим предыдущую задачу (п. 2) с использованием команд Правка / Заполнить / Прогрессия. Результат естественно будет тем же. Опишем наши действия:
- •Вопросы для самопроверки
- •Задания для лабораторных работ (для всех вариантов)
- •Элементарные приемы работы с формулами и функциями
- •Пример 7.2. Цель: Осознать, как связаны между собой влияющие и зависимые ячейки при их перемещении по листу Ехсеl.
- •Пример 7.3. Цель: Научиться применять в формулах относительные ссылки.
- •Пример 7.5. Цель: Научиться организовывать данные, включающие формулы и «записанные» в нескольких пользовательских таблицах, на одном или нескольких рабочих листах одной и той же книги.
- •Пример 7.6. Цель: Научиться строить диаграммы.
- •Пример 7.7. Цель: Научиться вычислять значения функций двух переменных в конечном числе точек.
- •Пример 7.9. Цель: Осознать, в каких случаях имеет смысл пользоваться абсолютными ссылками.
- •Пример 7.10. Цель: Познакомиться с «работой» функции сумм.
- •Пример 7.11. Цель: Рассмотреть возможность использования имен в формулах в качестве ссылок на (именованные) ячейки и диапазоны.
- •Пример 7.13. Постановка задачи: Число 2 требуется возвести в степень каждого из четырех чисел, записанных в ячейках а41:а44.
- •Пример 7.14. Цель: Провести сравнение двух способов решения одной и той же задачи: основанного на использовании формул массивов и обычных «ячеечных» формул.
- •Пример 7.15. Цель: Рассмотреть возможность совместного использования формул массивов и обычных «ячеечных» формул.
- •Пример 7.17. Цель: Составить таблицу истинности функции или для случая двух аргументов.
- •Число месяцев, в течение которых расходы превышали бюджет.
- •Вопросы для самопроверки
- •Контрольные вопросы
- •Задания для лабораторных работ (для всех вариантов)
- •Ответы на вопросы
- •Вопросы к зачету
- •Литература
Пример 7.9. Цель: Осознать, в каких случаях имеет смысл пользоваться абсолютными ссылками.
Постановка задачи: Имеются данные об объемах реализации одного и того же товара в трех магазинах за второй квартал года по определенной цене. Требуется найти выручку от реализации этого товара по каждому магазину за каждый месяц квартала (числовые значения см. ниже по тексту).
Результат:
Обсуждение: Выручка находится по алгебраической формуле <ЦЕНА>*<ОБЪЕМ РЕАЛИЗАЦИИ>. Цена записана в ячейке D3, а формулы Ехсеl, реализующие решение задачи, находятся в ячейках диапазона G7:I9. Поскольку цена товара за единицу продукции одна и та же для всех магазинов и для всех месяцев квартала, то рационально в главной формуле («лежащей» в ячейке G7) дать абсолютную ссылку на ячейку D3 (на рисунке стрелками показано ее «влияние» на все ячейки диапазона с результатами вычислений), а на ячейку В7 – дать относительную ссылку. Тогда без труда всю область выходных данных можно заполнить формулами, возникающими в результате копирования главной формулы с помощью маркера заполнения сначала вдоль соответствующей горизонтали, а затем вдоль соответствующей вертикали (или в обратном порядке). Ниже показаны формулы Ехсеl в зависимых ячейках и схема копирования главной формулы.
Вывод: Если условие задачи позволяет четко разграничить входные и выходные данные, то часто ссылки в ячейках выходных данных на ячейки, содержащие входные данные, являются абсолютными.
Вопрос: Каким образом перенастраиваются ссылки в зависимых ячейках данного примера, если произвести копирование
только блока входных данных,
только блока выходных данных,
блока, содержащего и те, и другие данные?
Пример 7.10. Цель: Познакомиться с «работой» функции сумм.
Постановка задачи: Имеются данные о выручке магазина за первое полугодие. Требуется найти суммарную выручку, выручку нарастающим итогом по месяцам и процент выручки за каждый месяц от полугодовой итоговой суммы.
Результат и метод решения задачи для двух вариантов расчета выручки нарастающим итогом показаны на Рис.7.12.
Справка
Функция СУММ суммирует все числа в интервале ячеек.
Синтаксис: = СУММ(Число1;Число2; ...), где Число1, Число2, ... - это от 1 до 30 аргументов, для которых требуется определить итог или сумму. При этом:
Рис. 7.12. Иллюстрация к примеру 7.10.
Суммируются числа, логические значения (о них речь пойдет ниже, но уже сейчас отметим, что логических значений всего два: ИСТИНА и ЛОЖЬ, причем первое в формулах, как правило, может быть представлено единицей, а второе - нулем) и текстовые представления чисел (т.н. числовые тексты), которые непосредственно введены в список аргументов.
Если аргумент является массивом или ссылкой, то только числа учитываются в массиве или ссылке. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.
Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки.
Иллюстративные примеры
Если в ячейке содержится формула =СУММ(3; 2), то ее отображаемое значение равно 5 (далее для краткости будем писать СУММ(3; 2) = 5).
СУММ("3"; 2; ИСТИНА) = 6, так как текстовое значение "3" преобразуется в число 3 (обратите внимание: в формулах текстовые строки, в том числе и числовые тексты, записываются в двойных кавычках), а логическое значение ИСТИНА преобразуется в число 1.
Если ячейка A1 содержит текст "3", а ячейка B1 содержит логическое значение ИСТИНА, то СУММ(A1; B1; 2)=2, так как нечисловые значения в ссылках игнорируются.
Если ячейки диапазона A2:E2 содержат числа 5, 1, 3, 4 и 10, то СУММ(A2:C2)=23.
Если ячейки диапазона A2:E2 содержат тексты а, б, в, г, д, то СУММ(A2:Е2) = 0.
Совет: Обдумайте, как в рассмотренном выше основном примере во втором варианте расчета выручки нарастающим итогом «работают» формулы =СУММ($B$4:B4), =СУММ($B$4:B5), =СУММ($B$4:B6) и т.д. по диапазону Н4:Н9.
Использование абсолютных имен
Ячейкам, формулам и константам в Excel можно присваивать имена, которые впоследствии используются как ссылки в формулах. При этом следует придерживаться следующих правил:
Первый символ в имени должен быть буквой или символом подчеркивания. Остальные символы имени могут быть: буквами, числами, точками и символами подчеркивания.
Имена не должны иметь сходства со ссылками, например, Z$100 или R1C1.
Использование пробела запрещено. В качестве разделителей слов можно использовать символы подчеркивания и точки, например: «Первый.Квартал» или «Процентная_Ставка».
Имя может состоять из строчных или прописных букв, хотя Excel их не различает.
Самый простой способ присваивания ячейке, группе ячеек или несмежному диапазону имени состоит в выполнении следующих шагов:
Выделить диапазон, которому предполагается присвоить имя.
Исполнить !ЛКМ в поле имени, которое расположено в левой части строки формул.
Ввести с клавиатуры имя согласно упомянутым выше правилам.
Нажать клавишу <Enter>.
Существует альтернативный способ присваивания имен, включающий п.1 предыдущего списка (этот пункт не является обязательным) и выполнение команд Вставка / Имя / Присвоить / !ЛКМ по полю Имя / Введение с клавиатуры выбранного имени / !ЛКМ в поле Формула / !ЛКМ по ячейке рабочего листа (или ПиО_ЛКМ по смежному диапазону) / !ЛКМ по кнопке <Добавить> / <ОК>.
Замечание: Созданные таким образом имена ячеек или диапазонов по умолчанию в формулах соответствуют абсолютным ссылкам на эти (поименованные) ячейки или диапазоны. Поэтому такие имена мы вправе называть абсолютными именами (заметим, что в Ехсеl используются также относительные и смешанные имена). Если в процессе ввода формулы требуется сделать ссылку на поименованный диапазон, нужно исполнить !ЛКМ по соответствующему имени в диалоговом окне Вставка имени, которое вызывается нажатием клавиши <F3> (или !ЛКМ непосредственно по именованной ячейке, или ПиО_ЛКМ по именованному диапазону).
Отметим еще одно полезное свойство существующих на рабочем листе именованных диапазонов: их можно использовать как метки при перемещениях по листу, исполняя !ЛКМ по соответствующим именам, предварительно открыв «карман имен» в левой части строки формул (при вводе формул «карман имен» становится «карманом функций», ранее применявшихся в обработке данных в текущем сеансе работы в открытой книге Ехсеl).
Очень удобно присваивать ячейкам и смежным диапазонам ячеек имена при помощи существующих (ранее набранных на клавиатуре на рабочем листе) заголовков строк и столбцов. В этом случае следует:
Выделить область, ячейкам которой (или которой как целому) предполагается присвоить имя, вместе со строкой или/и столбцом заголовков.
Выполнить команды Вставка / Имя / Создать.
В контекстном меню установить (не все, а лишь необходимые) флажки В строке выше, В столбце слева, В строке ниже или В столбце справа в группе флажков По тексту, чтобы указать расположение заголовков, на основе которых создаются имена.
Замечание: Созданные таким образом имена ссылаются только на ячейки, содержащие значения, и не включают в себя диапазоны заголовков строк и столбцов, на основе которых эти имена создавались.