Лабораторная работа 18 Тема- Построение формул
Работа добавлена на сайт samzan.net:
Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
Лабораторная работа №18
Тема: Построение формул. Присвоение имен. Использование массивов. Команды редактирования
Цель работы: Построение сложных выражений с помощью Мастера функций, применение имен ячеек и диапазонов при построении формул, использование массивов для выполнения расчетов.
Задание 1. Постройте сложные выражения, содержащие вложенные функции, с помощью Мастера функций.
Методические указания.
Заполнение таблицы, содержащей большое число записей, можно упростить, если воспользоваться сложным выражением, отображающим вхождения значений всех столбцов таблицы. В качестве примера используется таблица:
В поле Результат обработки необходимо занести значения всех полей таблицы в определенном формате. Выражение для выполнения этой процедуры выглядит следующим образом:
=A2&ТЕКСТ(B2;ЕСЛИ(ДЛСТР(B2)<=4; ЕСЛИ(ДЛСТР(B2)=3; "\ \ \ \ \ (000)"; "\ \ \ (0000)");" \ \ (00 000)")) &ТЕКСТ(C2;"\ \ \ (???-00-00)")
Ввод формулы в ячейку D2 производится по следующему алгоритму:
- Выделить ячейку D2.
- В строку формул ввести знак «=» признак формулы.
- Щелкнуть ячейку А2 и ввести знак «&».
- Найти функцию ТЕКСТ, и в диалоговом окне Аргументы функции в поле Значение ввести щелчком мыши ячейку В2.
- Установить курсор в поле Формат и найти функцию ЕСЛИ.
- В диалоговом окне функции ЕСЛИ (курсор должен находиться в поле Лог_выражение) найти функцию ДЛСТР.
- В диалоговом окне Аргументы функции ДЛСТР в поле Текст ввести щелчком мыши ячейку В2 и нажать ОК.
- Игнорировать сообщение об ошибке, щелкнув ОК.
- Курсор в строке формул должен находиться за скобкой, закрывающей В2. Ввести на месте курсора символы <=4 и точку с запятой
- Найти функцию ЕСЛИ среди десяти использовавшихся в текущем сеансе функций и повторить пп. 6, 7, 8.
- Курсор в строке формул должен находиться за скобкой, закрывающей В2. Ввести на месте курсора символы =3 и точку с запятой
- Ввести с клавиатуры форматы "\ \ \ \ \ (000)"; "\ \ \ (0000)");" \ \ (00 000)")) (следить за правильностью расстановки скобок, кавычек и пробелов между наклонными черточками и скобками).
- Переместить курсор за последнюю закрывающую скобку и ввести «&».
- Найти функцию ТЕКСТ, и в диалоговом окне Аргументы функции в поле Значение ввести щелчком мыши ячейку С2, а в поле Формат \ \ \ (???-00-00) (без кавычек и последней скобки это программа сделает сама). Нажать ОК.
15. В строке формул щелкнуть значок и протянуть маркер
заполнения до конца таблицы.
Результат работы алгоритма представлен в таблице:
Задание 2. Изучите присвоение и использование имен ячеек.
Методические указания.
Любым ячейкам и интервалам можно присвоить имена, а затем применять их в формулах. Существует два способа присвоения имен: использование поля имени и применение команды Вставка|Имя|Присвоить. При присвоении имен следует соблюдать следующие правила:
- Все имена должны начинаться с буквы или символов \ и _. Далее можно использовать числа или спецсимволы.
- Вместо пробела следует использовать символ подчеркивания.
- Имена не должны совпадать с адресами каких-либо ячеек.
- Имена можно записывать отдельными буквами, кроме латинских букв С и R.
- Длина имени ячейки не должна превышать 255 символов, длина имени диапазона 253 символа.
Для присвоения и использования имен применяются следующие алгоритмы:
- На р/л выделить любую ячейку, например С5.
- Щелкнуть в строке формул кнопку списка поля имени, или выполнить команду Вставка|Имя|Присвоить, или нажать комбинацию клавишей Ctrl + F3.
- Ввести имя ячейки, например Импорт.
- Нажать Enter или ОК.
- Присвоение имени интервалу ячеек:
- Выделить интервал, например B10:F15.
- Щелкнуть в строке формул кнопку списка поля имени, или выполнить команду Вставка|Имя|Присвоить, или нажать комбинацию клавишей Ctrl + F3.
- Ввести имя интервала ячеек, например Экспорт.
- Нажать Enter или ОК.
- Перемещение к именованным ячейкам и интервалам:
- Выделить в поле имени нужное имя ячейки или интервала.
- Нажать Enter.
- Присвоение имен на уровне рабочего листа:
- Выделить ячейку или интервал, которым следует присвоить
имя.
- Щелкнуть мышью поле имени и напечатать Лист1!Локальн_ _имя.
- Нажать Enter.
Примечание. Имена на уровне листа доступны только на листе, на котором они определены!
- Замена интервала ячеек, связанного с каким-либо именем:
- Выполнить команду Вставка|Имя|Присвоить или нажать комбинацию клавишей Ctrl + F3.
- Выбрать нужное имя в списке.
- В поле Формула выделить мышью старую ссылку и ввести новую или выделить старую ссылку и выделить новые нужные ячейки непосредственно на р/л.
- Нажать ОК.
Примечание. Для удаления какого-либо имени нужно выделить его в списке и щелкнуть кнопку Удалить.
- Создание имен с помощью текстовых ячеек.
За одну операцию можно присвоить имена нескольким ячейкам или смежным интервалам ячеек:
- На р/л листе создать таблицу, в которой столбцы Магазины, строки Продукты.
- В некоторый интервал, например B10-.F15, внесены данные. Строки и столбцы таблицы поименованы. Таблица выглядит так:
Выделить интервал B10:F15.
- Выполнить команду Вставка|Имя|Создать (или Ctrl+Shift+F3).
В появившемся диалоговом окне Создать имена флажки установлены автоматически.
- Нажать ОК. В поле имени будут отображены все имена.
- Присвоение имен константам и формулам.
Можно создать имена, определяющие константы и формулы, которые не содержатся ни в одной ячейке рабочего листа. Например, 18% НДС:
- Выполнить команду Вставка|Имя|Присвоить.
- Напечатать НДС в поле ввода Имя.
- В поле Формула ввести =18%.
- ОК.
Таким же образом можно присвоить имя некоторой формуле. Пусть для вычисления налога нужно постоянно использовать формулу =Лист1!$А$1+25, 8%. Присвоив этой формуле имя Налог, можно использовать его во всех расчетах, при любых изменениях в ячейке А1.
Примечание. Именованные константы и формулы не видны в Поле имени, но их всегда можно найти в списке окна Присвоение имени.
- Объемные имена:
- Выделить ячейку С20 на р/л Лист1, выделить листы 13 и ввести число 25.
- Выполнить команду Вставка|Имя|Присвоить (или Ctrl+F3).
- В поле Имя ввести ИмяОбъемн.
- В поле Формула ввести =Лист1:ЛистЗ!$С$20.
- ОК.
Примечание. Теперь можно использовать ИмяОбъемн в формуле, содержащей любую функцию, например =СУММ(ИмяОбъемн).
- Вставка имен в формулы:
- Выделить любую ячейку р/л.
- Начать ввод формулы, например =ПРОИЗВЕД(
- Выполнить команду Вставка|Имя|Вставить.
- Выбрать нужное имя.
- ОК.
- Вставка списка имен.
Команда Вставка|Имя|Вставить|Все имена помещает список имеющихся имен в столбец, начиная с активной ячейки, а в смежный столбец справа помещаются связанные с этими именами формулы.
Задание 3. Изучите работу с массивами.
Методические указания.
Действия над числами в массиве, расположенном горизонтально:
- На новом р/л заполнить целыми числами интервал А1:Е2. Вычислить, например, сумму чисел в строках 1 и 2 по каждому столбцу и результат поместить в строку 3.
- Выделить интервал АЗ:ЕЗ.
- Ввести формулу =А1:Е1+А2:Е2.
- Нажать клавиши Ctrl+Shift+Enter.
Единая формула существует одновременно в 5 ячейках, но внести изменения в них по отдельности нельзя. Изменения возможны только при выделении всего интервала. Например, изменить сумму на произведение нужно так:
- Выделить интервал АЗ:ЕЗ.
- Внести изменения в формулу.
- Нажать клавиши Ctrl+Shift+Enter.
Действия над числами в массиве, расположенном вертикально:
- Заполнить вещественными числами интервал J1:K6.
- Выделить интервал L1:L6.
- Ввести формулу =J1:J6*K1:K6.
- Нажать клавиши Ctrl+Shift+Enter.
Можно создать массивы, которые содержат несколько строк и столбцов. Такие массивы называются двумерными. Например, для получения целочисленных значений данных в ячейках А21:С26 можно создать двумерный диапазон массива:
- Ввести в диапазон А21:С26 вещественные числа.
- Выделить диапазон такого же размера и формы, как диапазон с исходными данными, например, E21:G26.
- Ввести формулу =ЦЕЛОЕ(А21:С26).
- Нажать клавиши Ctrl+Shift+Enter.
Правила ввода формул массива.
- Фигурные скобки вводить самостоятельно нельзя, в противном случае формула воспринимается как текст.
- Все ячейки в интервале массива являются единым целым и редактируются целиком путем выделения всего интервала.
- Редактировать табличную формулу можно следующим образом:
- Дважды щелкнуть одну из ячеек результата, курсор появится в ней самой. После редактирования нажать Ctrl+Shift+Enter.
- Для перемещения массива-интервала следует выделить его целиком и выполнить команды Вырезать и Вставить.
- Массивы констант. Массив констант может содержать числовые,
текстовые или логические значения. Список значений должен
быть заключен в фигурные скобки { }, отдельные элементы этого списка должны отделяться друг от друга «;» а строки «:».
Например, процедура преобразования в целые трех чисел
123,4567; 12,345; 12345,67 выглядит следующим образом:
- Выделить диапазон А8:С8.
- Ввести формулу =Целое({123,4567;12,345;12345,67}).
- Нажать Ctrl+Shift+Enter.
Задание 4. Изучите использование команд редактирования.
Методические указания.
Команда Вставка|Ячейки позволяет вставлять как одну или несколько ячеек, так и строку или столбец.
При выполнении команд Правка|Вырезать (Ctrl+X) и Правка| Вставить (Ctrl+V) должны соблюдаться следующие правила:
- Вырезаемая область должна быть единым прямоугольным интервалом.
- После команды Вырезать команда Вставка выполняется только один раз.
- Перед выполнением команды Вставить достаточно выделить ячейку в верхнем левом углу нового интервала.
Вставка вырезанных/копированных ячеек с раздвиганием. Команда Вставка|Вырезанные/Скопированные ячейки помещает вырезанные/ скопированные ячейки между ячейками рабочего листа со сдвигом ячеек вправо или со сдвигом ячеек вниз. Приводимый ниже пример использует два диапазона с разными форматами для наглядности по следующему алгоритму:
- Создать два диапазона.
- Выделить и вырезать (или копировать) ячейки в левом диапазоне, например второй и третий столбцы.
- Активизировать перед вставкой ячейку в правом диапазоне.
- Выполнить команду Вставка|Вырезанные/Скопированные ячейки. Команды Правка|Копировать и Правка|Вставить используются для
дублирования содержимого и формата выделенных интервалов в другие места без изменения содержимого исходных ячеек:
- Выделить ячейки Откуда.
- Нажать кнопку Копировать или выполнить команду Правка (Копировать.
- Выделить ячейку Куда.
- Нажать кнопку Вставить или выполнить команду Правка|Вставить.
Команда Вставить переносит все содержимое, форматы и примечания. Команда Правка|Специальная вставка позволяет перенести что-то одно из вышеперечисленного.
Команда Специальная вставка в разделе Операция одноименного диалогового окна дает возможность выполнить одно из четырех арифметических действий над содержимым принимающих и копируемых ячеек.
Примечания. Следует избегать использования кнопки Все в разделе Вставить диалогового окна совместно с арифметическими кнопками. При выполнении арифметических операций копируемые ячейки с текстовыми данными игнорируются. Флажок Пропускать пустые позволяет при вставке выделенного интервала игнорировать содержащиеся в нем пустые ячейки. Флажок Транспонировать позволяет осуществлять транспонирование выделенного интервала данных.
- Команды Правка|Заполнить|Вниз/Вправо/Вверх/Влево позволяют заполнить по заданному направлению выделенный интервал однородными данными, содержащимися в первой ячейке диапазона.
- Команда Правка|Заполнить|По листам копирует выделенный интервал на несколько выделенных листов.
Команда Правка|Заполнить|Выровнять дает возможность разбить содержащееся в ячейке длинное текстовое значение на несколько частей, соответствующих текущей ширине столбца, и распределить их по ячейкам в нижеследующих строках.
- Команда Правка|Заполнить|Прогрессия позволяет с помощью диалогового окна Прогрессия заполнить выделенный интервал разными типами данных.
Контрольные вопросы
- Показать, как с помощью Мастера вводить выражения, состоящие из вложенных функций.
- Где расположено поле имени и для чего оно предназначено?
- Какая комбинация клавишей вызывает окно Присвоение имени?
- Какие существуют правила присвоения имен?
- Как вычисляется сумма значений, объединенных именем Объемное имя?
- Какие существуют правила ввода формул массива? Как можно редактировать табличную формулу?
- Как выполняется вставка вырезанных ячеек с раздвиганием и вставка копируемых ячеек с раздвиганием ?
- Какие возможности предоставляет команда Специальная вставка?
- Как разбить длинное текстовое значение на несколько частей и определить их по ячейкам в нижеследующих строках?
-