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

• Название: Использование различных возможностей электронных таблиц для выполнения различных операции над содержимым ячеек
• Дата: 30.03.2022
• Размер: 1.77 Mb.
• Имя файла: 3 работа.docx
• Тип: Практическая работа; #429533
• страница: 2 из 4

Практическая работа №2

Тема: Простые расчеты в Excel. Использование различных форматов чисел

Цель занятия: выработать практические навыки использования основных операций с формулами Excel.

Необходимое оборудование: сеть Интернет, ПК.

Теоретическая часть

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

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

При установленном общем формате действуют следующие правила ввода данных:

1. 
   текст автоматически выравнивается по левому краю, а числа – по правому;
   
2. 
   для разделения целой и дробной части числа используется запятая;
   
3. 
   при вводе чисел через точку, тире или знак / они рассматриваются как даты (если это возможно);
   
4. 
   при вводе чисел через двоеточие они рассматриваются как значения времени;
   
5. 
   если к числу добавить знак %, оно будет представлено в процентном формате;
   
6. 
   числа в виде обычной дроби, например 2/3, следует вводить так: 0 2/3 (нужно обязательно указать целую часть числа, даже если она равна нулю, а также поставить пробел между целой и дробной частью).
   

Откройте таблицу Товары

1. 
   Выделите ячейку или диапазон, где нужно сменить формат (см рис).
   
2. 
   На вкладке Главная в группе Число раскройте список Числовой формат и выберите нужный вариант форматирования (Или через Контекстное меню–Формат ячеек–Вкладка Число– Денежный–Число десятичных знаков -0).
   

ПРИМЕЧАНИЕ

При переводе даты в числовой формат Excel отобразит число, соответствующее количеству дней, прошедших с 31.12.1899.
В группе Число вкладки Главная также имеется несколько кнопок для оперативной смены представления числовых данных. Эти кнопки имеют следующее назначение.

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

Процентный формат. Число будет умножено на 100, и к нему будет добавлен символ %. Формат с разделителем. Аналогичен денежному формату, но без знака денежной единицы. Увеличить разрядность. Увеличивает количество десятичных разрядов в дробной части числа Уменьшить разрядность. Уменьшает количество десятичных разрядов в дробной части числа

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

Как уже отмечалось выше, основное назначение программы Excel – это выполнение расчетов, для чего в ячейки таблиц нужно вводить формулы. Формула – это выражение, по которому Excel выполняет вычисления и отображает результат. При создании формул следует руководствоваться правилами:

формула всегда начинается со знака =;

в формулах используются следующие знаки арифметических действий:

+ сложение;

– вычитание;

• 
  умножение;
  

/ деление;

% процент;

^ возведение в степень;

• 
  в формулах можно использовать числа, ссылки на ячейки и диапазоны, а также встроенные функции;
  
• 
  формула всегда записывается в одну строку; для записи сложных формул используются круглые скобки, которыми устанавливается порядок проведения вычислений; для удобства работы с длинными формулами выполняйте ввод не в ячейку, а в строку формул; очень длинные формулы будут автоматически разбиваться в строке формул на несколько строк.
  
• 
  формулы можно вводить как вручную, так и с использованием различных средств автоматизации. Ручной ввод формул трудоемок и ненадежен, поскольку в этом случае легко ошибиться в имени ячейки или названии функции. Несмотря на указанные недостатки, этот способ полезен при вводе простых формул или внесении небольших изменений в уже введенные формулы.
  

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

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

кнопкой Вставить функцию (fx) в строке формул; в появившемся окне Мастера функций нужно выбрать название функции из списка и указать ее аргументы;

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

Использование кнопки Автосумма

Для быстрого суммирования диапазона ячеек служит кнопка Автосумма, которая включает в себя и другие формулы: Сумма, Среднее, Число, Максимум, Минимум Другие функции.

Практическая часть

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

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

Последовательность выполнения

1. 
   Создайте новую книгу.
   
2. 
   В первую строку введите заголовок таблицы, а во вторую – ее «шапку». При необходимости увеличьте ширину столбцов, перемещая их границы в области заголовков столбцов. Заполните таблицу исходными данными, как показано на рис.
   

3. 
   Для расчета начисленной зарплаты следует умножить ставку на количество часов.
   
4. 
   Для ввода формул в остальные ячейки столбца D воспользуйтесь автозаполнением:
   
5. 
   Введите формулу в ячейку E3. По условию задачи ставка налога равна 15 % от начисленной зарплаты, поэтому формула должна иметь вид: =D3*15%.
   
6. 
   С помощью автозаполнения введите формулы в остальные ячейки столбца E.
   
7. 
   Формула в ячейке F3 будет иметь вид: =D3-E3, заполнить другие ячейки столбца F.
   
8. 
   Для вычисления суммарного количества отработанных часов сделайте активной ячейку C7, нажмите кнопку Автосумма, убедитесь в том, что Excel правильно определил диапазон суммирования, и нажмите клавишу Enter.
   
9. 
   Для расчета остальных сумм сделайте активной ячейку C7 и выполните автозаполнение вправо.
   
10. 
    Для расчета количества часов в среднем на одного сотрудника выполните следующие действия:
    
    1. 
       сделайте активной ячейку C8;
       
    2. 
       нажмите стрелку рядом с кнопкой Автосумма и выполните команду Среднее;
       
    3. 
       Excel автоматически введет нужную формулу, но диапазон может быть определен неправильно, поскольку вам не нужно включать в него ячейку C7;
       

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

4. 
   нажмите клавишу Enter и проверьте результат вычислений.
   
1. 
   Выполните форматирование таблицы:
   
   1. 
      для изменения шрифта в ячейке выделите ее и воспользуйтесь кнопками группы Шрифт вкладки Главная;
      
   2. 
      для всех ячеек с денежными значениями установите Формат с разделителем с помощью соответствующей кнопки в группе Число; если в некоторых ячейках вместо цифр появятся символы #####, следует увеличить ширину соответствующих столбцов;
      
   3. 
      для ускорения работы можно изменять форматирование сразу нескольких ячеек, предварительно выделив их; результат форматирования таблицы может быть таким, как показано на рис.
      

12. 
    Сохраните книгу под именем Зарплата.
    

Расчеты с использованием функций и имен ячеек. Типы ссылок. Имена ячеек

1. 
   Ссылки на ячейки и диапазоны могут быть относительными и абсолютными. До сих пор в формулах использовались относительные ссылки, в которых обозначение ячейки состоит из буквы и цифры. Относительные ссылки имеют полезное свойство: при автозаполнении или копировании формул в соседние ячейки имеющиеся в формуле ссылки будут автоматически изменяться. Это позволяет создавать формулы только для одной строки, а для остальных пользоваться автозаполнением, что вы делали в предыдущем уроке при расчете заработной платы.
   
2. 
   Чтобы ссылка стала абсолютной, к обозначениям строки и столбца необходимо добавить знак доллара ($). Например, ссылка A2 – относительная, а ссылка $A$2 – абсолютная. Абсолютная ссылка не изменяется при копировании или заполнении формул. Особым вариантом являются смешанные ссылки, в которых абсолютным является только столбец или строка, например $A2 или A$2.
   
3. 
   После ввода формулы можно оперативно изменить ее тип, нажав клавишу F4.
   
4. 
   Вместо абсолютных ссылок лучше использовать имена ячеек. Любой ячейке или диапазону можно присвоить уникальное имя, которое затем использовать в формулах. Для присвоения имени выделите нужную ячейку, введите новое имя вместо адреса в левой части строки формул и обязательно нажмите клавишу Enter. Имена позволяют сделать формулу нагляднее и получить эффект, аналогичный использованию абсолютных ссылок.
   
5. 
   Иногда может понадобиться удалить или изменить присвоенное имя. Для этого на вкладке Формулы нажмите кнопку Диспетчер имен, в появившемся окне из списка выберите нужное имя и нажмите кнопку Удалить или Изменить.
   

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

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

Последовательность выполнения

1. 
   Создайте таблицу с исходными данными (см. рис.).
   
2. 
   В ячейку С5 введите формулу =В5*В2
   
3. 
   Чтобы ячейка В2 стала абсолютной нажмите клавишу F4, появится формула В5*$В$2– Enter
   
4. 
   С помощью автозаполнения введите формулы в остальные ячейки столбца С.
   
5. 
   Сохраните книгу под именем Прайс-лист.
   

Задание 3. Выполните вычисления с помощью имени ячейки:

1. 
   Создайте таблицу с исходными данными (см. рис.).
   
2. 
   Присвойте имя ячейке В2. Для этого:
   
   1. 
      сделайте ее активной;
      
   2. 
      щелкните кнопкой мыши в поле адреса в левой части строки формул;
      
   3. 
      введите новое имя, например Курс(рис.);
      
   4. 
      нажмите клавишу Enter.
      
3. 
   Создайте формулу для расчета рублевой цены в ячейке С5. Формула должна иметь вид
   

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

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

Задание 4. Создайте еще одну таблицу начисления зарплаты, которая рассчитывается следующим образом:

1. 
   для каждого сотрудника установлена месячная ставка, которую он получит, если отработает установленное количество рабочих дней;
   
2. 
   если сотрудник отработал меньше дней, чем положено, то его зарплата рассчитывается так: =ставка*отработано_дней/рабочих_дней_в_месяце;
   
3. 
   сумму налогов примите равной 13 %;
   
4. 
   сумма к выдаче вычисляется как разность ячеек Начислено и Налоги;
   
5. 
   сумму к выдаче для каждого сотрудника нужно перевести в доллары по курсу 35,5. Пример таблицы показан на рис. В ячейки диапазона D5:G9 следует ввести соответствующие формулы
   

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

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

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

1. 
   Постройте таблицу, содержащую сведения о стоимости туристических путёвок в разные страны мира. Необходимо указать цену путёвок в долларах, затем применяя абсолютную адресацию привести в цену в рублях. Курс доллара возьмите 25,6 руб.
   
2. 
   Постройте таблицу в которой рассчитайте, сколько денег заработал студент, если продавал газеты в течении недели и один экземпляр газеты стоит 12 руб.
   
3. 
   В сельскохозяйственном кооперативе работают 10 рабочих. Оплата труда производится по количеству собранных овощей (в кг). 1 кг овощей стоит 20 руб. Составьте таблицу, содержащую сведения о количестве собранных овощей каждым рабочим и об оплате рабочих.
   
4. 
   3 группы отправилась в путешествие. 1 группа проплыла 150 км на теплоходе, проехала 140 км на поезде и пролетела 100 км на самолете. 2 группа проплыла на теплоходе 100 км и проехала на поезде 120 км. 3 группа проехала на поезде 130 км и пролетела 200 км на самолете. Стоимость 1км на поезде – 50 руб., 30 руб. – на теплоходе и 100 руб. – на самолете. Сколько денег заплатила каждая группа за каждый вид транспорта и за все виды транспорта и все группы?
   

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

1. 
   Как в Excel представляются и обрабатываются числа?
   
2. 
   Какие существуют особенности ввода данных при использовании общего формата?
   
3. 
   Как изменять представление чисел в ячейках?
   
4. 
   Что такое формула? Каковы правила создания формул?
   
5. 
   Когда нужно использовать автозаполнение формул? Какова последовательность выполнения этой операции?
   
6. 
   Что такое относительная и абсолютная адресация ячеек?