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

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

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

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

Рис. 5.5. Исходные данные

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

=A2&ТЕКСТ(B2;ЕСЛИ(ДЛСТР(B2)<=4; ЕСЛИ(ДЛСТР(B2)=3; "\ \ \ \ \ (000)"; "\ \ \ (0 000)");"\ \ (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) (без кавычек и последней скобки – это программа сделает сама). Нажать ОК. Формула в ячейке D2 будет иметь следующий вид: =A2&ТЕКСТ(B2;ЕСЛИ(ДЛСТР(B2)<=4; ЕСЛИ(ДЛСТР(B2)=3; "\ \ \ \ \ (000)"; "\ \ \ (0 000)");"\ \ (00 000)")) &ТЕКСТ(C2;"\ \ \ (???-00-00)")

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

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

Рис. 5.6. Результат вычислений вложенной функции

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. Щелкнуть в строке формул кнопку списка поля имени , или выполнить команду Формулы|Определенные имена|Присвоить имя.

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

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

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

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

2. Нажать Enter.

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

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

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

3. Нажать Enter.

4. Переместитесь к именованному фрагменту или ячейке.

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

Замена имени

1. Открыть диспетчер имен Формулы|Определенные имена|Диспетчер имен или нажать комбинацию клавишей Ctrl + F3.

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

3. Нажать кнопку Изменить, откроется диалоговое окно Изменение имени, где старое имя будет автоматически выделено и ввести новое имя.

4. Нажать ОК.

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

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

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

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

2. Внесите данные. Строки и столбцы таблицы поименованы (Рис. 5.7.).

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

3. Выделить интервал B3:F8.

4. Выполнить команду Формулы|Определенные имена|Создать из выделенного фрагмента (или Ctrl+Shift+F3). В появившемся диалоговом окне флажки установлены автоматически.

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

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

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

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

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

3. В поле Диапазон ввести =18%. ОК.

4. Для примера, в любую ячейку введите формулу =25*НДС. Убедитесь в правильности расчетов.

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

Имена с внешними ссылками

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

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

3. В поле Имя ввести ИмяВнешнее.

4. В поле Диапазон ввести =Лист1:Лист3!$С$20. ОК.

5. Теперь можно использовать ИмяВнешнее в формуле, содержащей любую функцию, например в ячейке С21 введите функцию СУММ(ИмяВнешнее), которая суммирует значение ячейки С20 с трех листов.

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

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

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

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

3. Выполнить команду Формулы|Определенные имена|Использовать в формуле.

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

5. ОК. Результатом будет являться произведение всех значений по полю Хлеб.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Массивы констант

Массив констант может содержать числовые, текстовые или логические значения. Список значений должен быть заключен в фигурные скобки { }, отдельные элементы этого списка должны отделяться друг от друга « ; » а строки — « : ». Например, процедура преобразования в целые трех вещественных чисел 123,4567; 12,345; 12345,67 выглядит следующим образом:

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

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

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

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

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

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

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

2. Нажать кнопку Копировать или Ctrl+С.

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

4. Нажать кнопку Вставить или Ctrl+V.

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

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

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

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

Вставка вырезанных/копированных ячеек со сдвигом ячеек

В контекстном меню (щелчок правой кнопкой мыши) выбрать команду Вставить вырезанные/скопированные ячейки помещает вырезанные/скопированные ячейки между ячейками рабочего листа со сдвигом ячеек вправо или со сдвигом ячеек вниз.

Пример копирования с использованием двух диапазонов с разными форматами по следующему алгоритму (Рис. 5.8.):

Рис. 5.8. Копирование диапазонов

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

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

3. Активизировать перед вставкой ячейку в правом диапазоне, нажать правую кнопку мыши и выбрать пункт Вставить скопированные ячейки.

4. В диалоговом окне Вставка скопированных ячеек выбрать один из предложенных вариантов: диапазон со сдвигом вправо или диапазон, со сдвигом вниз.

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

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

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

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

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

Задания для индивидуальной работы

Задание 1

1. На новом листе создайте нижеприведенную таблицу, введите в поле Результат обработки формулу:

=А2&ТЕКСТ(В2;"\\\(?? 000)").

2. Скопируйте лист с таблицей из п. 1 и введите в поле Результат обработки формулу: =A2&ТЕКСТ(B2;ЕСЛИ(ДЛСТР(B2)<=4; ЕСЛИ(ДЛСТР(B2)=3; "\\\\\ (000)";"\\\ (0000)");"\ \(00000)")).

3. На новом листе создайте нижеприведенную таблицу и введите в поле Фамилия И.О. формулу: =СЦЕПИТЬ(B3;" ";ЛЕВСИМВ(C3);".";ЛЕВСИМВ(D3);".").

4. Скопируйте лист с таблицей из п. 3, введите в поле Фамилия И.О. формулу:

=B3&" "&ЛЕВСИМВ(C3)&"."&ЛЕВСИМВ(D3)&"."

5. На новом листе создайте нижеприведенную таблицу, введите в поле Возраст формулу: =ГОД(СЕГОДНЯ())-ГОД(ЕЗ). Примените числовой формат ячеек.

6. На новом листе создайте нижеприведенную таблицу, введите в поле Стаж работы формулу: =ГОД(СЕГОДНЯ()-E3)-1900+МЕСЯЦ(СЕГОДНЯ()-E3)/12.

7. На новом листе создайте нижеприведенную таблицу, вычислите порядковый номер суммы дохода от продаж с помощью функции Ранг (=РАНГ(C3;$C$3:$C$11)), а суммы по годам и среднее – с помощью простых арифметических выражений. Среднегодовой, максимальный и минимальный доходы вычислите с помощью соответствующих функций.

8. На новом листе создайте нижеприведенные данные, вычислите тенденцию увеличения прибыли, используя функцию ТЕНДЕНЦИЯ из категории Статистические.

Задание 2

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

10. Используя нижеприведенные данные, создать имена с помощью текстовых ячеек.

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

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

13. Используя данные и созданные имена в п. 10, перейдите к ячейкам, определяющих прибыль от продажи сыров.

14. Используя данные и созданные имена в п. 10, перейдите к ячейкам, определяющих прибыль от продажи сыров соков.

15. Создайте имя Налог на прибыль в размере 48%.

16. Присвойте имя Премия формуле

=Лист<номер>!<Имя ячейки>*15%.

17. Вычислите с помощью внешнего имени произведение чисел 3,8 на 4-х листах.

18. Вставьте на р/л список всех созданных в лабораторной работе имен.

Задание 3

19. Введите произвольные числа в диапазоны А2:А8 и С2:С8. Вычислите в столбце D значения функции КОРЕНЬ(А+С).

20. Найдите значения синусов 12 чисел, поместив результат в интервале 3*4.

21. Используя нижеприведенные диапазоны, выделите, вырежьте и скопируйте любые два столбца левого диапазона и вставьте их в правый диапазон со сдвигом ячеек вправо и со сдвигом ячеек вниз.

22. Используя диапазоны п. 21, выделите, вырежьте и скопируйте любые две строки правого диапазона и вставьте их в левый диапазон со сдвигом ячеек вправо и со сдвигом ячеек вниз.

23. Используя диапазоны п. 21 и команду Специальная вставка, вычтите из 4-й строки правого диапазона вторую строку левого диапазона.

24. Используя левый диапазон п. 21, cкопируйте его на 5 листов р/к.

25. На рабочем листе в любую ячейку введите свое имя. Выделите некоторый диапазон, начиная с ячейки с вашим именем. Заполните выделенный диапазон вниз / вправо / влево / вверх.

26. Выделите ячейку р/л, введите в нее длинное текстовое выражение и распределите его по ячейкам в нижеследующих строках.

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

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

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

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

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

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

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

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

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

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

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