Задание 8. Удалить и вставить несколько строк.

Порядок работы.

Чтобы вставить в электронную таблицу целую строку или столбец, нужно проделать следующие шаги:

- в области предполагаемой вставки выделить столько столбцов или строк, сколько предполагается вставить;

- если нужно вставить строки, в меню Вставка выбрать команду Строки. Excel сдвинет выделенные строки на столько же строк вниз. Для вставки столбцов в меню Вставка нужно выбрать команду Столбцы. В этом случае Excel сдвигает столбцы вправо. Либо щелкнуть правой клавишей мыши на диапазоне и в контекстном меню выбрать команду Добавить ячейки.

Для удаления строк или столбцов:

- выделить строки или столбцы для удаления;

- в меню Правка выбрать команду Удалить.

Excel удаляет строки или столбцы и соответственно сдвигает оставшиеся данные.

Задание 9. Занести в ячейку на 1-м листе курс доллара и подсчитать итоговую зарплату каждого человека в долларах, используя ячейку с курсом. Присвоить имя ячейке. Расположить зарплату в долларах в отдельном столбце.

Порядок работы.

Каждой ячейке или диапазону можно присвоить имя длиной до 255 символов. Помимо ограничения по длине, имя диапазона должно отвечать некоторым требованиям:

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

- не рекомендуется применять в именах символы операторов(+,-,*,/,<,>,&), т.к. это может внести путаницу в формулах;

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

Процедура создания имени диапазона включает следующие шаги:

1. Выделить диапазон для именования.

2. В меню Вставка в подменю Имя выбрать команду Присвоить. Откроется окно диалога Присвоение имени .

3. ввести имя диапазона в поле Имя.

4. Нажать кнопку Добавить. Excel добавит имя в список имен.

5. Нажать кнопку Закрыть для возврата в экран листа.

Назначенное имя диапазона будет доступно в любом листе книги.

Присвоим имя курс_доллара ячейке С15. Введем значение курса и отформатируем его Формат - ячейки - денежный - английский (США). Рядом со столбцом Суммарная зарплата за полгода (АВ) в ячейку АС3 введем формулу =АВ3/курс_доллара.

Распространим эту формулу для всех членов списка. Таким образом, мы подсчитали

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

Задание 10. Изменить название листа.

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

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

1. Как объединить ячейки?

2. Как ввести формулу в ячейку?

3. Как разделить таблицу на 4-ре части?

4. Как выполнить сортировку данных одного столбца по убыванию?

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

Цель работы: получить практические навыки по использованию функций ЕСЛИ(), СЧЕТЕСЛИ(), СУММЕСЛИ(), МАКС(), МИН(), СРЗНАЧ(), И(). Ознакомится с работой формул массивов.

Задание 1. Сформировать таблицу вида:

Сведения о сотрудниках предприятия:

Подоходный налог рассчитывается так:

А) Если годовой доход <=12 МРОТ, то 0% (МРОТ - минимальный размер оплаты труда. Его записать в отдельную ячейку вне таблицы - 100 руб.);

Б) Если12*МРОТ< годовой доход<=20000, то 12%;

В) Если 20000<годовой доход<50000, то подоходный налог равен 2400+20% от суммы, превышающей 20000;

Г) Если годовой доход >50000, то подоходный налог =4000+45% от суммы, превышающей 50000.

Порядок работы.

Введем произвольные фамилии (не менее 10). Должности будем выбирать из списка: секретарь, экономист, юрист, инженер, лаборант. Колонку таблицы "Годовой доход" заполним произвольным образом, но с учетом должности. Для заполнения колонки "Подоходный налог" нам придется использовать функцию ЕСЛИ().

В Microsoft Excel определено большое количество стандартных формул, именуемых функциями. Функции используются для различной сложности вычислений и текстовых операций. Функции выполняют вычисления по входным данным (задаваемым величинам) - аргументам - в указанном порядке, в соответствии с правилами синтаксиса. Список аргументов может состоять из чисел, текста, логических величин, массивов, ссылок. Кроме того, аргументы могут быть как константами, так и формулами. Эти формулы в свою очередь, могут содержать другие функции (до 7 уровней вложения). При вводе значений аргументов необходимо следить за соответствием типов аргументов.

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

Общий синтаксис функции следующий:

=Имя_функции(аргумент 1; аргумент 2;…)

Синтаксис функции ЕСЛИ:

=ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)

Функция ЕСЛИ() проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.

В нашем задании функция ЕСЛИ() будет выглядеть следующим образом (предположим, что мы будем вводить ее в ячейку D3):

ЕСЛИ(С3>50000;(С3-50000)*0,45+4000;

ЕСЛИ(С3>20000;(С3-20000)*0,2+2400;

ЕСЛИ(С3>12*МРОТ; С3*0,12; 0)))

Здесь МРОТ - поименованная ячейка, содержащая минимальный размер оплаты труда (см. условие задания).

После правильного ввода функции ЕСЛИ() в ячейку D3 остается размножить эту формулу для оставшихся членов списка (см. рис. 4).

Рис. 4.

Задание 2. На 2-ом листе рассчитать таблицу на основе таблицы задания 1.

Порядок работы.

Для подсчета количества человек, занимающих определенную должность, нам понадобится функция СЧЕТЕСЛИ(). Эта функция подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.

Синтаксис функции следующий:

СЧЕТЕСЛИ(диапазон; критерий)

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

=СЧЕТЕСЛИ(Лист1!В3:В15; "секретарь").

Обратите внимание на тот факт, что диапазон В3:В15 содержит явное указание на то, что он находится на первом листе.

Далее нам необходимо подсчитать "Средний доход" для каждой должности. Здесь нам понадобятся формулы массива.

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

Формула массива создается так же, как и простая формула, но с нюансами:

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

- ввести формулу;

- ввести комбинацию клавиш <CTRL + SHIFT + ENTER>. Excel заключит формулу массива в фигурные скобки.

Для значения Excel может понадобиться выполнить несколько действий. Например, следующая формула вычисляет среднее значение только тех ячеек, принадлежащих диапазону С3:С15 на 1-ом листе для которых в ячейках диапазона В3:В15 содержится слово "секретарь". Для этого используется функция ЕСЛИ, которая в диапазоне В3:В15 находит ячейки, содержащие слово "секретарь", и возвращает значения ячеек в диапазоне С3:С15 только из этих строк функции СРЗНАЧ, которая затем уже вычисляет их среднее значение (см. рис. 5). В данном задании наша формула для секретарей будет выглядеть следующим образом:

{=СРЗНАЧ(ЕСЛИ(Лист1!В3:В15="секретарь"; Лист1!С3:С15))}

Аналогично Среднему доходу производится расчет в колонке Максимальный доход., только вместо функции СРЗНАЧ надо воспользоваться функцией МАКС:

{=МАКС(ЕСЛИ(Лист1!В3:В15="секретарь"; Лист1!С3:С15))}

Здесь опять пришлось воспользоваться формулой массива.

Другой способ получения среднего дохода для каждой должности - использовать функции СУММЕСЛИ() и СЧЕТЕСЛИ(), поделив их результаты.

Обращение к функции СУММЕСЛИ имеет вид:

СУММЕСЛИ (диапазон1; критерий; диапазон_суммирования)

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

=СУММЕСЛИ(ЛИСТ1!В3:В15;"секретарь"; Лист1! С3:С15)/

СЧЕТЕСЛИ(ЛИСТ1!В3:В15;"секретарь").

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

= СУММЕСЛИ(ЛИСТ1!$В$3:$В$15; В2; Лист1! $С$3:$С$15)/

СЧЕТЕСЛИ(ЛИСТ1!$В$3:$В$15; В2).

Копируем эту формулу в ячейки С3 - С6 и получаем средний доход по указанным должностям.

Задание 3. Определить количество инженеров, чей годовой доход превышает 20000.

Порядок работы.

Данное задание выполним в два этапа. Сначала на первом листе добавим столбец, в котором определим инженеров с годовым доходом превышающим 20000. Определим - это значит, поставим 1, если инженер удовлетворяет нашему условию, а всем остальным: инженерам с годовым доходом не превышающим 20000 или не инженерам с годовым доходом превышающим или не превышающим 20000 поставим 0. Делать это мы будем с помощью функции ЕСЛИ:

ЕСЛИ(И($В$3:$В$15="инженер";$С$3:$С$15>20000);1;0).

Рис. 5.

При записи условия функции ЕСЛИ нам понадобилась логическая функция И(логическое значение1; логическое значение2; …) - логическое умножение. Эта функция возвращает значение "истина", если все аргументы имеют значение "истина" и возвращает значение "ложь", если хотя бы один аргумент имеет значение "ложь".

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

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

Воспользуемся функцией СЧЕТЕСЛИ(), считая, что необходимая нам информация находится в ячейках Е3 - Е15:

СЧЕТЕСЛИ(Е3:Е15;"=1").

Страницы: 1, 2, 3