Вставка (удаление) строк и столбцов

В процессе работы иногда возникает необходимость добавить либо строки, либо столбцы. Если надо добавить строки или столбцы в конец таблицы, то проблем не возникает. Но как быть, если вставку надо осуществить в середине таблицы?

Вставка строки производится всегда перед той строкой, в которой находится активная ячейка. Столбец, соответственно вставляется перед тем столбцом, в котором находится активная ячейка.

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

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

При вставке строк и столбцов табличный процессор автоматически изменяет соответствующим образом относительные ссылки, находящиеся в формулах в смещающихся строках и столбцах. Например, произведена вставка 4-й строки. Значит после этой операции содержимое ячеек, составляющих 4-ю строку, перейдет в ячейки 5-й строки. И если в ячейке, к примеру, Е4 находилась формула = D4 – С4, то эта формула окажется в ячейке Е5 и будет автоматически преобразована в формулу = D5 – С5.

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

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

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

6. Функции в электронных таблицах

Табличный процессор Excel содержит порядка 300 встроенных функций. Простейший способ получения полной информации о любой из них заключается в использовании команды главного меню Справка. Для удобства функции в Excel разбиты по категориям (математические, финансовые, статистические, текстовые и т.д.).

Обращение к каждой функции состоит из двух частей: имени функции и аргументов в круглых скобках. Величины, которые используются для вычисления значений функции, называются аргументами. Величины, которые являются результатом вычислений функций, называются возвращаемыми значениями. Чтобы использовать функцию, её нужно ввести как часть формулы в ячейку листа. Аргументы заключаются в скобки, причем, пробелы перед скобками или после них не допустимы. Если в функции используются два и более аргумента, то они разделяются знаком "; " точка с запятой.

В качестве аргументов могут использоваться:

- числа

- тексты, заключенные в кавычки

- логические величины

- константы

- арифметические выражения (формулы)

- другие функции

- ссылки или диапазон ссылок.

Следует обратить внимание на понятие " диапазон ссылок". Это блок ячеек, своего рода массив, который будет являться аргументом функции. Для того, чтобы указать диапазон ссылок надо указать адрес ячейки с которой начинается блок ячеек, поставить знак ": " двоеточие и указать адрес ячейки, которым заканчивается этот блок. Например, указанный в функции диапазон А2: А5 означает, что функция выполнит операцию с блоком ячеек в который входят А2, А3, А4 и А5.

В приведенном примере диапазон задан " вертикально", но его можно задать и " горизонтально", и в виде " прямоугольника". Например, указанный в функции диапазон В2: Е2 означает, что функция выполнит операцию с блоком ячеек в который входят В2, С2, D2 и Е2. Указанный в функции диапазон В2: С4 означает, что функция выполнит операцию с блоком ячеек в который входят В2, В3, В4, С2, С3 и С4.

Примеры функций:

- функция КОРЕНЬ(С4) вычисляет квадратный корень числа, находящегося в ячейке С4;

- функция ОКРУГЛ(В3; 2) округляет число, находящееся в ячейке В3 до 2-ух десятичных разрядов;

- функция ЛЕВСИМВ(" Танкист"; 4) возвращает четыре левых символа из слова танкист;

- функция СЧЕТЕСЛИ(С3: С12; " да") подсчитывает количество ячеек внутри указанного диапазона (начиная с С3 до С12 включительно), в которых находится слово " да".

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

Для вызова Мастера функций необходимо в команде главного меню Вставка выполнить команду f_x Функция…. Мастер функций можно вызвать кнопкой f_x, которая находится левее панели содержимого активной ячейки. B открывшемся диалоговом окне следует выбрать категорию и имя требуемой функции (см. рис 9).

Рис.9. Диалоговое окно мастера функций

Затем откроется новое диалоговое окно (см. рис 10), в поля которого, с соответствующими подсказками, надо ввести требуемые аргументы.

Рис. 10. Второе диалоговое окно мастера функций

После нажатия кнопки ОК готовая функция будет записана в активную ячейку.

6.1. Математические функции

Рассмотрим некоторые функции из этой категории.

1). Тригонометрические функции

SIN(число), COS(число), TAN(число) – возвращают соответственно синус, косинус, тангенс указанного угла. Число - это угол, указанный в радианах. Например, функция SIN(С4)- возвращает синус угла, значение которого указано в ячейке С4.

2). Обратные тригонометрические функции АSIN(число), АCOS(число), АTAN(число) – возвращают соответственно арксинус, арккосинус, арктангенс указанного числа. Результат выражается в радианах. Например, АCOS(В7) - возвращает угол в радианах, значение синуса которого указано в ячейке В7.

3). Функция преобразования радианов в градусы ГРАДУСЫ(угол), где угол это величина угла в радианах.

Функция преобразования градусов в радианы РАДИАНЫ(угол), где угол это величина угла в градусах. Например, функция РАДИАНЫ(Е5) - возвращает значение угла в радианах, преобразуя данные из ячейки Е5, в которой этот угол представлен в градусах.

Функция ПИ() возвращает округленное до 15 знаков после запятой число пи (3, 14159265358979). Это одна из не многих функций, не имеющая аргумента. Надо обратить внимание на то, что скобки около названия таких функций должны быть обязательно, хотя у них и нет аргумента. Именно по скобкам табличный процессор определяет, что это именно функция, а не обычная текстовая информация.

4). Логарифмические функции LN(число), LOG(число; основание), LOG10(число) возвращают соответственно натуральный логарифм числа, логарифм числа по указанному основанию, десятичный логарифм. Следует обратить внимание на то, что у функции два аргумента, которые разделены знаком "; " (точкой с запятой). Например, функция LOG(D4; 2) - возвращает логарифм числа, находящегося в ячейке D4, по основанию 2.

5). Функция округления ОКРУГЛ(число; число разрядов) обеспечивает округление числа до указанного количества десятичных разрядов. Если указанное число разрядов равно 0, то число округляется до ближайшего целого. Если оно больше 0 – то округляется до указанного количества десятичных разрядов. Если оно меньше 0 – то число округляется слева от десятичной запятой. Например:

- функция ОКРУГЛ(123, 542; 1) возвращает число 123, 5

- функция ОКРУГЛ(123, 542; 0) возвращает число 124

- функция ОКРУГЛ(123, 542; 1) возвращает число 120

Существуют еще две функции округления подобные функции ОКРУГЛ - ОКРУГЛВВЕРХ(число; число разрядов) и ОКРУГЛВНИЗ(число; число разрядов). Их отличие от функции ОКРУГЛ состоит в том, что первая всегда округляет число в большую сторону, а вторая – в меньшую сторону.

6). Функция суммирования СУММ(число1; число2; ….) обеспечивает суммирование перечисленных аргументов. В качестве аргументов могут быть числа, ссылки или диапазон. Например, функция СУММ(С2: С9) обеспечит суммирование значений, расположенных в блоке ячеек, начиная с С2 по С9 включительно.

Следует обратить внимание на то, что в примере в качестве аргумента указан диапазон (С2 и С9 разделены двоеточием. Если же в рассмотренном примере эти ссылки разделить точкой с запятой т.е. СУММ(С2; С9), то функция обеспечит суммирование только двух значений – значения в ячейке С2 и значения в ячейке С9.

Функция СУММ может иметь не только один аргумент, как в первом примере, или 2 аргумента, как во втором примере. В ней допускается использовать до 30 аргументов, разделенных знаком "; " (точка с запятой). Например, функция СУММ(С2: С9; Е2: Е9; В10) обеспечит суммирование значений, расположенных в блоках ячеек с С2 по С9, с Е2 по Е9 и значения из ячейки В10.

7). Еще одной полезной математической функцией является функция ПРОИЗВЕД(число1; число2; ….), которая обеспечивает расчет произведения перечисленных аргументов. Правила применения этой функции такие же, как и функции СУММ, поэтому в особых пояснениях она не нуждается.

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

6.2. Статистические функции

Рассмотрим некоторые статистические функции:

1). Функция нахождения максимума МАКС(число1; число2; ….) обеспечивает нахождения максимального элемента из перечисленных аргументов. В качестве аргументов могут быть числа, ссылки или диапазон.

2). Функция нахождения минимума МИН(число1; число2; ….) обеспечивает нахождения минимального элемента из перечисленных аргументов. В качестве аргументов могут быть числа, ссылки или диапазон.

3). Функция нахождения среднего значения СРЗНАЧ(число1; число2; ….) обеспечивает нахождения среднего арифметического значения из перечисленных аргументов. В качестве аргументов могут быть числа, ссылки или диапазон.

Порядок использования этих функций аналогичен порядку использования рассмотренных ранее функций СУММ и ПРОИЗВЕД.

Например:

функция МАКС(С2: С9) обеспечит нахождение максимального значения, из блока ячеек, начиная с С2 по С9 включительно;

функция СРЗНАЧ(С2: С9; Е2: Е9; В10) обеспечит расчет среднего арифметического значения, из блоков ячеек с С2 по С9, с Е2 по Е9 и значения из ячейки В10.

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

5). Функция СЧИТАТЬПУСТОТЫ(диапазон; критерий) подсчитывает количество пустых ячеек внутри указанного диапазона.

Особенностью этих функций является то, что первым аргументом в них указан диапазон, причем один. В этих функциях не допускается указывать два диапазона или отдельные ячейки, как это можно делать в функциях МИН, МАКС, СУММ, ПРОИЗВЕД, СРЗНАЧ и т.д.

Например:

функция СЧЕТЕСЛИ(С2: С9; " > 325") обеспечит подсчет количества ячеек со значением больше 325, из блока ячеек с С2 по С9 включительно;

функция СЧИТАТЬПУСТОТЫ(С2: В9) обеспечит подсчет количества пустых ячеек, из блока ячеек с С2 по В9.

6.3. Функции даты

1). Ввод даты в ячейку производится в формате ДД.ММ.ГГГГ

где ДД –день, ММ – месяц, ГГГГ – год. Например, требуется ввести дату 3 февраля 2011 года. Для этого следует установить курсор на ячейку, в которой должна быть дата, и ввести 03.02.2011

2). Функция СЕГОДНЯ() возвращает текущую дату в формате даты. Эта функция не имеет аргументов, но при ее использовании скобки должны быть обязательно. Например, если в компьютере на данный момент значится дата 28.01.2011, то функция СЕГОДНЯ() обеспечит вывод даты 28.01.2011. Естественно, что на следующий день эта функция обеспечит вывод уже даты 29.01.2011.

3). Функция РАЗНДАТ обеспечивает расчет промежутка между двумя датами. Эта функция имеет три параметра и в общем виде записывается так:

РАЗНДАТ(младшая дата; старшая дата; " ключ")

где

- младшая дата и старшая дата это даты, между которыми надо рассчитать промежуток;

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

" D" – промежуток будет рассчитан в днях

" M" – промежуток будет рассчитан в целых месяцах

" Y" – промежуток будет рассчитан в целых годах

" YM" – промежуток будет рассчитан в месяцах с учетом вычета целых годов

Например, функция РАЗНДАТ(C14; F14; " D") обеспечит расчет промежутка между датами, находящимися в ячейках C14 и F14, в днях. Если дата в ячейке С14 будет старше даты из ячейки F14, то функция выдаст сообщение об ошибке - #ЧИСЛО!. В этом случае достаточно отредактировать формулу, поменяв ссылки (адреса ячеек) местами.

Примечание. Функция РАЗНДАТ в справке и в мастере функций отсутствует, поэтому ее всегда следует набирать " вручную" в строке содержимого ячейки.

4). Для расчета промежутка между датами в днях можно использовать и функцию ДНЕЙ360. Общий вид этой функции следующий ДНЕЙ360(нач. дата; кон. дата; метод). Она обеспечит расчет промежутка между начальной датой и конечной датой в днях на основе 360-дневного года (12 месяцев по 30 дней).

Использование функции ДНЕЙ360 менее предпочтительно по сравнению с ранее рассмотренной функцией РАЗНДАТ, т.к. она рассчитывает промежуток между датами только в днях, да и то с некоторой погрешностью (из расчета 360-ти дневного года).

5). Дату в будущем или дату в прошлом можно рассчитать простым прибавлением к исходной дате или вычитанием из нее необходимого количества дней.

Например, формула = С5 + 23 обеспечит вывод даты, которая наступит через 23 дня после даты, указанной в ячейке С5. Еще один пример. Формула = СЕГОДНЯ() – 72 обеспечит вывод даты, которая была 72 дня назад.

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

Рис. 11. Электронная таблица для тренировки в использовании функций даты

1. Войдите внутрь электронной таблицы (курсор на таблицу и двойной щелчок левой клавишей).

2. В ячейку С2 введите формулу для вывода сегодняшней (текущей) даты. Если в ячейке возникает сообщение #######, то следует увеличить ширину этого столбца.

3. В ячейку D2 введите дату рождения (принять самостоятельно).

4. В ячейки Е2 и F2 введите формулы для расчета возраста в годах и днях (=РАЗНДАТ(D2; C2; " y") и =РАЗНДАТ(D2; C2; " d") соответственно).

5. В ячейке G2 рассчитайте дату, которая наступит через 83 дня.

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

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

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

1). Наиболее часто используемая логическая функция – функция ЕСЛИ. Она имеет следующий общий вид: