Лабораторная работа №18

Тема: Построение формул. Присвоение имен. Использование массивов. Команды редактирования

Цель работы: Построение сложных выражений с помощью Ма­стера функций, применение имен ячеек и диапазонов при построе­нии формул, использование массивов для выполнения расчетов.

Задание 1. Постройте сложные выражения, содержащие вложен­ные функции, с помощью Мастера функций.

Методические указания.

Заполнение таблицы, содержащей большое число записей, мож­но упростить, если воспользоваться сложным выражением, отобра­жающим вхождения значений всех столбцов таблицы. В качестве примера используется таблица:

В поле Результат обработки необходимо занести значения всех полей таблицы в определенном формате. Выражение для выполне­ния этой процедуры выглядит следующим образом:

=A2&ТЕКСТ(B2;ЕСЛИ(ДЛСТР(B2)<=4; ЕСЛИ(ДЛСТР(B2)=3; "\ \ \ \ \ (000)"; "\ \ \ (0000)");" \ \ (00 000)")) &ТЕКСТ(C2;"\ \ \ (???-00-00)")

Ввод формулы в ячейку D2 производится по следующему алго­ритму:

1. Выделить ячейку D2.

2. В строку формул ввести знак «=» — признак формулы.

3. Щелкнуть ячейку А2 и ввести знак «&».

4. Найти функцию ТЕКСТ, и в диалоговом окне Аргументы фун­кции в поле Значение ввести щелчком мыши ячейку В2.

5. Установить курсор в поле Формат и найти функцию ЕСЛИ.

6. В диалоговом окне функции ЕСЛИ (курсор должен находить­ся в поле Лог_выражение) найти функцию ДЛСТР.

7. В диалоговом окне Аргументы функции ДЛСТР в поле Текст ввести щелчком мыши ячейку В2 и нажать ОК.

8. Игнорировать сообщение об ошибке, щелкнув ОК.

9. Курсор в строке формул должен находиться за скобкой, за­крывающей В2. Ввести на месте курсора символы <=4 и точку с за­пятой

10. Найти функцию ЕСЛИ среди десяти использовавшихся в те­кущем сеансе функций и повторить пп. 6, 7, 8.

11. Курсор в строке формул должен находиться за скобкой, за­крывающей В2. Ввести на месте курсора символы =3 и точку с за­пятой

12. Ввести с клавиатуры форматы "\ \ \ \ \ (000)"; "\ \ \ (0000)");" \ \ (00 000)")) (следить за правильностью расстановки скобок, кавы­чек и пробелов между наклонными черточками и скобками).

13. Переместить курсор за последнюю закрывающую скобку и ввести «&».

14. Найти функцию ТЕКСТ, и в диалоговом окне Аргументы фун­кции в поле Значение ввести щелчком мыши ячейку С2, а в поле Формат — \ \ \ (???-00-00) (без кавычек и последней скобки — это программа сделает сама). Нажать ОК.

15. В строке формул щелкнуть значок и протянуть маркер

заполнения до конца таблицы.

Результат работы алгоритма представлен в таблице:

Задание 2. Изучите присвоение и использование имен ячеек.

Методические указания.

Любым ячейкам и интервалам можно присвоить имена, а затем применять их в формулах. Существует два способа присвоения имен: использование поля имени и применение команды Вставка|Имя|Присвоить. При присвоении имен следует соблюдать следующие правила:

1. Все имена должны начинаться с буквы или символов \ и _. Далее можно использовать числа или спецсимволы.

2. Вместо пробела следует использовать символ подчеркивания.

3. Имена не должны совпадать с адресами каких-либо ячеек.

4. Имена можно записывать отдельными буквами, кроме латин­ских букв С и R.

5. Длина имени ячейки не должна превышать 255 символов, дли­на имени диапазона — 253 символа.

Для присвоения и использования имен применяются следующие алгоритмы:

• Присвоение имени ячейке:

1. На р/л выделить любую ячейку, например С5.

2. Щелкнуть в строке формул кнопку списка поля имени, или выполнить команду Вставка|Имя|Присвоить, или нажать комбинацию клавишей Ctrl + F3.

3. Ввести имя ячейки, например Импорт.

4. Нажать Enter или ОК.

• Присвоение имени интервалу ячеек:

1. Выделить интервал, например B10:F15.

2. Щелкнуть в строке формул кнопку списка поля имени, или выполнить команду Вставка|Имя|Присвоить, или нажать комбинацию клавишей Ctrl + F3.

3. Ввести имя интервала ячеек, например Экспорт.

4. Нажать Enter или ОК.

• Перемещение к именованным ячейкам и интервалам:

1. Выделить в поле имени нужное имя ячейки или интервала.

2. Нажать Enter.

• Присвоение имен на уровне рабочего листа:

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

имя.

2. Щелкнуть мышью поле имени и напечатать Лист1!Локальн_ _имя.

3. Нажать Enter.

Примечание. Имена на уровне листа доступны только на листе, на котором они определены!

• Замена интервала ячеек, связанного с каким-либо именем:

1. Выполнить команду Вставка|Имя|Присвоить или нажать ком­бинацию клавишей Ctrl + F3.

2. Выбрать нужное имя в списке.

3. В поле Формула выделить мышью старую ссылку и ввести но­вую или выделить старую ссылку и выделить новые нужные ячейки непосредственно на р/л.

4. Нажать ОК.

Примечание. Для удаления какого-либо имени нужно выде­лить его в списке и щелкнуть кнопку Удалить.

• Создание имен с помощью текстовых ячеек.

За одну операцию можно присвоить имена нескольким ячейкам или смежным интервалам ячеек:

1. На р/л листе создать таблицу, в которой столбцы — Магази­ны, строки — Продукты.

2. В некоторый интервал, например B10-.F15, внесены данные. Строки и столбцы таблицы поименованы. Таблица выглядит так:

Выделить интервал B10:F15.

3. Выполнить команду Вставка|Имя|Создать (или Ctrl+Shift+F3).

В появившемся диалоговом окне Создать имена флажки установле­ны автоматически.

4. Нажать ОК. В поле имени будут отображены все имена.

• Присвоение имен константам и формулам.

Можно создать имена, определяющие константы и формулы, которые не содержатся ни в одной ячейке рабочего листа. Напри­мер, 18% НДС:

1. Выполнить команду Вставка|Имя|Присвоить.

2. Напечатать НДС в поле ввода Имя.

3. В поле Формула ввести =18%.

4. ОК.

Таким же образом можно присвоить имя некоторой формуле. Пусть для вычисления налога нужно постоянно использовать форму­лу =Лист1!$А$1+25, 8%. Присвоив этой формуле имя Налог, можно использовать его во всех расчетах, при любых изменениях в ячейке А1.

Примечание. Именованные константы и формулы не видны в Поле имени, но их всегда можно найти в списке окна Присвоение имени.

1. Объемные имена:

1. Выделить ячейку С20 на р/л Лист1, выделить листы 1—3 и вве­сти число 25.

2. Выполнить команду Вставка|Имя|Присвоить (или Ctrl+F3).

3. В поле Имя ввести ИмяОбъемн.

4. В поле Формула ввести =Лист1:ЛистЗ!$С$20.

5. ОК.

Примечание. Теперь можно использовать ИмяОбъемн в фор­муле, содержащей любую функцию, например =СУММ(ИмяОбъемн).

2. Вставка имен в формулы:

1. Выделить любую ячейку р/л.

2. Начать ввод формулы, например =ПРОИЗВЕД(

3. Выполнить команду Вставка|Имя|Вставить.

4. Выбрать нужное имя.

5. ОК.

3. Вставка списка имен.

Команда Вставка|Имя|Вставить|Все имена помещает список име­ющихся имен в столбец, начиная с активной ячейки, а в смежный столбец справа помещаются связанные с этими именами формулы.

Задание 3. Изучите работу с массивами.

Методические указания.

Действия над числами в массиве, расположенном горизонтально:

1. На новом р/л заполнить целыми числами интервал А1:Е2. Вычислить, например, сумму чисел в строках 1 и 2 по каждому столб­цу и результат поместить в строку 3.

2. Выделить интервал АЗ:ЕЗ.

3. Ввести формулу =А1:Е1+А2:Е2.

4. Нажать клавиши Ctrl+Shift+Enter.

Единая формула существует одновременно в 5 ячейках, но внес­ти изменения в них по отдельности нельзя. Изменения возможны только при выделении всего интервала. Например, изменить сумму на произведение нужно так:

1. Выделить интервал АЗ:ЕЗ.

2. Внести изменения в формулу.

3. Нажать клавиши Ctrl+Shift+Enter.

Действия над числами в массиве, расположенном вертикально:

1. Заполнить вещественными числами интервал J1:K6.

2. Выделить интервал L1:L6.

3. Ввести формулу =J1:J6*K1:K6.

4. Нажать клавиши Ctrl+Shift+Enter.

Можно создать массивы, которые содержат несколько строк и столбцов. Такие массивы называются двумерными. Например, для получения целочисленных значений данных в ячейках А21:С26 мож­но создать двумерный диапазон массива:

1. Ввести в диапазон А21:С26 вещественные числа.

2. Выделить диапазон такого же размера и формы, как диапазон с исходными данными, например, E21:G26.

3. Ввести формулу =ЦЕЛОЕ(А21:С26).

4. Нажать клавиши Ctrl+Shift+Enter.

Правила ввода формул массива.

1. Фигурные скобки вводить самостоятельно нельзя, в против­ном случае формула воспринимается как текст.

2. Все ячейки в интервале массива являются единым целым и редактируются целиком путем выделения всего интервала.

4. Редактировать табличную формулу можно следующим образом:

1. Дважды щелкнуть одну из ячеек результата, курсор появится в ней самой. После редактирования нажать Ctrl+Shift+Enter.

2. Для перемещения массива-интервала следует выделить его целиком и выполнить команды Вырезать и Вставить.

5. Массивы констант. Массив констант может содержать числовые,

текстовые или логические значения. Список значений должен

быть заключен в фигурные скобки { }, отдельные элементы это­го списка должны отделяться друг от друга «;» а строки — «:».

Например, процедура преобразования в целые трех чисел

123,4567; 12,345; 12345,67 выглядит следующим образом:

1. Выделить диапазон А8:С8.

2. Ввести формулу =Целое({123,4567;12,345;12345,67}).

3. Нажать Ctrl+Shift+Enter.

Задание 4. Изучите использование команд редактирования.

Методические указания.

Команда Вставка|Ячейки позволяет вставлять как одну или не­сколько ячеек, так и строку или столбец.

При выполнении команд Правка|Вырезать (Ctrl+X) и Правка| Вставить (Ctrl+V) должны соблюдаться следующие правила:

1. Вырезаемая область должна быть единым прямоугольным ин­тервалом.

2. После команды Вырезать команда Вставка выполняется толь­ко один раз.

3. Перед выполнением команды Вставить достаточно выделить ячейку в верхнем левом углу нового интервала.

Вставка вырезанных/копированных ячеек с раздвиганием. Коман­да Вставка|Вырезанные/Скопированные ячейки помещает вырезанные/ скопированные ячейки между ячейками рабочего листа со сдвигом ячеек вправо или со сдвигом ячеек вниз. Приводимый ниже пример использует два диапазона с разными форматами для наглядности по следующему алгоритму:

1. Создать два диапазона.

2. Выделить и вырезать (или копировать) ячейки в левом диапа­зоне, например второй и третий столбцы.

3. Активизировать перед вставкой ячейку в правом диапазоне.

4. Выполнить команду Вставка|Вырезанные/Скопированные ячейки. Команды Правка|Копировать и Правка|Вставить используются для

дублирования содержимого и формата выделенных интервалов в дру­гие места без изменения содержимого исходных ячеек:

1. Выделить ячейки Откуда.

2. Нажать кнопку Копировать или выполнить команду Правка (Копировать.

3. Выделить ячейку Куда.

4. Нажать кнопку Вставить или выполнить команду Правка|Вставить.

Команда Вставить переносит все — содержимое, форматы и при­мечания. Команда Правка|Специальная вставка позволяет перенести что-то одно из вышеперечисленного.

Команда Специальная вставка в разделе Операция одноименного диалогового окна дает возможность выполнить одно из четырех арифметических действий над содержимым принимающих и копи­руемых ячеек.

Примечания. Следует избегать использования кнопки Все в разделе Вставить диалогового окна совместно с арифметическими кнопками. При выполнении арифметических операций копируемые ячейки с текстовыми данными игнорируются. Флажок Пропускать пу­стые позволяет при вставке выделенного интервала игнорировать со­держащиеся в нем пустые ячейки. Флажок Транспонировать позволяет осуществлять транспонирование выделенного интервала данных.

• Команды Правка|Заполнить|Вниз/Вправо/Вверх/Влево позволяют заполнить по заданному направлению выделенный интервал одно­родными данными, содержащимися в первой ячейке диапазона.

• Команда Правка|Заполнить|По листам копирует выделенный ин­тервал на несколько выделенных листов.

Команда Правка|Заполнить|Выровнять дает возможность разбить содержащееся в ячейке длинное текстовое значение на несколь­ко частей, соответствующих текущей ширине столбца, и распре­делить их по ячейкам в нижеследующих строках.

• Команда Правка|Заполнить|Прогрессия позволяет с помощью ди­алогового окна Прогрессия заполнить выделенный интервал раз­ными типами данных.

Контрольные вопросы

1. Показать, как с помощью Мастера вводить выражения, состоя­щие из вложенных функций.

2. Где расположено поле имени и для чего оно предназначено?

3. Какая комбинация клавишей вызывает окно Присвоение имени?

4. Какие существуют правила присвоения имен?

5. Как вычисляется сумма значений, объединенных именем Объемное имя?

6. Какие существуют правила ввода формул массива? Как можно редактировать табличную формулу?

7. Как выполняется вставка вырезанных ячеек с раздвиганием и вставка копируемых ячеек с раздвиганием?

8. Какие возможности предоставляет команда Специальная вставка?

9. Как разбить длинное текстовое значение на несколько частей и определить их по ячейкам в нижеследующих строках?