Текстовые и логические функции Microsoft Excel

 

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

 

 

Функции MS Excel

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

Функции Excel - это специальные, заранее созданные формулы, в которые пользователь должен ввести только аргументы.

MS Excel имеет несколько сотен встроенных функций, которые выполняют широкий спектр различных вычислений.

Некоторые функции MS Excel являются эквивалентами математических формул, которые можно сделать самому. А некоторые функции самостоятельно в виде формул реализовать в MS Excel невозможно.

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

Полная информация о функциях в справочной системе программы (меню ?)

 

 

 

Синтаксис функций MS Excel

Функции состоят из двух частей: имени функции и аргументов.

Имя функции, например СУММ, - описывает операцию, которую эта функция выполняет.

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

В формуле, приведенной ниже: СУММ - имя функции; В1:В5 - аргумент.

Данная формула суммирует числа в ячейках В1, В2, В3, В4, В5.

=СУММ(В1:В5)

Знак равенства в начале формулы означает, что введена именно формула, а не текст. Если знак равенства будет отсутствовать, то Excel воспримет ввод просто как текст.

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

В случае ввода пробела или другого символа между именем и открывающей скобкой в ячейке будет отображено ошибочное значение #ИМЯ? Некоторые функции не имеют аргументов. Даже в этом случае функция должна содержать круглые скобки:

=С5*ПИ()

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

=ПРОИЗВЕД(А1;А3;А6)

 

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

=СУММ(А2:А5;В4:В8)

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

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

 

Полная информация о функциях в меню ?

 

 

 

 

Типы аргументов функций MS Excel

 

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

Числовые значения

Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 24, 987, 49:

=СУММ(24;987;49)

Текстовые значения

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

=ТЕКСТ(ТДАТА();"Д МММ ГГГГ")

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

Логические значения

Аргументы ряда функций могут принимать только логические значения ИСТИНА или ЛОЖЬ. Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например:

=ЕСЛИ(А1=ИСТИНА;"Повышение";"Понижение")&" цены"

Именованные ссылки

В качестве аргумента функции можно указать имя диапазона. Например, если диапазону ячеек А1:А5 присвоено имя "Дебет" (Вставка-Имя-Присвоить), то для вычисления суммы чисел в ячейках с А1 по А5 можно использовать формулу

=СУММ(Дебет)

Использование различных типов аргументов

В одной функции можно использовать аргументы различных типов. Например:

=СРЗНАЧ(Дебет;С5;2*8)

 

Полная информация о функциях в меню ?

 

 

 

 

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

 

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

  • БАТТЕКСТ() - BATTEXT() - Преобразует чисто в текст (бат) на тайском языке

  • СИМВОЛ() - CHAR() - Определяет знак по заданному коду.

  • ПЕЧСИМВ() - CLEAN() - Удаляет все непечатаемые знаки из текста.

  • КОДСИМВ() - CODE() - Определяет числовой код первого знака в текстовой строке.

  • СЦЕПИТЬ() - CONCATENATE() - Объединяет несколько текстовых элементов в один.

  • РУБЛЬ() - DOLLAR() - Преобразует число в текст, используя денежный формат доллара.

  • СОВПАД() - EXACT() - Проверяет идентичность двух текстов.

  • НАЙТИ() - FIND() - Ищет вхождение одного текста в другой (с учетом регистра).

  • ФИКСИРОВАННЫЙ() - FIXED() - Форматирует число и преобразует его в текст с заданным числом десятичных знаков.

  • ЛЕВСИМВ() - LEFT() - Выдает нужное количество самых левых знаков в строке.

  • ДЛСТР() - LEN() - Определяет количество знаков в текстовой строке.

  • СТРОЧН() - LOWER() - Делает все буквы в тексте строчными.

  • ПСТР() - MID() - Выдает определенное число знаков из строки текста, начиная с указанной позиции.

  • ПРОПНАЧ() - PROPER() - Делает прописной первую букву в каждом слове текста.

  • ЗАМЕНИТЬ() - REPLACE() - Заменяет знаки в тексте.

  • ПОВТОР() - REPT() - Повторяет текст заданное число раз.

  • ПРАВСИМВ() - RIGHT() - Выдает самые правые знаки текстовой строки.

  • ПОИСК() - SEARCH() - Ищет вхождение одного текста в другой (без учета регистра).

  • ПОДСТАВИТЬ() - SUBSTITUTE() - Заменяет в текстовой строке старый текст новым.

  • Т() - T() - Преобразует аргумент в текст.

  • ТЕКСТ() - TEXT() - Форматирует число и преобразует его в текст.

  • СЖПРОБЕЛЫ() - TRIM() - Удаляет из текста лишние пробелы.

  • ПРОПИСН() - UPPER() - Делает все буквы в тексте прописными.

  • ЗНАЧЕН() - VALUE() - Преобразует текстовый аргумент в число.

Простейший способ получения полной информации о любой из них заключается в использовании меню ?

 

 

 

В Тайланде пригодится

 

Тайский язык (пхаса-тхай)  официальный язык Таиланда. 46 миллионов носителей.

В алфавите 44 согласных буквы, 28 гласных форм и 4 диакритических знака для обозначения тонов. Согласные пишутся горизонтально слева направо, гласные располагаются сверху, снизу, слева или справа от соответствующего согласного.

Не различаются строчные и прописные буквы. Пробелы между словами не ставятся. Предложения разделяются пробелами. Существуют особые тайские цифры (ตัวเลขไทย), однако обычно используются индийско-арабские (ตัวเลขอารบิก).

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

Концевые частицы выражающие уважение (к Концевому, например ;-):

  • ครับ (khrap, с высоким тоном) для мужчин,

  • ค่ะ (kha, с нисходящим тоном) для женщин.

Бат (THB) — валюта Таиланда. Бат состоит из 100 сатангов.

 

Текстовая функция BATTEXT () преобразует число в текст на тайском языке, включая названия тайской валюты.
 

Синтаксис:

BATTEXT(номер)
Число является любым числом. суффикс "Baht" (บาท) добавляется к интегральной части числа с окончанием ถ้วน (чистыми), а "Satang" (สตางค์) добавляется к целой части

Пример:
BATTEXT(777,2) возвращает строку в тайских символах เจ็ดร้อยเจ็ดสิบเจ็ดบาทสองสตางค์  (со значением: семьсот семьдесят семь бат двадцать сатанг).
 

 

 

 

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

 

 

В состав встроенных функций Microsoft Excel входят логические функции, что позволяет использовать табличный процессор для решения логических задач в контексте изучения формальных систем (в том числе Алгебры высказываний) и логических операций

.

Самым простым, шагом применения Excel может стать реализация таблицы логических операций

 

 

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

 

 

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

Для реализации сложных выражений используется функция "ЕСЛИ".

Функции и выражения могут быть вложены друг в друга.

Например, для реализации операции импликации А=>В, следует создать следующую формулу:

 

 

Непосредственно с клавиатуры следует вводить такую формулу (для ячейки H4):

=ЕСЛИ(И(B4=ИСТИНА;C4=ЛОЖЬ);ЛОЖЬ;ИСТИНА)

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

 

=ЕСЛИ(И(B4;НЕ(C4));ЛОЖЬ;ИСТИНА)

Функция "ЕСЛИ" в качестве значений аргументов "Значение_если_истина" и "Значение_если_ложь" допускает вложенность до 7 уровней, что позволяет конструировать проверку достаточно сложных условий.

Функция "ЕСЛИ" позволяет реализовать ветвящуюся алгоритмическую структуру.

 

Используя таблицу логических операций можно исследовать составные высказывания. Например:

 

Чтобы получить таблицу истинности данного высказывания можно:

  • скопировать исходные данные из ячеек B2:C6 в ячейки B9:C13, з

  • скопировать ранее полученные формулы (например, для операции отрицания А необходимо копировать информацию из ячеек D3:D6 в ячейки D10:D13).

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

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

 

 

Чтобы избавиться от "ручного" ввода операций, можно воспользоваться элементом управления "Поле со списком" на Панели "Элементы управления формы" (меню "Разработчик").

 

 

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

 

 

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

 

Проблемы вызывает реализация механизма ссылок на необходимые ячейки. Чтобы не допустить ошибок при "ручном" копировании и исправлении формул мож можно автоматизировать изменение индексов ячеек на основе функции "ЕСЛИ"

Расширить число реализованных в программе операций можно совместным использованием функции "ЕСЛИ"  и встроенных функций по работе с массивами и ссылками (например, функцию "ИНДЕКС"). В этом случае можно использовать ЛЮБОЕ количество ЛЮБЫХ операций, главное, чтобы они входили в сформированный для функции массив.

 

 

 

Работа с датами в  MS Excel

 

Ввод значений даты.

Для того чтобы ввести в ячейку дату, следует указать номер дня, номер месяца и две последние цифры года через точку (12.12.87), дефис (12-12-87)
или слэш “/”(12/12/87).

Можно вводить также первые три буквы названия месяца (12-дек-87 и т.п.; для даты в мае месяце необходимо написать слово май). Текущий год можно не указывать – он будет добавлен к введенной дате автоматически.

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

Так, если ввести, например, значение 12-12-87 или 12 дек 87, то в ячейке отобразится 12.12.87, а в строке формул для данной ячейки будет выведено: 12.12.1987. Но если в ячейке указать 22.10.28, то в строке формул вместо ожидаемой даты 22.10.1928 вы увидите другую – 22.10.2028. Дело в том, что, если при вводе даты указаны только две последние цифры года, Microsoft Excel  добавит первые две цифры по следующим правилам:

  • если введенное число лежит в интервале от 00 до 29, то оно интерпретируется
    как год с 2000-го по 2029-й;

  • если введенное число лежит в интервале от 30 до 99, то оно интерпретируется
    как год с 1930-го по 1999-й.

Таким образом, фирма Microsoft в свое время позаботилась о переходе в новое тысячелетие. Поэтому года с 1900-го по 1929-й следует указывать полностью.

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

Представление дат в ячейках. Формат представления даты в ячейке, отображаемый после ввода значения, может быть изменен. Для этого надо выбрать в главном меню пункт Формат, подпункт Ячейки, вкладку Число, раздел Числовые форматы, пункт Дата. Так, вместо значения 05.12.87 можно получить 5.12.87; 5 дек 87; 5 Декабрь, 1987 и т.д.
 

При этом значение даты, отображаемое в строке формул, не меняется (оно не зависит от формата ее представления в ячейке).

 

Операции с датами.

Даты можно складывать и вычитать, сравнивать между собой, умножать и делить на числа!

Введем в ячейку А1 дату “1 января 1900 года” (следует ввести 1-1-1900, а не 1-1-00).

С помощью маркера заполнения распространим (скопируем) введенное значение на ячейки А2:А10 (в них появятся даты, в которых будут значения, соответствующие 2, 3, …, 10 января 1900 года). Скопируем блок ячеек А1:А10 в В1: В10. Изменим формат представления данных в блоке В1:В10 на Общий (Формат-Ячейки-Число - Общий). Мы увидим, что в этом блоке появятся значения 1, 2, …, 10. Итак, дата в Excel – количество дней, прошедших c 1 января 1900 года. Такая форма внутреннего представления дат и позволяет выполнять над ними различные арифметические операции и операции сравнения.

 

Функции ДЕНЬ, МЕСЯЦ и ГОД. Эти функции возвращают соответственно номер дня в месяце, номер месяца в году и год для некоторой даты.

Их синтаксис: ДЕНЬ (дата), МЕСЯЦ (дата) и ГОД (дата), где аргумент дата – адрес ячейки, содержащей дату, либо дата, заданная в общем или числовом формате (12345), либо как текст (например, "15-4-93" или "15-Апр-1993").

День возвращается как целое число в диапазоне от 1 до 31. Месяц определяется
как целое в интервале от 1 (Январь) до 12 (Декабрь). Значение года возвращается как целое число в интервале 1900 — 9999.

Примеры:

  • Если в ячейке А2 указана дата 26.10.49, то ДЕНЬ (А2) равняется 26,
    МЕСЯЦ (А2) равняется 10, ГОД (А2) равняется 1949.

  • ДЕНЬ ("4-Янв") равняется 4, МЕСЯЦ ("4-Янв") равняется 1.

  • ДЕНЬ ("15-Апр-1993") равняется 15, МЕСЯЦ ("15-Апр-1993") равняется 4, ГОД ("15-Апр-1993) равняется 1993.

  • ДЕНЬ ("11.8.93") равняется 11, МЕСЯЦ ("11.8.93") равняется 8, ГОД
    ("11.8.93") равняется 1993.
     

Функция ДЕНЬНЕД. Функция возвращает номер дня недели, соответствующий
некоторой дате.

Синтаксис: ДЕНЬНЕД (дата; тип), где дата – аргумент, аналогичный используемому в описанных выше функциях; тип – число, которое определяет вариант возвращаемых значений:

Примеры:

  • Если в ячейке А2 указана дата 26.10.49, то ДЕНЬНЕД (А2) равняется 4
    (Среда).

  • ДЕНЬНЕД ("15.2.90") равняется 5 (Четверг).

  • ДЕНЬНЕД ("15.2.90"; 2) равняется 4 (Четверг).

 

Функция СЕГОДНЯ. Функция возвращает дату текущего дня, отслеживаемую
компьютером.

Синтаксис: СЕГОДНЯ ( ) – без аргументов, но с обязательными скобками.

 

Функция ДАТА. Функция позволяет “собрать” дату из значений года, номера месяца и номера дня. Ее синтаксис: ДАТА (год; месяц; день), где год – это число от 1900 до 2078; месяц – это число, представляющее номер месяца в году; день – это число, представляющее номер дня в месяце.

Пример:

  • ДАТА (45; 5; 9) есть 9 мая 1945 года.