Формулы

Microsoft Office Excel обрабатывает данные при помощи формул и встроенных стандартных функций.

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

Например, в ячейке С5 формула =A4*D7 будет умножать данные из ячейки А 4 на данные из ячейки D1. В ячейке листа формула видна только в момент ввода или редактирования, а после ввода ячейка выводит результат вычислений. Когда табличный курсор выделяет ячейку с формулой, то ее можно увидеть в строке формул.

Константа представляет собой готовое (невычисляемое, постоянное) значение, которое введено в ячейку: текст, целые и дробные десятичные числа (в том числе процентные и денежные форматы), даты, время. Например, число 210, дата 09.04.2011 и текст "Итого" – константы, а выражения =(35+590)*23 и =0,13*CУMM(C9:D29) – формулы, в которые входят числовые константы 35,590, 23 и 0,13. Сами формулы не являются константами.

Операторы определяют действия в формуле (сложение, умножение, деление, сравнение, а также объединение). Арифметические операторы +, -, *, / вводят знаки плюс, минус, умножить, разделить, например: =(B4+25*C4)/(D5-F5). Оператор процентов % записывается вплотную к числу или адресу ячейки и соответствует делению на 100. Формула =(1+5%) вычислит значение 105%, а формула =С5*(1+5%) эквивалентна =С5*1,05 и вычисляет 105% числа в ячейке С5. Оператор возведения в степень Λ ("крышка") находится на клавише 6англ. Например, формула =3^4 означает 34 и возвратит значение 81. Термин "возвращать" в отношении формул и функций табличной обработки данных означает "выводить в качестве значения ячейки". Набираются и вводятся данные, формулы, функции и после вычисления возвращается результат – число, текст.

Операторы сравнения . = (равно), < (меньше), > (больше), <= (меньше или равно), >= (больше или равно), <> (не равно) – применяются в качестве условий и критериев логических и статистических функций.

Операторы ссыпки: двоеточие между адресами первой и последней ячейки прямоугольного диапазона (C9:D29) и точка с запятой, которая отделяет несколько параметров, например CPЗНАЧ(B9:B29;D9:D29;200).

Синтаксис написания формулы задает последовательность вычислений, а в функциях – правила написания их имен и задаваемых аргументов (переменных). По умолчанию Excel вычисляет формулу слева направо, начиная со знака равенства, и возвращает число. Формула =5+2*3 возвращает число 11, но формула =(5+2)*3 с теми же числами сначала вычислит значение 7 в скобках, потом умножит на 3 и возвратит число 21.

Например, ячейка с формулой =2*2 независимая, а ячейка с формулой =В9*К15 зависимая и пересчитает значение заново, если изменить данные в ячейке К15, на которую формула ссылается. Пересчет значений в зависимых ячейках при изменении данных в независимых ячейках – мощный автоматизм Excel.

Функции

Слово "функция" (лат. functio – исполнение) в обычном понимании означает деятельность, обязанность, работу, роль.

В математике функция – переменная величина, зависимая от изменения независимой переменной – аргумента. Функция может быть представлена соответствием у =f (х), а также формулой, таблицей, графиком, диаграммой.

В предыдущем параграфе рассмотрен объемный подход к измерению информации. Он используется для определения количества информации, заключенного в тексте, записанном с помощью некоторого алфавита. При этом содержательная сторона текста в учет не берется. Совершенно бессмысленное сочетание символов с данной позиции имеет ненулевой информационный объем.
Неопределенность и количество информации
Сейчас мы обсудим другой подход к измерению информации, который называют содержательным подходом. В этом случае количество информации связывается с содержанием (смыслом) полученного человеком сообщения. Вспомним, что с «человеческой» точки зрения — это , которые мы получаем из внешнего мира. Количество информации, заключенное в сообщении, должно быть тем больше, чем больше оно пополняет наши .
Как же с этой точки зрения определяется единица измерения информации? Вы уже знаете, что эта единица называется битом. Проблема измерения информации исследована в теории информации, основатель которой — Клод Шеннон. В теории информации для бита дается следующее определение:
Сообщение, уменьшающее неопределенность в два раза, несет 1 информации.
В этом определении есть понятия, которые требуют пояснения. Что такое неопределенность ? Поясним на примерах. Допустим, вы бросаете монету, загадывая, что выпадет: орел или решка. Есть всего два возможных результата бросания монеты. Причем ни один из этих результатов не имеет преимущества перед другим. В таком случае говорят, что они равновероятны.
В случае с монетой перед ее подбрасыванием неопределенность о результате равна двум. Игральный же кубик с шестью гранями может с равной вероятностью упасть на любую из них. Значит, неопределенность о результате бросания кубика равна шести. Еще пример: спортсмены-лыжники перед забегом путем жеребьевки определяют свои порядковые номера на старте. Допустим, что имеется 100 участников соревнований, тогда неопределенность спортсмена о своем номере до жеребьевки равна 100.
Следовательно, можно сказать так:
Неопределенность о результате некоторого события (бросание монеты или игрального кубика, вытаскивание жребия и др.) — это количество возможных результатов.
Вернемся к примеру с монетой. После того как вы бросили монету и посмотрели на нее, вы получили зрительное сообщение, что выпал, например, орел. Определился один из двух возможных результатов. Неопределенность уменьшилась в два раза: было два варианта, остался один. Значит, узнав результат бросания монеты, вы получили 1 информации.
Сообщение об одном из двух равновероятных результатов некоторого события несет 1 информации.
Это утверждение — частный вывод из определения, данного выше.
А теперь такая : студент на экзамене может получить одну из четырех оценок: 5— «отлично», 4 — «хорошо», 3 — «удовлетворительно», 2 — «неудовлетворительно». Представьте себе, что ваш товарищ пошел сдавать экзамен. Причем учится он очень неровно и может с одинаковой вероятностью получить любую оценку от «2» до «5». Вы волнуетесь за него, ждете результата экзамена. Наконец, он пришел и на ваш вопрос: «Ну, что получил?» — ответил: «Четверку!».
Вопрос: сколько битов информации содержится в его ответе?
Если сразу сложно ответить на этот вопрос, то давайте подойдем к ответу постепенно. Будем отгадывать оценку, задавая вопросы, на которые можно ответить только «да» или «нет».
Вопросы будем ставить так, чтобы каждый ответ уменьшал количество возможных результатов в два раза и, следовательно, приносил 1 информации.
Первый вопрос:
- Оценка выше «тройки»?
- Да.
После этого ответа число вариантов уменьшилось в два раза. Остались только «4» и «5». Получен 1 информации.
Второй вопрос:
- Ты получил «пятерку»?
- Нет.
Выбран один вариант из двух оставшихся: оценка — «четверка». Получен еще 1 информации. В сумме имеем 2 бита.
Сообщение об одном из четырех равновероятных результатов некоторого события несет 2 бита информации.
Разберем еще одну частную задачу, а потом получим общее правило.
На книжном стел лаже восемь полок. Книга может быть поставлена на любую из них. Сколько информации содержит сообщение о том, где находится книга?
Будем действовать таким же способом, как в предыдущей задаче. Метод поиска, на каждом шаге которого отбрасывается половина вариантов, называется методом половинного деления. Применим метод половинного деления к задаче со стеллажом.
Задаем вопросы:
- Книга лежит выше четвертой полки?
- Да.
- Книга лежит выше шестой полки?
- Нет.
- Книга — на шестой полке?
- Нет.
- Ну теперь все ясно! Книга лежит на пятой полке!
Каждый ответ уменьшал неопределенность в два раза. Всего было задано три вопроса. Значит, набрано 3 бита информации. И если бы сразу было сказано, что книга лежит на пятой полке, то этим сообщением были бы переданы те же 3 бита информации.
Заметим, что поиск значения методом половинного деления наиболее рационален. Таким способом всегда можно угадать любой из восьми вариантов за три вопроса. Если бы, например, поиск производился последовательным перебором: «Книга на первой полке?» — «Нет». — «На второй полке?» — «Нет» и т. д., то про пятую полку мы бы узнали после пяти вопросов, а про восьмую — после восьми.
Главная формула информатики
А сейчас попробуем получить формулу, по которой вычисляется количество информации, содержащейся в сообщении о том, что имел место один из множества равновероятных результатов некоторого события.
Обозначим буквой N количество возможных результатов события, или, как мы это еще называли, — неопределенность . Буквой i будем обозначать количество информации в сообщении об одном из N результатов.
В примере с монетой: N = 2, i = 1 .
В примере с оценками: N = 4, i = 2 бита.
В примере со стеллажом: N = 8, i = 3 бита.
Нетрудно заметить, что связь между этими величинами выражается следующей формулой:
2 i = N.
Действительно: 2 1 = 2 ; 2 2 = 4 ; 2 3 = 8.
С полученной формулой вы уже знакомы из базового курса информатики, и еще не однажды мы с ней встретимся. Значение этой формулы столь велико, что мы назвали ее главной формулой информатики. Если величина N известна, a i неизвестно, то данная формула становится уравнением для определения i. В математике оно называется показательным уравнением.
Пусть на стеллаже не 8, а 16 полок. Чтобы ответить на вопрос, сколько информации содержится в сообщении о месте нахождения книги, нужно решить уравнение:
2 i = 16.
Поскольку 16 = 2 4 , то i = 4 бита.
Количество информации (i) содержащееся в сообщении об одном из N равновероятных результатов некоторого событий, определяется из решения показательного уравнения: 2 i = N.
Если значение N равно целой степени двойки (4, 8,16, 32, 64 и т. д.), то показательное уравнение легко решить в уме, поскольку i будет целым числом. А чему, например, равно количество информации в сообщении о результате бросания игральной кости, у которой имеется шесть граней и, следовательно, N = 6? Можно догадаться, что решение уравнения
2 i = 6
будет дробным числом, лежащим между 2 и 3, поскольку 2 2 = 4 < 6, а 2 3 = 8 > 6. А как точнее узнать это число?
Пока ваших математических знаний недостаточно для того, чтобы решить это уравнение. Вы научитесь этому в 11-м классе в курсе математики. А сейчас сообщим, что результатом решения уравнения для N = 6 будет значение i = 2,58496 бита с точностью до пяти знаков после запятой.
Система основных понятий