Практика Excel

Вариант №13

1. Какими способами можно вставить функцию в формулу в ТП Excel.

2. Каким образом можно присвоить имя диапазону ячеек в ТП Excel.

3. Таблица № 13

4. Задание 3

3.1 Рассчитать оплату труда (рис.1). При выполнении расчетов использовать относительные и абсолютные ссылки (количество часов произвольное, все разделы таблицы заполнены).

3.2 Рассчитать надбавку к зарплате (разряд, оклад - произвольные числа, все разделы таблицы заполнены) (рис2)

Размер надбавки зависит от оклада и стажа работы:

- стаж от 5 до 10 лет - 5% от оклада;

- стаж от 10 до 15 лет - 10% от оклада;

- стаж от 10 до 20 лет - 15% от оклада;

- свыше 20 лет - 25% от оклада.

При выполнении расчетов использовать смешанные ссылки.

1. Какими способами можно вставить функцию в формулу в ТП Excel

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

В качестве примера формулы, вычисляющие корни квадратного трехчлена ax²+bx+с=0. Они введены в ячейке А2 и А3 и имеют следующий вид:

= (- В1+ КОРЕНЬ (В1*В1-4*А1*С1))/2/А1

= (- В1 - КОРЕНЬ (В1*В1-4*А1*С1))/2/А1

В ячейках А1, В1 и С1 находятся значения коэффициентов a, b и с соответственно. Если ввели значения коэффициентов а=1, b= - 5 и с=6 (это означает, что в ячейках А1, В1 и С1 записаны числа 1, -5 и 6), то в ячейках А2 и А3, где записаны формулы получим числа 2 и 3. Если изменить число в ячейке А1 на -1, то в ячейках с формулами получатся числа -6 и 1

В ячейках А2 и А3 представлен результат работы формулы. Если курсор установлен на ячейке А2, то в строке формул отображается записанная в ячейке А2 формула для нахождения корня квадратного трехчлена. Вообще в строке формул отражается содержимое ячейки, в которой расположен курсор.

В приведенных формулах используется функция КОРЕНЬ, которая вычисляет квадратный корень из числа. В данном случае будет извлечен квадратный корень из числа, получившегося в результате вычисления выражения:

В1*В1*-4*А1*С1.

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

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

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

Все функции используют одинаковые основные правила синтаксиса. Если нарушить правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.

Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как обычно в начале формулы.

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

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

Если у функции нет аргументов. Примерами таких функций являются ПИ, которая возвращает число, или функция СЕГОДНЯ, возвращающая текущую дату. При вводе таких функций нужно сразу после названия функции поставить круглые скобки. Если хотите получить в ячейке число р или текущую дату, то введите в ячейки формулы следующего вида:

=ПИ()

=СЕГОДНЯ()

Для того чтобы вычислить значение функции, нужно ввести в ячейку знак равенства, а затем название ее аргументов.

В формулах можно использовать операции сложения «+», вычитания «-», умножения «*», деления «/», возведения в степень «^». Также можно использовать знак взятия процента «%», скобки «(», «)». При записи времени используется символ двоеточия «:».

Кроме того, в произвольное место формулы можно с помощью кнопки вставка функции вставить любую из многочисленных функций Exscel.

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

Для внесения изменений в формулу необходимо щелкнуть мышью в строке формул или нажать клавишу F2. Затем внести изменения и нажать кнопку ввода в строке формул или клавишу Enter. Если необходимо внести изменения в формулу непосредственно в той ячейке, где она записана, то дважды нажав мышью на ячейку с этой формулой.

Для отмены изменения необходимо выбрать кнопку отмены в строке формул или нажать клавишу Esc.

Значение формул выводится в числовом формате ячейки, содержащей формулу. Если формат числа для этой ячейки не установлен, значение формулы выводится в числовом формате первой из ячеек, на которые ссылается формула. Например, если для ячейки содержащей формулу «=А1+А2», не установлен формат числа, значение формулы выводится в числовом формате, установленном для ячейки А1.

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

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

Ссылки на ячейки используют заголовки соответствующих строк и столбцов рабочего листа.

В Excel столбцы помечены латинскими буквами (A, B, C,D,…, Z; AA, AB, AC, AD,…, AZ; BA, BB, BC), а строки пронумерованы. Такие ссылки называются ссылками типа А1.

Можно заменить буквы в заголовках столбцов на номера. Такой тип ссылок называется R1C1. Ссылка на активную ячейку выводится на экран в области ссылок, расположенной слева от строки формул.

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

Обратиться к Мастеру функций можно:

1. при помощи пункта меню Вставка/Функция;

2. комбинацией клавиш [Shift] - [F3];

3. кнопкой на панели инструментов:

После вызова Мастера функций появляется первое окно диалога, в котором отображаются категории и имена функций, строка формул и кнопки. Функции перечислены по категориям, таким как: «Финансовые», «Математические», «Статистические». При перемещении по списку категорий, список имен функций справа меняется. Выбор категорий подтверждается щелчком мыши на соответствующей строке. При выборе функции из списка в правой части диалогового окна, в строке формул автоматически появляется определение функции, а также правильно расположенные скобки и точки с запятой, разделяющие аргументы функции.

После выбора нужной функции и нажатии на кнопку Шаг> происходит переход ко второму окну диалога. На этом шаге производится ввод аргумента выбранной функции с клавиатуры в специальное окно ввода, где находится курсор в виде вертикальной черты. Интервал клеток можно также выделить непосредственно на рабочем листе, тогда он автоматически заносится в окно ввода. После нажатия кнопки Закончить, Мастер помещает эти данные в строку формул рабочего листа таблицы, а в клетке появляется результат вычисления по заданной функции.

После всех этих функций производится форматирование: шрифтовое и цветовое оформление; выравнивание данных в клетках; установку формата ввода чисел. Сохранение и закрытие документа.

2. Каким образом можно присвоить имя диапазону ячеек в ТП Excel.

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

Для создания имени для некоторой ячейки или диапазона ячеек нужно выделить ячейку или диапазон и выбрать Вставка - Имя - Присвоить, и в окне, которое появилось, ввести имя диапазона.

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

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

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

Воспользовавшись функцией связывания, вы добьетесь того, что при изменении названия позиции товара на первом рабочем листе все изменения будут отображены на других рабочих листах. Для связывания ячеек нужно выделить диапазон ячеек и скопировать его в буфер. Затем нужно перейти в рабочую книгу или в рабочий лист, куда будет происходить вставка, выделить такой же по размерам диапазон и выбрать Правка - Специальная вставка - нажать кнопку Вставить связь. После этого будет видно как содержимое ячеек вставилось в новое место. Однако ячейки были вставлены без какого-либо форматирования. Для того, чтобы скопировать и установленные форматы, повторно выбираем Правка - Специальная вставка, помечаем переключатель Форматы и нажимаем ОК.

Имя - это легко запоминающийся идентификатор, который можно использовать для ссылки на ячейку, группу ячеек, зничение или формулу. Создать имя для ячейки можно в поле имени, или через меню Вставка - Имя - Присвоить … Использование имен обеспечивает следующие преимущества: формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки. Например, формула «=Активы-Пасивы» гораздо понятнее чем формула «F6-D6».

При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте - в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.

После того как имя определено, оно может использоваться в любом месте рабочей книги. Доступ ко всем именам из любого рабочего листа можно получить с помощью окна имени в левой части строки формул.

Так же можно определить специальные имена, диапазон действия которых ограничивается текущим рабочим листом. Это означает, что эти имена можно использовать лишь на том рабочем листе, на котором они определены. Такие имена не отображаются в окне имени строки формул или окне диалога «Присвоить имя», если активен другой рабочий лист книги.

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

Например, определив имя «Прибыли» как «=$F$12», можно заменить все ссылки на ячейку $F$12 именем «Прибыль».

Быстро перейти на поименованную ссылку, заменить ссылки, вставить ссылку в формулу с помощью окна имени в строке формул.

Заголовок столбца служит не только для обозначения столбца, но и для выделения всего столбца и изменения его ширины.

Заголовок строки выполняет аналогичные функции для строк. На пересечении строки и столбца располагается основной структурный элемент таблицы - ячейки (клетка).

Ячейка - область электронной таблицы, находящаяся на пересечении столбца и строки, это наименьшая структурная единица на рабочем листе.

Формат и размеры ячеек - ширину столбцов и высоту строк - можно изменять с помощью команд меню (иногда кнопок на панели инструментов), а также вручную - с помощью мыши или клавиш. Текущая (активная) ячейка - ячейка, в которой в данный момент находится курсор . Она выделяется на экране жирной черной рамкой. Для выделения любой ячейки достаточно щелкнуть по ней мышью: В активную ячейку можно вводить данные и производить данные и производить над ней различные операции.

Каждая конкретная ячейка таблицы имеет адрес, который используется для указания на ячейку - при ссылке на нее, например А1.

Ссылка - способ (формат) указания адреса (имени) ячейки. Адрес и содержимое текущей ячейки выводится в строке формул электронной таблицы.

Адреса ячеек (ссылки) могут быть относительными и абсолютными. Ячейки могут иметь собственные имена.

Ссылки на ячейку (адреса ячеек) используют в формулах и функциях в качестве аргументов. При выполнении вычислений на место ссылки вставляется значение, находящееся в ячейке, на которую указывает ссылка.

Типичными установками, принимаемыми по умолчанию на уровне всех ячеек таблицы, являются:

- ширина ячейки - около 8 разрядов, высота - около 12 пунктов;

- левое выравнивание для символьных данных;

- основной формат для цифровых данных с выравниванием вправо.

Каждая команда электронной таблицы требует указания адреса ячейки: или блока (диапазона) ячеек, в отношении которых она должна была быть выполнена.

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

Блок используемых ячеек может быть указан или выделен двумя путями:

- непосредственным набором с клавиатуры начального и конечного адресов ячеек, формирующих диапазон;

- выделением блока с помощью мыши или клавиш управления курсором.

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

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

Например, формула «=Сумма+НДС» гораздо понятнее, чем формула «F5+G5».

При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте - в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.

После того как имя определено, оно может использоваться в любом месте рабочей книги. Доступ ко всем именам из любого рабочего листа можно получить с помощью окна имени в левой части строки формул. Можно также определить специальные имена, диапазон действия которых ограничивается текущим рабочим листом. Это означает, что эти имена можно использовать лишь на рабочем листе, на котором они определены. Такие имена не отображаются в окне имени строки формул или окне диалога «Присвоить имя», если активен другой лист книги.

Excel автоматически создает имена на основе заголовков строк и столбцов рабочего листа. После того, как имя определено можно: заменить все соответствующие ссылки этим именем во всех местах рабочего листа. Определив имя «Платежи» как «$F$12», можно заменить все ссылки на ячейку $F$12 с именем «Платежи». После того как формула введена в ячейку, вы можете ее перенести, скопировать или распространить на блок ячеек.

Иногда в формулах нужно использовать ссылки, в которых абсолютной является только часть ссылки - либо на имя столбца, либо на номер строки. Например, $F5 или A$10. Такие ссылки называются смешанными. При копировании формулы со смешанной ссылкой в другую ячейку абсолютная часть ссылки остается постоянной, а относительная часть меняется относительно нового положения.

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

Первый: ввести знак $ с клавиатуры;

Второй: установить курсор в формуле после ссылки на ячейку и нажать клавишу F4. При повторном нажатии на эту клавишу циклически появляются ссылки, например, для А4,$A$4,A4$4,$A4.A4.

Присвоение названий ячейкам с первоначальными значениями производится посредством выделения смежных колонок с названиями и значениями, затем выбрать команду Вставка - Имя - Создать. В диалоговом окне Создать имена установить флажок в столбце с лева нажать кнопку ОК.

1. Целевая функция. Под переменными ввести название Целевая функция и в соседней ячейке задать формулу целевой функции.

2. Ограничения. Даются краткие названия используемых ограничений.

3. Формула. Задаются формулы, описывающие ограничения.

4. Константа (Свободный член). Значение, набранное в этом столбце, показывает максимальное или минимальное значение, которое может принимать формула.