Целая часть числа , дробная часть числа
дни (месяцы, годы) часы (минуты, секунды)
Вид разделителя между элементами дат определяется национальными настройками операционной системы Windows. В русской версии для элементов даты это, обычно, точка (если использовать при вводе значки “–“ или “/”, они после нажатия клавиши Enter также будут преобразованы в точки); для элементов времени – это двоеточие. Дни отделяются от часов пробелом.
Основная единица времени в Excel – один день. Каждый день имеет порядковый номер, начиная с 1, который соответствует 1 января 1900г (начало отсчета дат в Excel). Например, 1 января 2001г. хранится в виде числа 36892, поскольку именно столько дней прошло с 1 января 1900г. Описанный способ хранения дат позволяет их обрабатывать точно так же, как и обычные числа, например, находить дату, отстоящую от любой другой даты на желаемое число дней в будущем или прошлом, находить промежуток времени между двумя датами, т.е. реализовать арифметику дат.
Форматы даты позволяют отображать их, например, в одном из привычных видов: 1.01.98; 1.янв.98; 1.янв; январь 98 года и будут описаны позже. Нужно сказать, что если вводить данные сразу в виде даты, то соответствующий формат будет присвоен автоматически. Так, введенное в клетку значение5.10.01 будет правильно воспринято системой как 5 октября 2001г. При вводе дат допускается указание только двух последних цифр года. В этом случае они интерпретируются следующим образом в зависимости от диапазона, в котором они лежат:
00¸29 – с 2000г по 2029г.; 30¸99 – с 1930г по 1999г.
Допускается не указывать при дате ее год. В этом случае он считается текущим годом (системным годом компьютера). Так, ввод вида5.10 установит в клетке 5 октября текущего, например 2004, года.
Время – это дробная часть при дне-числе. Поскольку в сутках 24 часа, один час соответствует 1/24, 12 часов – значению 0,5 и т.п. Аналогично вводу даты, ввод времени возможен сразу в формате времени. Например, ввод вида 10:15:28 будет соответствовать 10 часам 15 минутам 28 секундам 0 января 1900 года, что в числовом формате равно 0,420138888888889. Арифметика дат, естественно, поддерживается и на уровне времени.
При указании времени можно игнорировать секунды и минуты. В последнем случае после часов следует обязательно ввести двоеточие. Например, если мы введем символы 6:, в клетке обнаружим 6:00 (т.е. 6 часов 0 минут). Возможно совмещение даты и времени, отделяемое пробелом. Так, ввод 7.2.99 6:12:40 соответствует 7 февраля 1999г 6 часам 12 минутам 40 секундам.
Существует быстрый способ ввода текущих в данный момент даты и времени, хранящихся в компьютере, – это клавишные комбинации Ctrl+; и Ctrl+Shift+: соответственно.
ЛОГИЧЕСКИЕ ДАННЫЕ имеют одно из двух значений – ИСТИНА или ЛОЖЬ. Они используются как индикаторы наличия/отсутствия какого-либо признака или события, а также могут являться аргументами некоторых функций. Во многих случаях вместо этих значений можно использовать цифры 1 или 0 соответственно.
МАССИВЫ не являются собственно типом данных, а только образуют организованное множество клеток или констант любого типа. Excel рассматривает массив (возможно содержащий множество клеток) как единый элемент, к которому в целом могут быть применены математические операции и операции отношений. Массив может содержать не только множество клеток, но множество констант, например, выражение {7;-4;9} описывает массив констант из трех числовых элементов. Позже мы еще вернемся к вопросу обработки массивов.
Создание формул
Сила электронных таблиц заключается в возможности помещать в них не только данные, но и формулы.
Все формулы должны начинаться со знака “=“ и могут включать константы, знаки операций, функции, адреса клеток (например =5+4/35, =12%*D4, =12*А4-SIN(D3)^2).
В Excel допустимы следующие операторы:
Арифметические операторы (перечислены в порядке приоритетов):
– инвертирование (умножение на минус 1),^ возведение в степень,
% операция процента, *, / умножение, деление, +, – сложение, вычитание.
Операции выполняются слева-направо в порядке их приоритетов, которые могут быть изменены круглыми скобками. Примеры формул:
формулы в обычной записи: клеточные формулы:
=7+5^3/(6*8)
=A5/(C7-4)+(4+F4)/(8-D5)*2,4
2 + SinD32 =2+(SIN(D3))^2.
Замечания к знаку %.
Если вы введете в ячейку число со знаком %, фактическое его значение будет в 100 раз меньше. Например, если введено 5%, запомнится число 0,05. Таким образом, вводится процент, а хранится коэффициент. Такое действие эквивалентно установлению процентного формата клетки для числа 0,05.
Ввод процентов в формулу (т.е. в выражение, начинающееся со знака равно) может иметь смысл для придания наглядности. Положим, вам нужно получить 5% от числа 200. Можно записать так =0,05*200, а можно =5%*200 или =200*5%. В обоих случаях результат будет одинаковым – 10. Знак процента может применяться и к ячейкам, например =E4%. Результатом будет одна сотая часть содержимого Е4.
Текстовый оператор – &. Оператор используется для сцепления двух строк в одну. Так, например, результатом применения оператора сцепления в формуле =“Петр”&” Кузнецов” будет фраза “Петр Кузнецов”.
Операторы отношения:=, <, >, <=, >=, < >. Операторы могут использоваться как с числовыми, так и текстовыми данными. Смысл их очевиден, кроме, может быть, знаков < >. Они означают отношение неравенства.
С помощью знаков отношения можно строить формулы вида ="F">"D" и =3>8.
Их результатом в первом случае явится слово ИСТИНА, поскольку буква F по алфавиту идет после буквы D (код буквы F больше кода буквы D). Во втором случае, по очевидным причинам, – слово ЛОЖЬ.
Применение таких формул на практике кажется малополезным, однако это не так. Пусть, например, нужно выяснить факт того, что все числа, содержащиеся в таблице в клетках A1, A2, A3 и A4, больше нуля. Это можно сделать с помощью простого выражения вида (скобки обязательны) =(A1>0)*(A2>0)*(A3>0)*(A4>0).
Если это действительно так, результатом вычислений явится
=ИСТИНА*ИСТИНА*ИСТИНА*ИСТИНА=1*1*1*1=1.
Поскольку в арифметических операциях логическое значение ИСТИНА интерпретируется как 1, а ЛОЖЬ – как 0, здесь мы получим число 1. В противном случае – 0. В дальнейшем (внутри функции ЕСЛИ()), это обстоятельство может быть правильно обработано.
Другой пример. Выяснить факт того, что только одно из A1, A2, A3, A4 больше нуля. Здесь пригодится выражение =(A1>0)+(A2>0)+(A3>0)+(A4>0).
Если, например, только А2 больше нуля то =ЛОЖЬ+ИСТИНА+ЛОЖЬ+ЛОЖЬ=0+1+0+0=1.
Если все числа отрицательны, результатом будет 0. Если положительных чисел больше одного, то результат будет больше 1 (от 2 до 4).
Замечание. В Excel возможно сравнение между собой букв и чисел и принято, что буква всегда “больше” числа. Так, например, значение клетки, содержащей пробел, будет больше любого числа. Если не обращать на это внимание, может возникнуть труднораспознаваемая ошибка, поскольку клетка, содержащая пробел, выглядит так же, как и пустая клетка, значение которой считается нулевым. Кроме операторов, в Excel имеется множество функций, которые являются важнейшим вычислительным инструментом электронных таблиц. Они будут рассмотрены в главе 4.
| Действие | Содержимое А1 |
| Щелкнуть мышью А1 и ввести знак “=” | = |
| Щелкнуть A2 и затем ввести знак “+” | =А2+ |
| Щелкнуть D4 и ввести знак “*” | =А2+D4* |
| Щелкнуть C1 и нажать Enter | =А2+D4*C1 |
| Рис. 2.4-1 |
Ссылки на ячейки могут вводиться непосредственно с клавиатуры, но могут более надежно и более быстро указываться мышью, которая используется как указка. Здесь гарантируется правильный ввод, поскольку пользователь непосредственно видит (выделяемые объекты обрамляются бегущей пунктирной линией) и выбирает именно те данные, которые он хочет включить в выражение.
| Действие | Содержимое А1 |
| Щелкнуть А1 и ввести “=СУММ(” | =СУММ( |
| Установить курсор в A2 и, удерживая кнопку мыши, перенести его в D8. Ввести знак “;” | =СУММ(А2:D8; |
| Щелкнуть E3, ввести знак “)” и нажать Enter | =СУММ(А2:D8;Е3) |
| Рис. 2.4-2 |
Положим нам нужно ввести в ячейку А1 формулу вида =А2+D4·С1. Здесь (рис. 2.4-1) следует выполнить следующую цепочку действий:
Аналогичным образом можно включать в формулы ссылки и на блоки. Положим, в А1 нужно ввести следующую (рис. 2.4-2) функцию суммирования: =СУММ(А2:D8;E3). Название функции вводится русскими буквами, а адреса клеток, естественно, латинскими.
В панели инструментов Excel имеются специальные средства, облегчающие ввод формул. Они доступны через пиктограммы Мастер функцийи Автосуммирование (для суммирования).
| A | B | C | D | E | F | G | |
| =СУММ(B2:F2) | |||||||
| =СУММ(E4:F4) | |||||||
| =СУММ() | |||||||
| Рис. 2.4-3 |
В виду большой важности, рассмотрим сейчас последнюю. Автосуммирование доступно через кнопку å на панели инструментов. С ее помощью можно очень просто реализовать функцию суммирования, практически не прикасаясь к клавиатуре. Пусть (строка 2 на рис. 2.4-3) нам нужно вычислить в клетке G2 сумму смежных ячеек области В2:F2. Для этого следует встать на ячейку G2 и щелкнуть по кнопке автосуммирования. Excel сам введет в G2 название функции и ее аргументы, а также выделит бегущей пунктирной линией предполагаемую область суммирования, так что вам останется только нажать кнопку Enter. Excel включает (обводит бегущим пунктиром) в область суммирования непрерывный участок таблицы до первого нечислового значения вверх или влево.
Пусть, в G4 нужно просуммировать данные из диапазона клеток B4:F4, среди которых есть (пока) и пустые. Щелчок на кнопке å в клетке G4 создаст функцию суммирования только для клеток Е4:F4. Однако легко исправить положение тут же выделив мышью нужную область суммирования B4:F4 и нажав Enter. Если к клетке, где вычисляется сумма, сверху/слева не примыкает никакая клетка-кандидат на суммирование (строка 6 на рисунке), кнопка автосуммирования введет только имя функции. Здесь следует поступить как и ранее – самим указать мышью объект суммирования (здесь В6:F6).
| A | B | C | |
| Рис. 2.4-4 |
Обработка массивов.Формулы, использующие представление данных как массивов, обычно вводятся в некоторый блок сразу во все его клетки. Например, пусть в столбце С (рис. 2.4-4) требуется получить произведение элементов столбцов А и В. Типичный способ – это ввод в С1 формулы вида =А1*В1 с последующим копированием вниз. Однако можно поступить и по другому. Выделить область С1:С3 будущего произведения, ввести формулу =А1:А3*B1:B3 и нажать клавиши Ctrl+Shift+Enter. Вы обнаружите, что во всех клетках области С1:С3 получены соответствующие попарные произведения, а в строке формул увидите одинаковое для всех них выражение {=А1:А3*B1:B3}.
Массивами для введенной формулы здесь являются блоки А1:А3, В1:В3 и С1:С3. Фигурные скобки возникли автоматически. Вводить их с клавиатуры бессмысленно, они возникают при нажатии клавиш Ctrl+Shift+Enter.
При работе с массивами нельзя изолированно редактировать никакую из формул массива, а только все сразу, для чего следует их все предварительно выделить. Это удобно сделать нажатием клавиш Ctrl+/, установив маркер мыши на любую из клеток, содержащих формулу массива.
В данном примере использование формул массива не дает каких-либо преимуществ, и было использовано только для иллюстрации этой технологии. Однако существует множество полезных ее применений, которые мы рассмотрим позже. Некоторые из функций Excel вообще работают только с массивами.
@ Клетки, содержащие формулы, отображают их результат, а сами формулы показываются в строке формул. Это относится только к текущей клетке. Если необходимо показать все формулы листа (например, для выявления ошибок), следует нажать клавиши Ctrl+`(значок `находится в левом верхнем углу клавиатуры – на той же клавише, что и символ Ё) или реализовать командную цепочку: меню Сервис+Параметры+вкладка Вид+флаг Формулы.