Присвоение имен ячейкам и их диапазонам

Чтобы понять смысл функции было проще, можно присвоить имя ячейке или диапазону ячеек и затем использовать его в вычислениях. Например, вы можете присвоить ячейкам E4:E10 (рис. 10.3) имя Москва, и тогда формула для нахождения минимального значения для этих ячеек будет выглядеть следующим образом: =МИН (Москва).

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

Для того чтобы присвоить имя ячейке или диапазону ячеек, выделите набор ячеек вместе с их заголовком (заголовок будет использован в качестве имени выделенного диапазона) и выберите команду Вставка | Имя | Создать. Нажмите кнопку ОК.

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

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

 

Типы функций

 

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

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

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

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

Транспонирование матрицы

Пусть дана матрица A = .

Расположите числа этой матрицы в ячейках А3:С5. Выделите место под транспонированную матрицу, например, ячейки Е3:G5. Вызовите мастер функций (кнопка на строке формул). В открывшемся диалоговом окне в списке Категория выберите Математические, а в списке Выберите функцию – ТРАНСП. Откроется диалоговое окно Аргументы функции. В поле ввода Массив задайте диапазон ячеек с исходной матрицей (вручную или с помощью мыши). Затем нажмите клавиши CTRL+SHIFT+ENTER, чтобыраспространить результат на всю выделенную область.

Произведение двух матриц

Так же, как и в предыдущем примере, нужно выделить диапазон ячеек для матрицы-результата и вызвать мастер функций. В списке функций выберите МУМНОЖ, откроется диалоговое окно, имеющее два поля ввода: Массив 1 и Массив 2. Введите в эти поля диапазоны ячеек с числами первой и второй матриц, затем нажмите клавиши CTRL+SHIFT+ENTER. Количество столбцов массива 1 должно быть таким же, как количество строк массива 2.

 

Текстовые функции

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

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

Создание инициалов

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

Разместите исходную таблицу, как показано на рис. 10.7, и заполните её вручную. Для порядковых номеров (столбец А) используйте автозаполнение.

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

=B4&" "&ЛЕВСИМВ(C4)&"."&ЛЕВСИМВ(D4)&".".

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

Примечание. В используемой формуле В4 – фамилия, & – знак объединения символьных фрагментов. Далее следует пробел (символ пробел заключен в кавычки, т.к. является текстовой константой). ЛЕВСИМВ – функция из списка (находится в разделе Текстовые), возвращает первый (самый левый) символ текстовой строки, в нашем примере функция ЛЕВСИМВ(С4) возвращает первый символ имени. “.” – текстовая константа «точка». Далее функция ЛЕВСИМВ(D4) – возвращает первый символ отчества, после которого снова ставится точка.

 

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

Excel преобразует значение даты и времени суток в так называемые сериальные числа, которые используются при вычислениях. Эти числа должны быть заданы в качестве аргумента дата_в_числовом_формате. Число 1 соответствует значению даты 01/01/1900, максимальное значение 65380 – дате 31/12/2078.

Значения времени суток также преобразуются в сериальные числа с десятичными разрядами. Например, значение 0,00001 соответствует первой секунде.

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

Определение стажа

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

Занесите исходные данные в ячейки В4:Е8 вручную (рис. 10.8), а в ячейку F4 запишите формулу:

=ГОД(СЕГОДНЯ()-E4)-1900+МЕСЯЦ(СЕГОДНЯ()-E4)/12.

С помощью автозаполнения скопируйте формулу на все последующие ячейки столбца F. Результат может выглядеть странно, т.к. значения в ячейках отображаются в формате Дата. Изменим его на Числовой с 2 знаками после запятой (команда Формат | Ячейки, вкладка Число).

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

 

Рис. 10.8. Пример использования функций даты и времени

 

Примечание. Функция СЕГОДНЯ() возвращает текущую дату в числовом формате. У этой функции нет аргументов. Функция ГОД возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое в интервале 1900-9999. В нашем примере в качестве аргумента используется разность дат – сегодняшней и начала работы. Функция МЕСЯЦ возвращает месяц, соответствующий аргументу дата_в_числовом_формате. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь). В качестве аргумента этой функции также используется разность дат – сегодняшней и начала работы; затем количество месяцев делится на 12, т.е. определяется доля года.

 

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

Логических функций в Microsoft Excel шесть: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

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