Задания для лабораторных работ (для всех вариантов)

1. Используя процедуру автозаполнения, создать нарастающий и убывающий числовые ряды типа

• арифметической прогрессии,

• геометрической прогрессии,

• даты (с изменением по дням, месяцам, годам),

• времени (с изменением по часам, минутам, секундам), и текстовые ряды типа

• «слово_число»,

• «число_слово» (см. ниже п.6).

Там, где это возможно, получить результат по одному первому члену ряда, применяя маркер заполнения.

2. Сформировать из введенных данных таблицы и отформатировать их по образцу, приведенному в данном разделе.

3. В сформированной таблице поменять местами какие-либо две строки и два столбца.

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

5. Создать новый пользовательский список.

6. Введите в какую-либо ячейку текст типа “слово_число”, “число_слово” и “число_число” по образцу:

квартира1 этаж1 (“слово_число”)

квартира 1 этаж1

квартира1 этаж 1

квартира 1 этаж 1

1-я квартира этаж1 (“число_число”)

1 -я квартира этаж1

1квартира этаж 1

1 квартира этаж 1

1-я квартира 1-й этаж (“число_слово”)

1 -я квартира 1-й этаж

и т.п.

Обратите внимание на расстановку пробелов между “числовой” и “текстовой” компонентами этих текстовых строк. (Номера квартир и этажей расставьте по собственному вкусу). В процедуре автозаполнения используйте действие ПиО_ЛКМ + маркер заполнения в различных направлениях (вправо, влево, вверх, вниз). Какие продолжения текстовых рядов при этом возникают в соседних ячейках? Убедитесь в том, что в текстовых строках типа “число_число” приоритет влияния на продолжения рядов данных имеет “числовая компонента” в первом “числе”, если она отделена от “текстовой компоненты” (этого первого “числа”) пробелом или знаком тире, в противном случае приоритет влияния на продолжение рядов данных закрепляется за “числовой компонентой” второго “числа”. Проведите аналогичные эксперименты в ситуации, когда соответствующие словосочетания, содержащие числа, введены в две соседние ячейки (при этом выделяйте обе эти ячейки и применяйте действие ПиО_ЛКМ + маркер заполнения в направлениях вправо или влево, вверх или вниз).

7. Элементарные приемы работы с формулами и функциями

Основные определения

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

Синтаксис формулы – это характер упорядочения входящих в нее элементов и порядок их обработки (последовательность выполнения действий). Любая формула Ехсеl включает знак равенства (с которого всегда должен начинаться ввод формулы), операнды – обрабатываемые (вычисляемые) элементы и операторы – производимые над элементами действия. Операндами могут быть: константы, ссылки или диапазоны ссылок, заголовки, имена или функции. Операторы обладают свойством приоритетности. Это означает, что те из них, которые имеют более высокий приоритет, выполняются раньше тех, которые имеют более низкий приоритет. О конкретном смысле операндов и операторов речь пойдет ниже.

Функция –это стандартная встроенная в Ехсеl формула, которая возвращает результат выполнения определенных действий над своими элементами, выступающими в качестве ее аргументов. Функции используются для упрощения сложных вычислений. Аргументами функции могут являться константы, ссылки на ячейки или диапазоны, имена, текстовые строки и другие (вложенные) функции. Просмотреть список встроенных функций (весьма значительный!) можно, выполнив !ЛКМ по кнопке <Вставка функции>.

Пример 7.1. Цель: Ввести в ячейку А1 формулу = 5+2*3 (звездочка (*) – оператор умножения чисел в Ехсеl) и напомнить понятие приоритета арифметических операций.

Результат:

Действия:

1. Выделение ячейки А1.

2. Ввод с клавиатуры знака = ^.

3. Ввод с клавиатуры остальной части формулы.

4. Фиксация ввода клавишей <Enter>.

Обсуждение: По умолчанию Excel вычисляет формулу слева направо, начиная со знака равенства (=). Синтаксис написания формулы управляет процессом ее вычисления. Введенная в ячейку А1 формула

при выделении ячейки отображается в строке формул, а результат вычисления, т.е. число 11 отображается в самой ячейке. Эта формула возвращает число 11, так как умножение имеет (как это принято в арифметике) более высокий приоритет над сложением и поэтому выполняется в первую очередь: сначала происходит умножение 2 на 3 (результат умножения равен 6), а затем полученное значение складывается с 5. Если для изменения синтаксиса воспользоваться скобками, например, так =(5+2)*3, то сначала произойдет сложение 5 и 2, а затем умножение полученного результата на 3. Последняя формула возвращает число 21. Ниже представлено отображение ячейки А1 в этом случае, соответственно, до и после фиксации ввода:

, .

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

Совет: Одну и ту же формулу можно ввести сразу в несколько ячеек. Для этого необходимо выделить ячейки, ввести формулу, а затем нажать клавиши <Ctrl> + <Enter> .

Применение операторов и порядок выполнения действий в формулах

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

Таблица 7.1

Операторы Ехсеl и их приоритет

Оператор	Описание
: (двоеточие) ; (точка с запятой) (пробел)	Операторы ссылок (адресные операторы)
–	Унарный минус
%	Процент
^	Возведение в степень
* и /	Умножение и деление
+ и –	Сложение и вычитание
&	Объединение последовательностей символов в одну последовательность
Операторы сравнения	= < > <= >= <>

Верхним строкам Табл.7.1 отвечает более высокий приоритет операторов, а расположенным ниже – соответственно более низкий. Операторы, расположенные в одной строке таблицы, имеют одинаковый приоритет. Если формула содержит операторы с одинаковым приоритетом, например, формула содержит операторы умножения и деления, то Excel обрабатывает операторы слева направо. Как уже говорилось выше, чтобы изменить порядок обработки операторов, нужно расставить круглые скобки в соответствующих позициях формулы. В ряде случаев расстановка круглых скобок непринципиальна, например, две формулы =P1:Y1^(1/2) и =(P1:Y1)^(1/2) (с которыми мы встретимся ниже) приведут к одному и тому же результату, поскольку оператор диапазона (двоеточие) имеет более высокий приоритет, чем оператор возведения в степень. Однако формула = P1:Y1^1/2 возвратит иной результат в сравнении с написанными выше: в формулах = P1:Y1^(1/2) и =(P1:Y1)^(1/2) из каждого элемента диапазона P1:Y1 извлекается квадратный корень, а в формуле = P1:Y1^1/2 каждый элемент этого диапазона возводится в степень 1 (т.е. не изменяется), а затем делится пополам. Указанное различие в возвращаемых значениях сравниваемых формул существенно связано со свойством приоритетности операторов: : , ^ , /.

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

Обратим внимание на различие операторов Унарный_минус и Вычитание. Например, в формулах = -А1 , = -А1-В1 первый минус это Унарный_минус, а в формулах = -А1-В1, = А1-В1 второй минус обозначает Вычитание. С использованием унарного минуса в формулах Ехсеl могут быть связаны неожидаемые результаты (и даже неожиданные ошибки). Так, если пользоваться правилами арифметики, то мы ожидаем, что формула = -(-1)^2 возвратит значение, равное -1 , а на самом деле в Ехсеl это не так: результат будет равен 1 (еще раз внимательно посмотрите на Таблицу 7.1 приоритетов операторов!). Поэтому рекомендация: либо вообще не пользоваться оператором Унарный_минус, либо применять его с осторожностью.

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

Адресные операторы действуют на диапазоны ячеек, которые в дальнейшем обрабатываются иными операторами Ехсеl (см. Табл.7.2).

Арифметические операторы (операции) используются для выполнения вычислений над числами (см. Табл.7.3). Результатом выполнения арифметических операций всегда являются числа.

Операторы сравнения (см. Табл. 7.4) используются для обозначения операций сравнения двух операндов (обычно чисел, но это необязательно). Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

Таблица 7.2

Адресные операторы Ехсеl

Адресный оператор	Название и назначение	Пример
: (двоеточие)	Оператор диапазона (ссылается на все ячейки между границами диапазона включительно).	B5:B15 (Это часть столбца В, включающая все ячейки, начиная с пятой строки и заканчивая пятнадцатой)
(пробел)	Оператор пересечения (ссылается на общие ячейки диапазонов).	B5:B15 A7:D7 (В этом диапазоне, содержится единственная ячейка B7, являющаяся общей для двух диапазонов B5:B15 и A7:D7)
; (точка с запятой)	Оператор объединения (ссылается на объединение ячеек диапазонов).	B5:B15;А7:Е7 (в формулах общие ячейки отдельных диапазонов (например, в данном случае ячейка В7) учитываются столько раз, сколько раз они входят в эти диапазоны)

Таблица 7.3

Арифметические и текстовые операторы Ехсеl

Арифметические и текстовые операторы	Название	Пример
+ (знак плюс)	Сложение	= 3+3
– (знак минус)	Вычитание	= 3–1
– (знак минус)	Унарный минус	= –1
* (звездочка)	Умножение	= 3*3
/ (косая черта)	Деление	= 3/3
% (знак процента)	Процент	= 20%
^ (крышка)	Возведение в степень	= 3^(1/2)
& (амперсант)	Текстовый оператор «&»	используется для объединения последовательностей символов в единую последовательность

Таблица 7.4

Операторы сравнения Ехсеl

Оператор сравнения	Название	Пример
= (знак равенства	Равно	=A1=B1
> (знак больше	Больше	=A1>B1
< (знак меньше)	Меньше	=A1<B1
>= (знак больше и знак равенства)	Больше или равно	=A1>=B1
<= (знак меньше и знак равенства)	Меньше или равно	=A1<=B1
<> (знак больше и знак меньше)	Не равно	=A1<>B1

Вычисление значений в формулах, ссылающихся на ячейки

Формула является основным средством анализа данных. С помощью формул можно складывать, умножать, сравнивать, объединять данные и выполнять прочие операции над данными (при условии, что смысл операции соответствует типу данных, например, складывать можно, как правило, данные типа Число, но не типа Текст). Формулы могут ссылаться на ячейки текущего листа (используя тип ссылки =А1, что означает: в данную ячейку введена формула, которая ссылается на ячейку того же листа, где расположена данная ячейка), других листов той же книги (используя тип ссылки =Лист1!A1, что означает: данная ячейка содержит формулу, ссылающуюся на ячейку с именем А1, которая располагается на листе с именем Лист1; при этом имена отсылочных листа и ячейки разделены знаком !) или других книг (в этом случае используется тип ссылки =[Книга1]Лист1!$A$1, что означает: данная ячейка содержит формулу, ссылающуюся на ячейку с именем А1, которая располагается на листе с именем Лист1 книги с именем Книга1; при этом имена отсылочных книги, листа и ячейки разделены соответствующими знаками (см. синтаксис формулы)^).

Если необходимо, чтобы ячейка, в которую вводятся данные, ссылалась на значение некоторой другой ячейки, нужно в процессе ввода формулы в данную ячейку выполнить !ЛКМ по этой другой ячейке или набрать ее имя (в «латинице»!, если это имя типа имени по умолчанию ,т.е. типа А1, $A1, Лист1!A1 и т.п.) на клавиатуре (говорят, что данная ячейка ссылается на другую ячейку). В этом случае ячейка, содержащая формулу, называется зависимой ячейкой, т.к. ее значение зависит от значения другой ячейки, а последняя называется влияющей, т.к. ее значение влияет на значение зависимой ячейки. Формула, находящаяся в зависимой ячейке, как правило, возвращает иное значение по сравнению с исходным, если изменяется значение влияющей ячейки.

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

Для ввода в формулу встроенной в Ехсеl функции или для ее изменения удобно пользоваться Мастером функций или, по-другому, панелью формул/функций. После вставки функции в строку формул там отображается ее имя, аргументы, синтаксис, а также возвращаемое функцией значение. При работе с Мастером функций следует руководствоваться следующими общими правилами:

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

2. Чтобы начать ввод формулы с функции, необходимо нажать кнопку <Изменить формулу> в строке формул.

3. Далее, либо нажать кнопку <Вставка функции> на панели инструментов Стандартная, либо нажать кнопку <Стрелка вниз>, расположенную справа от поля имен (которое при вводе формулы заменяется на поле Функции) в левой части строки формул.

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

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

6. По завершении ввода функции нажать кнопку <ОК> в диалоговом окне для дальнейшего набора формулы или сразу нажать клавишу <Enter> для фиксации ввода формулы.

7. Панель формул можно использовать для изменения функции в формуле. Чтобы отобразить панель формул, нужно выделить ячейку, содержащую формулу с функцией, и нажать кнопку <Изменить формулу>. Сразу отобразится диалоговое окно с первой функцией формулы, содержащее ее поля со всеми аргументами. Изменение ЛЮБОЙ ДРУГОЙ функции, входящей в формулу, производится путем ПОЗИЦИОНИРОВАНИЯ КУРСОРА ВВОДА в строке формул НА ЛЮБОЙ ЧАСТИ ЕЕ ИМЕНИ с последующим исполнением !ЛКМ по кнопке <Изменить формулу>.

8. Для очистки ячейки от формулы нужно выделить ячейку, ее содержащую, и нажать клавишу <Delete>.