Перемещение формул и данных

 

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

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

В EXCEL по умолчанию действуют следующие правила модификации таких ссылок.

1.При перемещении формулы адреса данных, на которые она ссылается, не изменяются.

Например, ячейка А3 содержит формулу =А1+В1. Переместим формулу из ячейки А3 в ячейку F21. При этом содержимое ячейки F21 останется прежним: =А1+В1.

2.При перемещении данных, на которые ссылается формула, адреса в формуле модифицируются по аналогии с относительными адресами.

Например, ячейка А3 содержит формулу =А1+В1. Перенесем содержимое ячейки А1 в ячейку Н17. При этом содержимое ячейки А3 примет вид =Н17+В1.

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

Например, ячейка А3 содержит формулу =$А$1*A2. Перенесем весь диапазон А1:А3, включая и формулу, и данные, на место диапазона В1:В3. При этом содержимое ячейки В3 примет вид =$B$1*B2.

 

ИСПОЛЬЗОВАНИЕ ВСТРОЕННЫХ ФУНКЦИЙ EXCEL

Основные понятия

 

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

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

Некоторые функции выполняют очень простые вычисления. Например, функция =СУММ(D2:D7) суммирует значения в диапазоне D2:D7. Эта запись значительно короче, чем эквивалентная формула =D2+D3+D4+D5+D6+D7. Другие функции выполняют значительно более сложные расчеты (например, вычисление итогового сальдо ряда финансовых операций в проекции на текущий момент или вычисление значений функции Бесселя).

Синтаксис обращения к функциям одинаков и включает два основных компонента:

· имя встроенной функции;

· один или более аргументов, заключенных в скобки.

Аргументы функций разделяются один от другого разделителем, который устанавливается при настройке EXCEL. На всех машинах кафедры ИиП в качестве разделителя установлен знак “;”.

Аргументы функций могут иметь четыре различных типа:

· Условие представляет собой логическое выражение, в которое входят логические операторы (=, >, <, >=, <=, <>) либо адрес ячейки, содержащей такое выражение, либо логическая функция. Значением такого аргумента может являться истина или ложь.

· Ссылка представляет собой обычно адрес ячейки или диапазона.

· Текст – это просто последовательность символов (букв, цифр и других знаков), заключенных в кавычки;

· Значение как аргумент представляет собой число, адрес или имя ячейки, содержащей числовую величину.

Обзор списка встроенных функций

 

В EXCEL используется более 300 встроенных функций, условно разделенных на 10 групп (категорий). Основанием для такого разделения служит область применения каждой встроенной функции.

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

Для более подробного изучений функций следует воспользоваться встроенной помощью (раздел “Создание формул и проверка книг”, подраздел “Функции листа”).

 

 

Функции даты и времен

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

ДЕНЬ(<ссылка на ячейку с датой>)

Возвращает номер дня в месяце.

 

МЕСЯЦ(<ссылка на ячейку с датой>)

Возвращает порядковый номер месяца в году. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь).

 

ГОД(<ссылка на ячейку с датой>)

Возвращает год, соответствующий дате в ячейке. Год определяется как целое в интервале от 1900 до 9999.

 

ДЕНЬНЕД(<ссылка на ячейку с датой>)

Возвращает день недели, соответствующий дате в ячейке. День недели определяется как целое в интервале от 1 (Воскресенье) до 7 (Суббота).

 

СЕГОДНЯ()

Возвращает текущую дату.

 

Примеры:

Пусть в ячейке А1 записана дата 09.05.1945. Тогда:

=ДЕНЬ(А1) возвращает 9

=МЕСЯЦ(А1) возвращает 5

=ГОД(А1) возвращает 1945

=ДЕНЬНЕД(А1) возвращает 4

 

 

Логические функции

 

ЕСЛИ(<логическое выражение>;<значение1>;<значение2>)

Возвращает значение1, если логическое выражение есть ИСТИНА и значение2, если логическое выражение есть ЛОЖЬ.

Пусть, например, нужно вычислить налог с некоторой суммы, которая хранится в ячейке А1, по следующей формуле:

 

налог =

от суммы, если она меньше 120;

от суммы, если она больше либо равна 120.

 

 

Это можно сделать с помощью следующей формулы:

=ЕСЛИ(А1<120 ; А1*0.05; А1*0.15)

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

 

налог =

20 руб., если сумма меньше или равна 100;

7 % от суммы, если она больше 100, но меньше 1000;

20 % от суммы, если она больше либо равна 1000.

 

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

 

=ЕСЛИ(А1<=100;20;ЕСЛИ(А1<1000;A1*0.07;A1*0.20))

=ЕСЛИ(А1<1000;ЕСЛИ(А1<=100;20;A1*0.07);A1*0.20))

=ЕСЛИ(А1>=1000; A1*0.20;ЕСЛИ(А1<=100;20;A1*0.07))

 

И(<логическое выражение1><логическое выражение2>…)

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Примеры:

=И(В2=1;В4>8) возвращает значение ИСТИНА, если в ячейке В2 содержится 1, а в ячейке В4 – число больше 8;

=И(С2:С5) возвращает значение ИСТИНА, если во всех ячейках С2, С3, С4, С5 содержится ИСТИНА.

Предположим, что нужно вывести на экран содержимое ячейки А1, если она содержит число строго между 5 и 20 и сообщение «неверный ввод» в противном случае. Это можно сделать с помощью формулы:

=ЕСЛИ(И(А1>5;A1<20);A1;»неверный ввод»)

 

ИЛИ(<логическое выражение1><логическое выражение2>…)

Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеет значение ЛОЖЬ.

Примеры:

ИЛИ(1+1=2;2+2=5) возвращает ИСТИНА

ИЛИ(1+1=3;2*2=5) возвращает ЛОЖЬ

ИЛИ(А1<=5;A1>=20) возвращает ЛОЖЬ, если ячейка А1 содержит число строго между 5 и 20 и ИСТИНА в противном случае.