1008-1

61

Рис. 41. Результат расчета элементов треугольника

Операции перемещения и копирования содержимого ячеек

Данные электронной таблицы можно перемещать, копировать или уда-

лять.

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

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

2.На вкладке Главная в группе Буфер обмена нажать кнопку Вырезать или в контекстном меню выбрать команду Вырезать. Указанные данные будут выделены с помощью бегущей рамки.

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

4.Нажать <Enter>, или на вкладке Главная в группе Буфер обмена нажать

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

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

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

2.На вкладке Главная в группе Буфер обмена нажать кнопку Копировать

или в контекстном меню выбрать команду Копировать. Указанные данные будут выделены с помощью бегущей рамки.

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

62

4. Нажать <Enter>, или на вкладке Главная в группе Буфер обмена нажать

кнопку , или в контекстном меню выбрать команду Вставить. Программа скопирует указанные данные.

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

1.Выделить ячейку или копируемый блок.

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

Формулы также можно копировать в другие ячейки. При этом в зависи-

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

относительные ссылки (А2 или С23) – основаны на относительной позиции ячейки, содержащей формулу, и ячейке, на которую указывает ссылка. При копировании и перемещении ячейки содержащиеся в них имена автоматически перенастраиваются в соответствии с их новым расположением. Например, если поместить в ячейку С1 выражение =А1+В1 и его скопировать в ячейку С2, то в С2 будет помещена формула =А2+В2 (рис. 42).

абсолютные ссылки – в них перед именем столбца и номером строки стоит символ $ ($A$2), и всегда ссылаются на ячейку, расположенную в определенном месте. При копировании абсолютные ссылки остаются неизменными. Если в ячейку D1 поместить формулу =$A$1+$B$1 и ее скопировать в ячейку ниже, то в D1 будет записана та же формула =$A$1+$B$1;

смешанные ссылки (частично абсолютные ссылки) – содержат либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Символ $ стоит или перед именем столбца, или перед номером строки ($A2, А$2). При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не изменяется. Например, при копировании формулы из ячейки Е1, содержащей =$A1+В$1, в Е2 сохранится имя столбца А, а номер строки будет изменен, а во втором слагаемом – должно измениться имя столбца, а номер строки останется таким же. Имя столбца во втором слагаемом не изменяется, так

63

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

Рис. 42. Типы ссылок в формулах

Упражнение 9

1.Щелкните по кнопке , расположенной справа от ряда ярлыков листов. После последнего листа добавится лист с именем Лист3, и он станет активным.

2.Блок ячеек А1:В5 заполните произвольными числами.

3.Введите в ячейки С1:Е1 формулы согласно рис. 42.

4.Скопируйте содержимое ячеек С1:Е1 в ячейки С2:Е5. Проанализируйте результат.

5.В ячейку F1 введите произвольное число. Поэлементно умножьте столбец чисел Е на данное число и результат поместите в ячейки G1:G5. Какую формулу следует ввести в ячейку G1, чтобы можно было ее скопировать в остальные ячейки столбца?

Упражнение 10

Составьте таблицу умножения. Для этого:

1.Вставьте новый лист и назовите его Таблица умножения;

2.В соответствии с рис. 43 введите название таблицы и заполните строчку и столбец с множителями;

3.В ячейку В3 введите формулу =А3*В2 и скопируйте ее растягиванием вправо и сразу, не снимая выделение, вниз. Результат будет неправильный, так как в формуле используются относительные ссылки ячеек.

Проанализируйте формулу =А3*В2:

при ее копировании вправо должна остаться неизменной ссылка на столбец А, т.е. следует записать $A3;

при копировании формулы вниз необходимо зафиксировать ссылку на строку 2, т.е. B$2.

Таким образом, в ячейку В3 следует ввести формулу =$A3*B$2;

4. Скопируйте введенную формулу в диапазон ячеек В3:К12. Убедитесь,

64

что полученный результат правильный.

Рис. 43. Таблица умножения

Упражнение 11

Составьте таблицу начисления заработной платы и расчета подоходного налога в соответствии с рис. 44. Для этого:

1.Вставьте новый лист и назовите его Зарплата.

2.Создайте таблицу в соответствии с рис. 44.

3.В ячейку D5 поместите формулу =C5*$D$2 и скопируйте ее в диапазон ячеек D6:D9. Обратите внимание, что в формуле ставка налога представлена в виде абсолютной ссылки, которая при копировании формулы изменяться не будет, а начисленная зарплата работника представлена с помощью относительной ссылки, и при копировании формулы она будет меняться относительно нового местоположения.

Рис. 44. Таблица для расчета заработной платы

65

4.В ячейку Е5 поместите формулу =C5-D5 и скопируйте ее в ячейки

Е6:Е9.

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

С10 и нажмите на вкладке Формулы кнопку суммирования . В качестве аргументов выделите диапазон С5:С9. Скопируйте формулу из С10 в ячейки D10:E10. Результат сравните с рис. 45.

Рис. 45. Результат расчета

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

1.Какие основные возможности для обработки табличных данных предоставляют пользователю электронные таблицы?

2.Назовите основные элементы окна программы Excel и укажите их функциональное назначение.

3.Как управлять рабочими книгами и рабочими листами?

4.Назовите и охарактеризуйте основные типы данных в ячейках электронной таблицы Excel.

5.Что такое формула в Excel? Как использовать стандартные функции в формулах?

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

7.Расскажите о возможностях форматирования данных в ячейках.

8.Как изменить ширину столбцов и высоту строк?

9.Каковы основные функции мастера автозаполнения?

66

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

ЛАБОРАТОРНАЯ РАБОТА № 4

Тема работы: Логические функции в электронных таблицах Microsoft

Excel.

Логическая функция ЕСЛИ

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

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

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

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

чение ИСТИНА или ЛОЖЬ;

значение если истина – возвращаемое значение, если условие верно. Если не указано, то возвращается значение ИСТИНА;

значение если ложь – возвращаемое значение, если условие ложно. Если не указано, то возвращается значение ЛОЖЬ.

Значение функции ЕСЛИ, записанной в ячейку электронной таблицы, вычисляется следующим образом: если логическое выражение истинно, то значением данной ячейки будет Значение если истина, в противном случае – Значе-

ние если ложь.

Логические выражения строятся с помощью операций сравнения: < (меньше), <= (меньше или равно), > (больше), >= (больше или равно), = (равно), <> (не равно).

Упражнение 1

Создайте смету на производство мебельного изделия (рис. 46), содержащую перечень используемого материала, его количество и стоимость. Некоторые материалы должны быть доставлены с удаленного склада. Для таких товаров в графе Доставка (столбец F) стоит знак «*», а стоимость такого материала

67

возрастает на 10 %. Рассчитайте стоимость мебельного изделия, выполнив следующие действия:

1.Запустите Microsoft Excel. В появившейся рабочей книге переименуйте первый лист, назвав его Смета.

2.Введите в указанные в табл. 5 ячейки текст заголовка и шапки таблицы

всоответствии с рис. 46.

3.Выделите диапазон ячеек А1:H1 и на вкладке Главная в группе Вырав-

нивание нажмите кнопку .

Рис. 46. Исходная таблица для расчета сметы на изделие

Таблица 5 Содержимое ячеек, в которых располагаются название таблицы и ее шапки

68

4. Отформатируйте заголовок таблицы следующим образом:

выделите блок ячеек А5:H5;

в контекстном меню выберите команду Формат ячеек;

в появившемся окне на вкладке Выравнивание задайте параметры: По горизонтали: по центру; По вертикали: по центру;

Переносить по словам: поставьте флажок; нажмите кнопку ОК.

5.Выполните подбор ширины столбцов и высоты строки с номером 5 в соответствии с рис. 46.

6.Заполните столбец с порядковым номером материала, используя автозаполнение.

7.Введите данные в ячейки В6:F5, содержащие сведения о материалах в соответствии с рис. 46, и отформатируйте их.

8.Для более удобного представления итоговой стоимости материалов объедините ячейки А11:F11, выполнив следующее:

выделите ячейки А11:F11;

в контекстном меню выберите команду Формат ячеек;

в появившемся окне на вкладке Выравнивание установите по горизонтали: по правому краю, поставьте флажок Объединение ячеек;

на вкладке Шрифт выделите Полужирный и нажмите кнопку ОК.

9.В объединенную отформатированную ячейку введите Итого.

10.Выделите блок ячеек А5:H11 и на вкладке Главная в группе Шрифт

нажмите на кнопку Все границы , чтобы каждая ячейка блока была окружена рамкой.

11. Для расчета стоимости доставки единицы материала в каждую ячейку столбца G следует ввести формулу, суть которой заключается в следующем: если напротив названия материала в столбце F стоит «*», то в соответствующей ячейке столбца G должно стоять значение, равное 10 % от цены единицы материала, если звездочки нет, то – 0. Для ввода формулы установите курсор в ячейку G6 и на вкладке Формулы ленты инструментов нажмите Логические ЕСЛИ. Появится окно ввода аргументов логической функции ЕСЛИ, в которое введите следующие значения:

Логическое выражение – F6=«*»

Значение, если истина – D6*$C$3

69

Значение, если ложь – 0 и щелкните по кнопке ОК.

Если выделить ячейку G6, то в строке формулы должно быть записано: =ЕСЛИ(F6="*"; D6*$C$3; 0).

12.Скопируйте формулу из ячейки G6 в остальные ячейки столбца.

13.Для расчета суммарной стоимости всех материалов с учетом доставки в ячейку Н6 введите формулу =(D6+G6)*E6 и скопируйте ее в другие ячейки данного столбца.

14.В ячейках G11 и Н11 вычислите итоговую стоимость доставки и мебельного изделия. Результат сравните с рис. 47.

Рис. 47. Результат расчета сметы с учетом доставки материала

Упражнение 2

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

ные соответственно , в случае, когда величина дискри-

минанта больше 0.

Выполнить решение для нескольких наборов коэффициентов:

1)a= 2, b= – 3, c = –2;

2)a = 1, b = 2, c = 10.

Упражнение 3

1. Создайте таблицу, приведенную на рис. 48.

70

Рис. 48. Таблица к упражнению 3

2. Рассчитайте продажную цену каждой книги, исходя из следующего: на закупочную цену вводится наценка. Наценка устанавливается в процентах от закупочной цены книги в зависимости от категории (А, В или С). Так как заданы три типа категории, от которой зависит наценка, то следует использовать вложенную функцию ЕСЛИ. Для этого:

выделите ячейку Е7 и на вкладке Формулы ленты инструментов в сек-

ции Библиотека функций выберите Логические ЕСЛИ. На экране появится окно ввода аргументов данной функции, в котором задайте:

Логическое выражение – С7=«А» Значение если истина – D7+D7*$C$1

Значение если ложь – вновь вызовите функцию ЕСЛИ и задайте аргумен-

ты:

Логическое выражение – С7=«В» Значение если истина – D7+D7*$C$2 Значение если ложь – D7+D7*$C$3

и нажмите ОК;

скопируйте формулу из ячейки Е7 в остальные строки столбца. 3. Вычислите стоимость проданных книг по формуле:

Стоимость = Продажная цена * Количество

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

G11.

Функции СЧЕТЕСЛИ и СУММЕСЛИ

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