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

  •  вводить формулы;

  • пользоваться стандартными функциями;

  • ссылаться на диапазон ячеек;

  • выполнять сложные вычисления;

  • копировать формулы;

  •  исправлять ошибки в формулах.

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

 

Вычисления в Excel

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

 

Упражнение 1. Ввод формул

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

  1.  Откройте фaйл Электронная таблица.xls, созданный на предыдущем занятии. В этой рабочей книге есть лист Формулы, измененный в контрольном упражнении предыдущего занятия. Раскройте его. Первые семь строк листа должны выглядеть так, как показано па рис. 10.1, остальные ячейки будут пусты. Если это не соответствует действительности, отредактируйте лист.

  2. Щелкните на ячейке В10 и введите формулу =(СЗ-ВЗ)/ВЗ*100.

Знак равенства указывает программе Excel, что ячейка содержит формулу. СЗ и ВЗ — это ссылки на содержимое соответствующих ячеек. Если предположить, что таблица содержит объем продаж продукции различным клиентам, то (СЗ-ВЗ) — это прирост продаж для фирмы Фантом с января по февраль. Разделив полученную величину на ВЗ (продажи за январь), получаем относительный прирост продаж за месяц. Результат умножается на 100, что дает прирост в процентах. Как видите, в ячейки можно вводить простые, арифметические выражения со знаками +, -, *, / и скобками, определяющими последовательность выполнения операций.

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

Рис. 10.1. Ввод формулы

  1. Щелкните на ячейке В10 и перетащите угловой маркер выделения вправо, чтобы оказались охваченными ячейки с В10 по F10.

Примечание

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

На первый взгляд кажется, что в ячейки C10:F10 должна дублироваться формула ячейки В 10, но это не так. Excel поддерживает механизм относительных ссылок на ячейки. Когда формула переносится на одну клетку вправо, точно так же смещаются все ссылки этой формулы (это верно и для смещения формулы в любых других направлениях на любое число клеток). То есть в ячейке С11 появится формула =(D3-C3)/C3*100, в ячейке D10 - формула =(E3-D3)/D3*100 и т. д. Чтобы проверить это, щелкните на каждой ячейке и изучите содержимое строки фор-•мул. В итоге ячейки строки 10 будут содержать относительный прирост продаж компании Фантом по месяцам.

  1.  Выделите строки с третьей по седьмую.

  2.  Щелчком на кнопке Вырезать (Cut) панели инструментов Стандартная (Standard) вырежьте их содержимое.

  3.  Щелкните на ячейке А2 правой кнопкой мыши и выберите в контекстном меню команду Вставить. Данные таблицы переместятся вверх на одну строку. Теперь снова изучите формулы ячеек строки 10.

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

Примечание

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

Упражнение 2. функции

Excel поддерживает множество стандартных математических функций, которые можно вставлять в формулы. С тремя из них — СУММ (SUM), СРЗНАЧ (AVERAGE) и СЧЕТ (COUNT) — вы познакомились на предыдущем занятии. Чтобы научиться работать с другими функциями, выполните следующие шаги.

  1.  Щелкните на ячейке Н8 и введите текст Максимум. Нажмите клавишу Tab.

  2. Щелкните на кнопке Вставка функции (Paste Function) панели инструментов Стандартная. Откроется окно диалога, показанное на рис. 10.2. Чтобы найти нужную функцию, можно кратко описать желаемую процедуру в текстовом поле Поиск функции (Search for a function) или же выбрать ее категорию в списке категорию: (Select a Category) этого окна диалога, а затем пролистать список Выберите функцию (Select a Function Name). Выделив имя одной из функций, вы увидите ее описание в нижней части окна диалога. Excel делит все функции на следующие категории:

    • Финансовые (Financial) — функции для расчета амортизации имущества, стоимости основных фондов, нормы прибыли, величины выплат на основной капитал и других финансовых показателей;
    •  Дата и время (Date & Time) — операции прямого и обратного преобразования даты и времени в текстовые строки. Функции этой группы перечислены в табл. 10.1;

Рис. 10.2. Вставка функции

ТАБЛИЦА 10.1. Функции обработки даты и времени

Функция

Описание

ДАТА(DATE)

Возвращает дату в формате Excel

ДАТАЗНАЧ(DATEVALUE)

Преобразует дату из текстового формата в код Excel

ДЕНЬ(DAY)

День месяца заданной даты

ДНЕЙ360 (DAYS360)

Вычисляет количество дней между двумя датами на основе З60-дневного года

ЧАС (HOUR)

Час времени, заданного аргументом

.МИНУТЫ (MINUTE)

Минута времени, заданного аргументом

МЕСЯЦ (MONTH)

Номер месяца заданной даты

J-ДАТА (NOW)

Текущая дата и время в числовом формате

СЕКУНДЫ (SECOND)

Секунда времени, заданного аргументом

ВРЕМЯ (TIME)

Преобразует время дня в дробное число от 0 до 1

ВРЕМЗНАЧ (TIMEVALUE)

Преобразует время из текстового формата в дату в числовом формате

ДЕНЬ (TODAY)

Текущая дата в числовом формате

ДЕНЬНЕД (WEEKDAY)

Номер дня недели заданной даты

ГОД(YEAR)

Год заданной даты


    •  Математические (Math & Trig) — математические и тригонометрические функции, некоторые из них приведены в табл. 10.2;

ТАБЛИЦА 10.2. Математические и тригонометрические функции

Функция

Описание 

COS, SIN, TAN,

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

ACOS, ASIN, ATAN, ATAN2

Обратные тригонометрические функции

COSH, SINH, TANH

Гиперболические функции

ACOSH, ASINH, ATANH

Обратные гиперболические функции

LN, LOG, LOG10

Натуральный логарифм, логарифмы по основанию 2 и 10

EXP

Экспонента

НЕЧЕТ (EVEN), OKPyiTI(ROUND), ОКРУГЛВВЕРХ (ROUNDUP), ОКРУГЛВНИЗ (ROUNDDOWN), 4ETH(ODD)

Функции округления

ABS

Модуль (абсолютное значение)'числа

ГРАДУСЫ (DEGREES),

Преобразование радиан в градусы

РАДИАНЫ (RADIANS)

Преобразование градусов в радианы


ЦЕЛОЕ (INT)

Целая часть числа

ОСТАТ (MOD)

Остаток от деления

ПИ(Р1) 

Число пи

СТЕПЕНЬ (POWER)

Возведение в степень

ПРОИЗВЕЛ (PRODUCT)

Произведение ряда чисел

СЛЧИС (RAND)

Возвращает случайное число

РИМСКОЕ (ROMAN)

Преобразование арабского числа в римское

КОРЕНЬ (SQRT)

Квадратный корень

СУММ (SUM)

Сумма ряда чисел

СУММ КВ (SUMSQ)

Сумма квадратов ряда чисел

ОТБР (TRUNC)

Отбрасывает дробную часть


ТАБЛИЦА 10.3. Статистические функции

Функция

Описание

СРОТКЛ(AVEDEV)

Среднее абсолютное значение отклонения от среднего

СРЗНАЧ (AVERAGE), СРЗНАЧА(AVERAGEA)

Среднее арифметическое аргументов

СЧЕТ (COUNT), СЧЕТЗ (COUNTA), СЧИТАТЬ ПУСТОТЫ (COUNT-BLANK), СЧЕТЕСЛИ (COUNTIF)

Количество чисел в списке аргументов

КБЛДРОТКЛ (DEVSQ.)

Сумма квадратов отклонении от среднего но выборке

CFFEOM (GEOMEAN)

Среднее геометрическое набора положительных чисел

МАКС (МАХ), МАКСА (МАХА)

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

МИН (M1N), МИНА (MINA)

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

ДИСП (STDEV), ДИСПА (STDEVA),

Функции расчета дисперсии

ДИСПР (STDEVP), ДИСПРА (STDEVPA) СТАНДОТ-КЛОН (VAR), СТАНДОТКЛОНА (VARA), СТАНДОТКЛОНП (VARP), СТАНДОТКЛОНПА (VARPA)

Стандартное отклонение по выборке


    • Статистические (Statistical) — функции для расчета среднего значения, дисперсии, статистических распределений и других вероятностных характеристик. Некоторые статистические функции перечислены в табл. 10.3;
    •  Ссылки и массивы (Lookup & Reference) — операции преобразования ссылки на ячейку в число, расчета ссылок на основе числовых аргументов, вычисления числа строк и столбцов диапазона и других параметров, связанных с адресацией ячеек листа Excel;
    •  Работа с базой данных (Database) — функции формирования выборки из базы данных и расчета статистических параметров величин, расположенных в базе данных;
    •  Текстовые (Text) — функции для работы с текстовыми строками. Часть этих функций перечислена в табл.-10.4.

ТАБЛИЦА 10.4. Текстовые функции

Функция

Описание

СИМВОЛ (CHAR)

Возвращает символ с заданным кодом

ПЕЧСЙМВ (CLEAN)

Удаляет из строки все непечатаемые символы

КОДСИМВ (CODE)

Код первого символа строки

СЦЕПИТЬ(CONCATENATE)

Объединяет две текстовые строки

СОВПАД (EXACT)

Проверяет идентичность двух строк

ЛЕВСИМВ (LEFT)

Возвращает несколько левых символов строки

ДЛСТР (LEN)

Количество символов в строке

СТРОЧН (LOWER)

Делает все буквы текста строчными

ПРОПНАЧ(PROPER)

Делает первую букву прописной, а остальные — строчными

ПОВТОР(REPT)

Повторяет текст заданное число раз

ПРАВСИМВ (RIGHT)

Возвращает несколько Правых символов и роки

Т, ТЕКСТ(TEXT), ФИКСИРОВАННЫЙ (FIXED)

Преобразует число в текст 

СЖПРОБЕЛЫ (TRIM)

Удаляет лишние (двойные) пробелы

ПРОПИСН (UPPER)

Делает все буквы прописными

ЗНАЧЕН (VALUE)

Преобразует текстовый аргумент в число


    • Логические (Logical) — шесть функций для работы с данными логического типа, то есть величинами или условиями, принимающими значение Истина или Ложь. Эти функции перечислены в табл. 10.5;
    •  Проверка свойств и значений (Information) — функции проверки типа данных аргумента, режима форматирования ячейки, типа сгенерированной ошибки и других специальных условий.

ТАБЛИЦА 10.5. Логические функции

Функция

Описание

И (AND)

Логическое умножение

ЛОЖЬ (FALSE)

Возвращает ложное значение

ЕСЛИ (IF)

Возвращает одно значение, если условие истинно, и другое, если условие ложно

НЕ (NOT)

Логическое отрицание

ИЛИ (OR)

Логическое сложение

ИСТИНА (TRUE)

Возвращает истинное значение


В списке Категория (Function Category) есть также пункты Рекомендуемый перечень (Recommended), Полный алфавитный перечень (All) и 10 недавно использовавшихся (Most Recently Used), которые выводят соответственно список всех функций и 10 функций, применявшихся последними.

  1.  Выберите в списке Категория окна диалога вставки функций (см. рис. 10.2) пункт Статистические.

  2.  Выберите в списке Функция (Function Name) пункт МАКС.

  3.  Щелкните на кнопке ОК. Откроется окно ввода диапазона ячеек, показанное на рис. 10.3.

Рис. 10.3. Окно ввода аргументов

Некоторые функции, например SIN, имеют только один аргумент, поскольку нельзя вычислить синус сразу двух чисел. Однако многие функции, подобные МАКС, способны обрабатывать практически неограниченные массивы данных. Такие функции могут воспринимать до 30 аргументов, каждый из которых является числом или ссылкой на одну или несколько ячеек.

Примечание

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

С помощью функции МАКС вы сейчас найдете максимальный уровень продаж за один месяц для компаний Фантом, РИФ и Викинг.

Рис. 10.4. Выбор ячеек для первого аргумента

  1.  Щелкните на кнопке в правой части поля Число 1 (Number 1). Окно диалога свернется в строку, открывая доступ к ячейкам листа.

  2.  Выделите все числовые ячейки строки Фантом. Обозначение соответствующего диапазона ячеек появится в строке свернутого окна диалога ввода аргументов (рис. 10.4).

  3. Щелкните на кнопке строки аргумента. На экране снова развернется окно ввода аргументов.

  4.  Щелкните на кнопке в правой части поля Число 2 (Number 2).

  5.  Выделите ячейки B4:G4 и снова щелкните на кнопке строки ввода аргумента. Поскольку вы ввели уже два аргумента, Excel автоматически добавит поле ввода третьего.

  6.  Введите в поле Ч и ел о 3 (Number 3), как показано на рис. 10.5, текст B6:G6. Это диапазон нужных ячеек строки Викинг. В окне диалога правее полей с аргументами демонстрируются их реальные значения. Ниже списка чисел отображается результат вычислений.

Рис. 10.5. Три диапазона ячеек в качестве аргументов функции МАХ

  1.  Щелкните на кнопке ОК. В ячейке 18 появится максимальное число из диапазона, записанного в строках 2,4 и 6. Сама формула появится в строке формул в верхней части окна Excel. Щелкните в этой строке. Три аргумента функции МАКС, задающие три диапазона ячеек, будут выделены разными цветами, а соответствующие группы ячеек листа Excel окажутся обведенными рамками соответствующих цветов, как показано на рис. 10.6.

Рис. 10.6. Группы ячеек, выступающие в качестве аргументов формулы

Примечание

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

Упражнение 3. Диапазон ячеек

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

  1.  Щелкните на ячейке Н9 и введите текст Сумма. Затем нажмите клавишу Tab.

  2. Введите символы =СУММ () (=SUM ()). Эта формула вычисляет сумму всех величин, указанных в скобках в качестве аргумента.

  3. Щелкните в строке формул и поместите курсор между двумя скобками.

  4.  Введите в скобки текст В: В.

  5.  Щелкните на кнопке Ввод слева от строки формул. В ячейке 19 появится сумма всех ячеек столбца В, так как символы В:В обозначают все ячейки столбца В.

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

  7.  Повторите шаги с 3-го по 6-й, поочередно вводя в скобки формул значения из первого столбца табл. 10.6. Изучите, какие диапазоны ячеек соответствуют указанным условным обозначениям. Немного потренировавшись, вы сможете указывать в качестве аргумента 4>ункции любые группы ячеек листа. , Если в разных формулах часто приходится ссылаться на одну и ту же группу ячеек, особенно если в группу входят разрозненные ячейки из разных областей листа, ей удобно присвоить специальное имя.

  8.  Протаскиванием мыши выделите ячейки B3:G3.

  9.  Нажмите клавишу Ctrl и протащите указатель мыши по ячейкам B5:G5.

Рис. 10.7. Столбец В в качестве аргумента функции

ТАБЛИЦА 10.6 .Способы ссылки на группы ячеек

Обозначение

Группа ячеек

F3

Ячейка на пересечении столбца F и строки-3

Е10:Е20

Ячейки с 10-й по 20-ю в столбце Е

В15:Е15

Ячейки с В по Е в строке 15

5:5

Все ячейки строки 5

5:10

Все ячейки строк с 5-й по 10-ю

В:В

Все ячейки столбца В

B:J

Все ячейки столбцов c B no J

А10:Е20

Прямоугольная область пересечения строк с 10-й по 20-ю и столбцов с А по Е


  1.  Введите в поле Имя (Name Box) слово Строки, как показано на рис. 10.8.

  2.  Нажмите клавишу Enter.

  3.  Щелкните на ячейке 19, введите формулу =СУММ(Строки) (=SUМ(Строки)) и нажмите клавишу Enter. В ячейке 19 появится сумма двенадцати ячеек строк Зи5. 

  4.  Щелкните на стрелке раскрывающегося списка Имя (Name Box).

Рис. 10.8. Именованная группа ячеек

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

Формулы

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

 

Упражнение 4. Копирование формул

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

  1.  Щелкните на любой непустой ячейке в строке 10. Вы увидите, что выбранная формула ссылается на ячейки строки 2.

  2.  Выделите диапазон B10:F10.

  3.  Нажатием клавиш Ctrl+C скопируйте ячейки.

  4. Щелкните на ячейке В11, а затем на кнопке Вставить панели инструментов Стандартная. Появится еще одна строка ячеек с формулами. Щелкните на любой из них и взгляните в строку формул. Вы увидите, что формулы новых ячеек ссылаются на данные строки 3, как показано на рис. 10.9.

  5.  Щелкните на ячейке В 12 и нажмите клавиши Ctrl+V, чтобы вставить еще одну строку. Формулы этой строки уже будут ссылаться на данные строки 4. Таким образом, номера ячеек данных изменяются ровно на столько клеток, на сколько смещается вставляемая формула.

Рис. 10.9. Копирование формул

Примечание

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

  1.  Вставьте тот же самый фрагмент в ячейки В13 и В14.

  2.  Скопируйте заголовки строк из ячеек А2:А6 в ячейки А10:А14.

Примечание

Обычно Excel формирует относительные ссылки на ячейки. Но иногда нужно сослаться в формуле на клетку с константой, которая не должна меняться при копировании формулы в другую ячейку или при перемещении исходных данных. В этом случае пользуйтесь абсолютными ссылками, которые отличаются от относительных наличием символа $ перед буквой столбца, номером строки или перед обеими этими характеристиками, например $Е$15. В такой ссылке не изменяется та часть, перед которой стоит знак $. Например, копируя формулу =СУММ($В$2:$0$6) (=SUM($B$2:$G$6)) в другую ячейку, вы получите тот же самый результат, что и в исходной клетке формулы.

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

  1.  Выделите группу ячеек 12:16.

  2. Щелкните на кнопке Автосумма (AutoSum) панели инструментов Стандартная.

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

Примечание  

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

Упражнение 5. Поиск ошибок

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

Рис. 10.10. Автосумма

  1.  ЩелкнитенаячейкеШивведитеформулу СРЗНАЧ B11:F11 (AVERAGE B11:F11). 

  2.  Нажмите клавишу Enter. В ячейке появится сообщение #ИМЯ (#NAME), которое \ указывает на ошибку в имени функции. Слева от него находится маленький значок помощника, содержащий раскрывающийся список, в котором приводятся возможные способы устранения ошибки. . В табл. 10.7 приведен список сообщений об ошибках, которые можно встретить в ячейках при работе с формулами.

  3.  Чтобы исправить формулу, двойным щелчком на ячейке 111 перейдите в режим редактирования формулы.

Примечание

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

ТАБЛИЦА 10.7. Сообщения об ошибках в формулах

Сообщение

Описание ошибки

#####

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

#ЗНАЧ (#VALUE!)

Неверный тип аргумента или операнда. Например, указание в качестве аргумента ячейки с текстом, когда требуется число

#ДЕЛ/0(#01У/0!)

Деление на 0

#ИМЯ (#NAME!)

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

#Н/Д (#N/A)

Данные ячейки одного из аргументов формулы в данный момент недоступны

*#ССЫЛКА (#REF!)

Неверная ссылка на ячейку

#ЧИСЛО(#МиМ!)

Невозможно вычислить результат формулы, либо он слишком велик или мал для корректного отображения в ячейке

#ПУСТО (#NULL!)

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


  1.  Измените содержимое ячейки так, чтобы получилась формула СРЗНАЧ (В 11:F11) (AVERAGE(B11:F11)).

    Упражнение 6. Применение функций

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

    1.  В листе Формулы выделите и скопируйте ячейки Bl:Gl.

    2.  Разверните лист ЛистЗ (Sheet3).

    3.  Щелкните правой кнопкой мыши на ячейке А1 и выберите в контекстном меню команду Вставить.

    4. Введите в ячейку A3 формулу =ЛЕВСИМВ(А1 ;3) (=LEFT(A1; 3)). Эта формула возвращает три левых символа ячейки А1.

    5.  Перетащите угловой маркер выделения вправо, чтобы рамка охватила ячейки A3:F3. Теперь в третью строку выводятся сокращенные варианты названий месяцев из ячеек первой строки.

    6.  Щелкните наячейкеНЗ и введите формулу =СЦЕПИТЬ(ВЗ ;D3) (CONCATENATE (ВЗ ; D3)). В ячейке НЗ появится объединение строк Фев и Апр.

    7.  В ячейку А8 введите формулу =ТДАТА () (=NOW ()) и нажмите клавишу Enter. В ней тут же появятся текущие дата и время.

    8.  Введите в ячейки с В8 по В13 формулы = ГОД (А8) (=YEAR(A8)), =МЕСЯЦ(А8) (=MONTH (А8)),=ДЕНЬ(А8)(=DAY(А8)),=ЧАС(А8)(=HOUR(A8)),=МИНУТЫ(А8) (=MINUTE(A8)) и =СЕКУНДЫ(А8) (=SECOND (A8)). В этих ячейках появятся по отдельности все шесть компонентов текущих даты и времени.

    9.  Дважды щелкните на ячейке А8, чтобы перевести ее в режим редактирования.

    10.  Щелкните на кнопке Ввод, расположенной слева от строки формул. Эта операция приведет к обновлению значения в ячейке А8, что повлияет и на числа ячеек В8:В13.

    Теперь давайте выполним логическую операцию.

    1. ВведитевячейкуА4формулу=ЕСЛИ(АЗ="Фев"; "Да"; "Нет") (=IF(АЗ="Фев"; " Да "; " Н е т ")). Эта операция сравнивает значение ячейки A3 с текстовой строкой Фев. В случае равенства выводится текст второго аргумента — Да. В случае неравенства выводится текст третьего аргумента — Нет. Так как в ячейке A3 присутствует текст Янв, то результатом этой операции будет значение Нет.

    2.  Растяните маркер ячейки А4 вправо, чтобы рамка охватила диапазон A4:F4. Теперь формулы строки 4 проверяют ячейки строки 3 на совпадение их содержимого со строкой Фев. Как видите, слово Да появилось только в столбце В.

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

    Примечание

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

    ТАБЛИЦА 10.8. Знаки сравнения

    Знак

    Значение 

    =

    Равно

    >

    Больше

    <

    Меньше

    >=

    Больше или равно

    <=

    Меньше или равно

    <>

    Не равно

Последнее изменение: четверг, 11 августа 2011, 12:32