Изменение содержимого ячейки.

1. Дважды щелкните ячейку, содержимое которой необходимо изменить.
2. Измените содержимое ячейки.
3. Чтобы ввести или отменить изменения, нажмите клавишу ENTER или ESC.

— Чтобы включить или выключить редактирование непосредственно в ячейке, выберите команду Параметры в меню Сервис, выберите закладку Правка, а затем установите или снимите флажок "Правка прямо в ячейке". Если флажок "Правка прямо в ячейке" снят, формулы можно изменять только в строке формул.

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

1. Чтобы удалить содержимое ячейки, переведите в неё курсор мышью или клавишами и нажмите Delete.

Присваивание имен ячейкам рабочей книги

Для ссылки на ячейки в столбцах и строках можно использовать заголовки этих столбцов и строк листа. Также для представления ячеек, диапазонов ячеек, формул или констант (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Выражение и результат вычисления выражения константами не являются.) можно создавать имена.

Имя. Слово или строка знаков, представляющее ячейку, диапазон ячеек, формулу или константу. Понятные имена, такие как «Продукты», используют для ссылок на диапазоны, названия которых трудно запомнить, например, Продажи!C20:C30. Заголовки можно использовать в формулах, содержащих ссылки на данные на том же листе; для представления группы ячеек, находящейся на другом листе, этой группе следует присвоить имя.

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

Для присвоения имени ячейке или группе ячеек

1. Выделите ячейку, диапазон ячеек или несмежный диапазон, которому присваивается имя.

2. Щелкните поле Имя, расположенное у левого края строки формул (Строка формул. Панель в верхней части окна Microsoft Excel, которая используется для ввода или изменения значений или формул в ячейках или на диаграммах. На ней отображается константа или формула, содержащаяся в активной ячейке.).

3. Введите имя для назначения этим ячейкам.

4. Нажмите клавишу ENTER.

При изменении содержимого ячейки ей нельзя присвоить имя.

Тема 4: Блоки (диапазоны).

1. Блок – это прямоугольная группа связанных ячеек. Другое его название, встречающееся в литературе, - диапазон. Частным случаем блока является один столбец или одна строка или даже одна ячейка. Обозначается блок через адреса левой верхней и правой нижней ячейки через “:”, например А1:Е6. В функциях, как Вы помните, в качестве аргумента обычно используется блок. Например, обычно формат устанавливается для группы ячеек, которые выделяются как блок, а затем для них всех задается формат.

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

Для выделения строки надо щелкнуть мышью на номер строки или нажать Shift+пробел.

Для выделения столбца надо щелкнуть мышью на название столбца или нажать Ctrl+пробел.

Задание имен ячейкам и блокам. Хотя для работы достаточно использовать координаты, но часто бывает удобнее давать блокам (в т.ч. ячейкам) запоминающиеся имена. Для этого надо выделить блок и ввести его имя в поле “Имя” в строке формул. Чтобы просмотреть список имен надо раскрыть список поля “Имя”.

Эти операции можно осуществлять через “Меню”, Вставка, Имя. Удаление имени лучше осуществлять через Меню.

Тема 5: Копирование формул, фиксация ячеек.

Редактирование формул. Нередко в процессе работы надо исправить или дополнить формулу. Для этого выделяется соответствующая ячейка и нажимается клавиша F2 – Excel переходит в режим редактирования. Теперь в строке формул можно изменить содержимое выражения, используя клавиши: Home, End, ß, à, Del и Забой (BackSpace). Т.е. можно работать как в обычном редакторе, только однострочном. Выход из этого режима клавишей Enter.

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

Копирование формул. При копировании формулы, она изменяется таким образом, чтобы соответствовать расположению ячейки, в которую она копируется. Например, если мы копируем ячейку С4 в Е4 и она содержит =С2*С3, то в результате копирования мы получим =Е2*Е3. Аналогично, если мы копируем по столбцу, например С4 в С14, то получим =С12*С13. Т.е. при копировании по строке изменяются буквы, а по столбцу – номера. При копировании с изменением и столбца и строки изменяются и буквы и номера. Если адрес ячейки при копировании не должен изменяться, или может изменяться только по столбцу или только по строке, то применяется фиксация ячейки, Например: $D$12, $D12, D$12

Сводная таблица.

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

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

Сводная таблица создается с помощью мастера сводных таблиц, используемого для размещения и объединения анализируемых данных. Чтобы начать создание сводной таблицы, выберите команду Сводная таблица в меню Данные.

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

Лекция № 2 (10) Excel.

Тема 6: Отображение таблицы в Excel. Раздел Формат.

Типы данных в программировании в Excel.

Тема 7: Разделы Вставка и Правка.

Тема 8: Построение диаграмм.

Тема 6: Отображение таблицы в Excel. Раздел Формат.

Таблица Excel отображается на одном Листе. Часто на Листе располагается несколько таблиц. Основным элементом таблицы является ячейка. Они объединяются в строки, столбцы и диапазоны (блоки). Команда Формат ячейки включает следующие вкладки: Число, Выравнивание, Шрифт, Граница, Вид, Защита.

Вкладка Число задаёт формат данных в ячейке:

- Общий

- Числовой

- Денежный

- Финансовый

- Дата

- Время

- Процентный

- Дробный

- Экспоненциальный

- Текстовый

- Дополнительный (все форматы)

Реально в Excel используется только два типа данных – Число и Текст. Форматы Дата и Время являются частным случаем типа данных Число. Формат Общий означает, что тип и формат данных Excel должна выбрать сама. И, разумеется, уж она выберет!!! Поэтому обычно применение пользователем этого типа данных является ошибкой (бальной!).

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

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

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

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

Формат Текстовый является фактически комментарием. Он не несёт для Excel никакой реальной информации, при использовании в арифметических операциях считается равным нулю и, главным образом, служит для пояснения содержимого таблицы пользователю. Содержимое ячейки автоматически считается текстом, если оно начинается с буквы (включая знак пробела). Если текст начинается с цифры, то перед ним надо поставить апостроф – < ' >. В самой ячейке апостроф не будет отображаться, в ней будет виден только сам текст, а в строке формул апостроф будет виден.

Формат Дополнительный (все форматы) включает несколько дополнительных (специальных) форматов, полезных при экспорте и импорте из баз данных. Указание " (все форматы) " является не вполне корректным.

Вкладка Выравнивание задаёт размещение данных в ячейке. Она включает опции:

- Выравнивание по горизонтали,

- Выравнивание по вертикали,

- Ориентация,

- Направление текста,

а также флажки Отображение:

o переносить по словам,

o автоподбор ширины,

o объединение ячеек.

Опция Выравнивание по горизонтали аналогична Word'у, но кроме 4-х имеющихся там вариантов включает ещё: по значению, с заполнением, по центру выделения и распределённый.

Вариант В ыравнивание по значению позволяет по расположению данных в ячейке определить их тип. Напоминаю, что в Excel фактически существует 2 типа данных – текстовый и числовой. Текстовый тип данных (формат Текстовый) выравнивается по левому краю, а Числовой (все остальные форматы) по правому.

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

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

Вариант Распределённый позволяет распределить данное по всей ширине ячейки. В отличие от Выравнивание по ширине это справедливо и для последней (единственной) строке абзаца. Разумеется данное должно представлять собой набор слов. Флажок Распределять по ширине отменяет Отступ. Параметр Отступ задаёт величину отступа абзаца справа, слева или с обеих сторон.

Опция Выравнивание по вертикали позволяет распределить данное по высоте ячейки. Она содержит варианты: по центру, по нижнему краю, по высоте и распределённый. Разницу между этими вариантами требуется определить самостоятельно. Разумеется, необходимо при использовании этой опции установить флажок переносить по словам.

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

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

Вкладка Шрифт задаёт параметры шрифта и является частным случаем аналогичной вкладки Word'а. Флажок Обычный задаёт параметры шрифта по умолчанию (Arial, обычный, 10). Эти параметры можно изменить через Сервис, Параметры, Общие, Стандартный шрифт.

Вкладка Граница позволяет задать Вид, Цвет, и Расположение линий в таблице. Её функции и возможности очень похожи на действие команды Границы в Word'е.

Вкладка Вид – это заливка ячеек.

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

Команда Формат ячейки включает:

- Высота – позволяет задать высоту строки числом;

- Автоподбор высоты,

- Скрыть,

- Отобразить.

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

Построение диаграмм в Excel.

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

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

В объемной диаграмме ось Z направлена вертикально, а ось Х (расстояние) и ось Y (ширина) представляют две стороны основания диаграммы.

Для диаграмм используется следующая терминология:

- Ряды данных. На графике в качестве ряда данных отображаются значения, задаваемые в виде блока, обычно строки или столбца. Эти данные отображаются в виде графических элементов диаграммы, зависящих от ее типа, например полос, секторов, линий и т.п. На одной диаграмме может быть отображено несколько рядов данных, например: Цена, Количество и Стоимость. В этом случае желательно, чтобы значения разных рядов не отличались друг от друга на несколько порядков, иначе малые значения будут не видны.

- Категории – это число элементов в ряду данных. Во многих случаях все ряды данных имеют одинаковое количество элементов, а значит в диаграмме используется только одна категория. Но в некоторых диаграммах используется несколько категорий.

- Легенда. Определяет отдельные элементы диаграммы.

- Сетка. Является продолжением деления осей, улучшая восприятие и анализ данных на диаграмме.

В Excel можно строить диаграммы разных типов. Выбор типа диаграммы зависит как от характера данных, так и от вида их анализа. Большинство типов диаграмм позволяют представлять данные как в плоском, так и в объемном виде. Объемные диаграммы выглядят более эффектно и позволяют подчеркнуть различие между разными наборами данных. Но 3-хмерное представление данных часто затрудняет их анализ. Основными типами диаграмм являются:

· Гистограмма

· Линейчатая

· График

· Круговая

· Точечная

· С областями

· Кольцевая

· Лепестковая

· Поверхность

· Пузырьковая

· Биржевая

· Коническая, Цилиндрическая и Пирамидальная

Гистограмма показывает изменение данных за определенный период времени и иллюстрирует соотношение отдельных значений данных. Категории располагаются по горизонтали, а значения по вертикали. Таким образом уделяется большее внимание изменениям во времени. Гистограмма с накоплением демонстрирует вклад отдельных элементов в общую сумму. В трехмерной гистограмме сравнение данных производится по двум осям. Показанная на рисунке трехмерная диаграмма позволяет сравнить объемы продаж в Европе за каждый квартал с объемами продаж в двух других регионах. Используется для иллюстрации соотношения между отдельными значениями ряда.

Линейчатая диаграмма отражает соотношение отдельных компонентов. Категории расположены по горизонтали, а значения по вертикали. таким образом уделяется большее внимание сопоставлению значений и меньшее - изменениям во времени. Линейчатая диаграмма с накоплением показывает вклад отдельных элементов в общую сумму. Удобна для сравнения значений разных рядов данных в один момент времени.

График отражает тенденции изменения данных за равные промежутки времени.

Круговая диаграмма показывает как абсолютную величину каждого элемента ряда данных, так и его вклад в общую сумму. На круговой диаграмме может быть представлен только один ряд данных. Такую диаграмму рекомендуется использовать, когда необходимо подчеркнуть какой-либо значительный элемент. Для облегчения работы с маленькими долями диаграммы в основной диаграмме их можно объединить в один элемент, а затем разбить их в отдельную диаграмму рядом с основной. Удобна для отображения относительного соотношения между частями относительно целого.

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

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

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

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

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

Пузырьковая диаграмма является разновидностью точечной диаграммы. Размер маркера данных указывает значение третьей переменной. При подготовке данных следует расположить в одной строке или столбце все значения переменной x, а соответствующие значения y - в смежных строках или столбцах.

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

Коническая, Цилиндрическая и Пирамидальная. Маркеры данных в виде конуса, цилиндра и пирамиды могут придавать впечатляющий вид объемным гистограммам и объемным линейчатым диаграммам.

Создание диаграммы

Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются каждый раз, когда изменяются данные на листе.

Можно создать либо внедренную диаграмму, либо лист диаграммы.

1. Выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме.

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

2. Нажмите кнопку Мастер диаграмм.

3. Следуйте инструкциям мастера.

· Создание стандартной диаграммы за один шаг

Типом диаграммы, использующимся по умолчанию в Microsoft Excel, является гистограмма.

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

· Для создания внедренной диаграммы, использующей стандартный тип диаграммы, выделите необходимые данные, а затем нажмите кнопку Создать диаграмму текущего типа. Если кнопка Создать диаграмму текущего типа отсутствует, добавьте ее на панель инструментов.

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

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

1 Выберите элемент диаграммы, к которому нужно добавить рисунок.

2 Нажмите на стрелку рядом с кнопкой Цвет заливки, выберите цвет заливки, а затем — вкладку Рисунок.

3 Укажите нужный рисунок.

4 В поле Папка выберите диск, папку или адрес Интернета, где находится нужный рисунок, а затем дважды щелкните его кнопкой мыши.

5 Укажите нужные параметры на вкладке Рисунок.

Для получения справки по конкретному параметру нажмите кнопку с вопросительным знаком и выберите нужный параметр.

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

Лекция № 3 (11) Функции Excel.

Тема № 9. Функции Excel.

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

Microsoft Excel поддерживает обе системы дат: система дат 1900 и 1904. По умолчанию, Microsoft Excel для Windows использует систему дат 1900. Чтобы воспользоваться системой дат 1904, выберите команду Параметры в меню Сервис, а затем — вкладку Вычисления. Установите флажок Система дат 1904.

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

Система дат	Первая дата	Последняя дата
	1 января 1900 г. (значение 1)	31 декабря 9999 г. (значение 2958525)
	2 января 1904 г. (значение 1)	31 декабря 9999 г. (значение 2957063)

Чтобы изменить систему дат, установите или снимите флажок система дат 1904 на вкладке Вычисления в пункте Параметры (меню Сервис).

В числовом формате даты цифры справа от десятичной запятой представляют время; цифры слева от десятичной запятой представляют дату. Например, в системе дат 1900, дата в числовом формате 367,5 представляет код даты и времени: соответствующий 12 часам дня 1 января 1901 года.

¨ ТДАТА – Возвращает текущую дату и время в числовом формате.

Синтаксис: ТДАТА()

Функция ТДАТА меняет свое значение только при расчете листа Excel или при выполнении макроса, содержащего эту функцию.

§ ДАТАВозвращает дату в числовом формате для заданной даты. Для получения более подробной информации о числовом формате даты см. ТДАТА.

Синтаксис: ДАТА(год; месяц; день)

Год - это число от 1900 до 2078.

Месяц - это число, представляющее номер месяца в году. Если месяц больше 12, то это число прибавляется к первому месяцу указанного года. Например, ДАТА(90;14;2) возвращает числовой формат даты 2 февраля 1991 года.

День - это число, представляющее номер дня в месяце. Если день больше числа дней в указанном месяце, то это число прибавляется к первому дню указанного месяца. Например, ДАТА(91;1;35) возвращает числовой формат даты 4 февраля 1991 года.

· Функция ДАТА наиболее полезна в формулах, в которых год, месяц и день являются формулами, а не константами.

¨ ДАТАЗНАЧ – Возвращает числовой формат даты, представленной в виде текста. Функция ДАТАЗНАЧ используется для преобразования даты из текстового представления в числовой формат.

Синтаксис: ДАТАЗНАЧ(дата_как_текст)

· Большинство функций автоматически преобразует даты в числовой формат.

Примеры ДАТАЗНАЧ("22.8.55") равняется 20323

¨ ДЕНЬ – Возвращает номер дня в месяце для даты в числовом формате. День возвращается как целое число диапазоне от 1 до 31.

Синтаксис: ДЕНЬ(дата_в_числовом_формате)

¨ МЕСЯЦ – Возвращает месяц, соответствующий аргументу дата_в_числовом_формате. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь).

Синтаксис: МЕСЯЦ(дата_в_числовом_формате)

¨ СЕГОДНЯ – Возвращает текущую дату в числовом формате. Числовой формат даты - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Синтаксис: СЕГОДНЯ()

¨ ДЕНЬНЕД – Возвращает день недели, соответствующий аргументу дата_в_число­вом_формате. День недели определяется как целое в интервале от 1 (Воскресенье) до 7 (Суббота).

Синтаксис: ДЕНЬНЕД(дата_в_числовом_формате;тип)

Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Аргумент дата_в_числовом_формате можно задать как текст, например, как "15 Апр 1993" или "4.15.93", а не как число.

Тип - это число, которое определяет тип возвращаемого значения.

Тип Возвращаемое число

1 или опущен Число от 1 (Воскресенье) до 7 (Суббота). Аналогично предыдущей версии Microsoft Excel.

2 Число от 1 (Понедельник) до 7 (Воскресенье)

3 Число от 0 (Понедельник) до 6 (Воскресенье)

Можно использовать функцию ТЕКСТ, чтобы преобразовать значение в нужный числовой формат при использовании системы дат 1900:

ТЕКСТ("4.16.90"; "ДДДД") равняется Понедельник

¨ ГОД – Возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое в интервале 1900-9999.

Синтаксис: ГОД(дата_в_числовом_формате)

Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Можно задать аргумент дата_в_числовом_формате как текст, например, как "19 Мар 1955" или "19.3.55", а не как число. Текст автоматически преобразуется в дату в числовом формате.

¨ ДОЛЯГОДА – Возвращает долю года, которую составляет количество дней между двумя датами (начальной и конечной). Функция ДОЛЯГОДА служит для определения доли общегодовых гонораров или обязательств, приходящихся на указанный период.

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

Синтаксис: ДОЛЯГОДА(нач_дата;кон_дата;базис)

Нач_дата - это дата, которая соответствует начальной дате.

Кон_дата - это дата, которая соответствует конечной дате.

Базис - это тип используемого способа вычисления дня.

Базис Способ вычисления дня

0 или опущен US (NASD) 30/360

1 Фактический/фактический

2 Фактический/360

3 Фактический/365

4 Европейский 30/360

· Все аргументы усекаются до целых.

· Если нач_дата или кон_дата не являются допустимой датой, то функция ДОЛЯГОДА возвращает значение ошибки #ЧИСЛО!.

· Если базис < 0 или если базис > 4, то функция то функция ДОЛЯГОДА возвращает значение ошибки #ЧИСЛО!.

¨ ЧАС – Возвращает час, соответствующий заданной дате в числовом формате. Час определяется как целое в интервале от 0 (12:00 AM) до 23 (11:00 PM).

Синтаксис: ЧАС(дата_в_числовом_формате)

¨ МИНУТЫ – Возвращает минуты, соответствующие аргументу дата_в_чис­ло­вом_фор­мате. Минуты определяются как целое в интервале от 0 до 59.

Синтаксис: МИНУТЫ(дата_в_числовом_формате)

¨ СЕКУНДЫ – Возвращает секунды, соответствующее аргументу дата_в_числовом_фор­мате. Секунды определяются как целое в интервале от 0 (нуля) до 59. Функция СЕКУНДЫ используется, чтобы получить секунды момента времени, заданного датой в числовом формате.

Синтаксис: СЕКУНДЫ(дата_в_числовом_формате)

¨ ВРЕМЯ – Возвращает дату в числовом формате для заданного времени. Дата в числовом формате, возвращаемая функцией ВРЕМЯ - это десятичная дробь в интервале от 0 до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера).

Синтаксис: ВРЕМЯ(часы;минуты;секунды)

Часы - это число от 0 (нуля) до 23, представляющее час.

Минуты - это число от 0 до 59, представляющее минуту.

Секунды - это число от 0 до 59, представляющее секунду.

Примеры

ВРЕМЯ(12; 0; 0) равняется 0,5, что эквивалентно 12:00:00

ВРЕМЯ(16; 48; 10) равняется 0,700115741, что эквивалентно 16:48:10

ТЕКСТ(ВРЕМЯ(23; 18; 14); "ЧЧ:ММ:СС") равняется "23:18:14"

¨ ВРЕМЗНАЧ – Возвращает числовой формат для времени, представленного аргументом время_как_текст. Время в числовом формате - это десятичная дробь в интервале от 0 до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера). Функция ВРЕМЗНАЧ используется для преобразования времени, представленного в виде текста, в числовой формат времени.

Синтаксис: ВРЕМЗНАЧ(время_как_текст)

Время_как_текст - это текстовая строка, содержащая время в любом формате, допустимом в Microsoft Excel. Информация о дате в аргументе время_как_текст игнорируется.

Функции обработки текста

С помощью функций обработки текста можно с помощью формул производить действия над строкам текста — например, изменить регистр или определить длину строки. Можно также объединить несколько строк в одну. В примере ниже показано, как с помощью функций СЕГОДНЯ и ТЕКСТ создать сообщение, содержащее текущую дату и привести его к виду "дд-ммм-гг". ="Балансовый отчет от "&ТЕКСТ(СЕГОДНЯ(),"дд-мм-гг")

Список функций обработки текста:

СИМВОЛ

ПЕЧСИМВ

КОДСИМВ

СЦЕПИТЬ

РУБЛЬ

СОВПАД

НАЙТИ

ФИКСИРОВАННЫЙ

ЛЕВСИМВ

ДЛСТР

СТРОЧН

ПСТР

ПРОПНАЧ

ЗАМЕНИТЬ

ПОВТОР

ПРАВСИМВ

ПОИСК

ПОДСТАВИТЬ

Т

ТЕКСТ

СЖПРОБЕЛЫ

ПРОПИСН

ЗНАЧЕН

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

Операционная среда Таблица символов

Macintosh Таблица символов Macintosh

Windows ANSI

Синтаксис – СИМВОЛ(число)

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

Примеры

СИМВОЛ(65) равняется "A"

СИМВОЛ(33) равняется "!"

ПЕЧСИМВ – Удаляет все непечатаемые символы из текста. Функция ПЕЧСИМВ используется в том случае, когда текст, импортированный из другого приложения, содержит символы, которые не могут быть напечатаны операционной системой. Например, можно использовать функцию ПЕЧСИМВ, чтобы удалить низкоуровневые компьютерные коды, которые часто встречаются в начале или в конце файла данных и не могут быть напечатаны.

Синтаксис – ПЕЧСИМВ(текст)

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

Примеры

Поскольку СИМВОЛ(7) возвращает непечатаемый символ:

ПЕЧСИМВ(СИМВОЛ(7)&"текст"&СИМВОЛ(7)) равняется "текст"

КОДСИМВ – Возвращает числовой код первого символа в текстовой строке. Возвращаемый код соответствует таблице символов, используемой на данном компьютере.

Синтаксис – КОДСИМВ(текст)

Текст - это текст, в котором требуется узнать код первого символа.

Примеры

КОДСИМВ("A") равняется 65

КОДСИМВ("Alphabet") равняется 65

СЦЕПИТЬ – Объединяет несколько текстовых строк в одну.

Синтаксис – СЦЕПИТЬ (текст1;текст2;...)

Текст1, текст2,... - это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.

Вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор "&".

Примеры

СЦЕПИТЬ("Суммарное "; "Значение") равняется "Суммарное Значение". Это эквивалентно выражению "Суммарное"&" "&"Значение".

Пусть имеется рабочий лист, в котором собраны данные о видах рыб, обитающих в реке, и пусть ячейка C2 содержит "вида", ячейка C5 содержит " речная форель" и ячейка C8 содержит 32. Тогда

СЦЕПИТЬ("Численность популяции ";C2;" ";C5;" составляет ";C8;" на километр.") равняется "Численность популяции вида речная форель составляет 32 на километр."

РУБЛЬ – Преобразует число в текст, используя денежный формат с округлением до заданного числа десятичных знаков. Используется следующий формат: # ##0,00 р.;- # ##0,00 р.

Синтаксис – РУБЛЬ(число; число_знаков)

Число - это либо число, либо ссылка на ячейку, содержащую число, либо формула, вычисление которой дает число.

Число_знаков - это число цифр справа от десятичной запятой. Если число_знаков отрицательно, то число округляется слева от десятичной запятой. Если число_знаков опущено, то оно полагается равным 2.

Наибольшее различие между форматированием ячейки, содержащей число, с помощью команды Ячейка в меню Формат и форматированием числа непосредственно с помощью функции РУБЛЬ состоит в том, что функция РУБЛЬ преобразует свой результат в текст. Число, которое форматируется с помощью команды Ячейка по-прежнему остается числом. Однако, можно продолжать использовать числа, отформатированные функцией РУБЛЬ в формулах, потому что Microsoft Excel преобразует числа, введенные как текст, в числовые значения в процессе вычислений.

Примеры

РУБЛЬ(1234,567; 2) равняется "1234,57 р."

РУБЛЬ(1234,567; -2) равняется "1200 р."

РУБЛЬ(-1234,567; -2) равняется "-1200 р."

РУБЛЬ(-0,123; 4) равняется "-0,1230 р."

РУБЛЬ(99,888) равняется "99,89 р."

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

Синтаксис – СОВПАД(текст1; текст2)

Текст1 - это первая текстовая строка.

Текст2 - это вторая текстовая строка.

Примеры

СОВПАД("слово"; "слово") равняется ИСТИНА

СОВПАД("Слово"; "слово") равняется ЛОЖЬ

СОВПАД("с лово"; "слово") равняется ЛОЖЬ

Чтобы проверить, соответствует ли введенное пользователем значение одному из значений, заданных интервалом, следует ввести следующую формулу в ячейку как массив. Чтобы ввести формулу как массив в отдельную ячейку, нажмите клавиши CTRL+SHIFT+ENTER в Microsoft Excel для Windows или +ENTER в Microsoft Excel для Macintoch. Имя ПроверяемоеЗначение ссылается на ячейку, содержащую введенное пользователем значение; имя ИнтервалДляСравнений ссылается на список текстовых значений, с которыми производится сравнение.

{=ИЛИ(СОВПАД(ПроверяемоеЗначение; ИнтервалДляСравнений))}

НАЙТИ – Находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и начальную положение начала искомого текста относительно крайнего левого символа просматриваемого текста. Для поиска вхождений одной текстовой строки в другую текстовую строку можно использовать также функцию ПОИСК, но в отличие от функции ПОИСК, функция НАЙТИ учитывает регистр и не допускает символов шаблона.

Синтаксис – НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)

Искомый_текст - это искомый текст.

· Если искомый_текст - это "" (пустая строка), то функция НАЙТИ считает подходящим первый символ в просматриваемой строке (то есть возвратит значение аргумента нач_позиция или 1).

· Искомый_текст не должен содержать никаких символов шаблона.

Просматриваемый_текст - это текст, содержащий искомый текст.

Нач_позиция - это позиция символа, с которой следует начинать поиск. Первый символ в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1.

· Если искомый_текст не входит в просматриваемый_текст, то функция НАЙТИ возвращает значение ошибки #ЗНАЧ!.

· Если нач_позиция меньше или равна нулю, то функция НАЙТИ возвращает значение ошибки #ЗНАЧ!.

· Если нач_позиция больше длины строки просматриваемый_текст, то функция НАЙТИ возвращает значение ошибки #ЗНАЧ!.

Примеры

НАЙТИ("М";"Мадам Смирнова") равняется 1

НАЙТИ("м";"Мадам Смирнова") равняется 5

НАЙТИ("м";"Мадам Смирнова";6) равняется 8

Предположим, что на рабочем листе хранится список деталей с их серийными номерами, и нужно выделить список наименований деталей без серийных номеров. Можно использовать функцию НАЙТИ для поиска символа #, а затем функцию ПСТР, чтобы исключить серийный номер. Пусть ячейки A2:A4 содержат следующий список деталей с серийными номерами: "Керамические изоляторы #124-6745-87", "Медные катушки #12-671-6772", "Переменные сопротивления #116010".

ПСТР(A2;1;НАЙТИ(" #";A2;1)-1) возвращает "Керамические изоляторы"

ПСТР(A3;1;НАЙТИ(" #";A3;1)-1) возвращает "Медные катушки"

ПСТР(A4;1;НАЙТИ(" #";A4;1)-1) возвращает "Переменные сопротивления"

ФИКСИРОВАННЫЙ – Округляет число до заданного количества десятичных цифр, форматирует число в десятичном формате с использованием запятых и точек и возвращает результат в виде текста.

Синтаксис – ФИКСИРОВАННЫЙ(число; число_знаков; без_разделителей)

Число - это число, которое округляется и преобразуется в текст.

Число_знаков - это число цифр справа от десятичной запятой.

Без_разделителей - это логическое значение, причем если аргумент без_разделителей имеет значение ИСТИНА, то ФИКСИРОВАННЫЙ не включает разделители тысяч в возвращаемый текст. Если аргумент без_разделителей имеет значение ЛОЖЬ или опущен, то возвращаемый текст будет включать разделители как обычно.

Числа в Microsoft Excel не могут иметь более 15 значащих цифр, но число_знаков может быть задано вплоть до 127.

Если число_знаков отрицательно, то число округляется слева от десятичной запятой.

Если число_знаков опущено, то оно полагается равным 2.

Наибольшее различие между форматированием ячейки, содержащей число, с помощью команды Ячейка в меню Формат и форматированием числа непосредственно с помощью функции ФИКСИРОВАННЫЙ состоит в том, что функция ФИКСИРОВАННЫЙ преобразует свой результат в текст. Число, которое форматируется с помощью команды Ячейка, по-прежнему остается числом.

Примеры

ФИКСИРОВАННЫЙ(1234,567; 1) равняется "1234,6"

ФИКСИРОВАННЫЙ(1234,567; -1) равняется "1230"

ФИКСИРОВАННЫЙ(-1234,567; -1) равняется "-1230"

ФИКСИРОВАННЫЙ(44,332) равняется "44,33"

ПСТР – Возвращает заданное число символов из строки текста, начиная с указанной позиции.

Синтаксис – ПСТР(текст; начальная_позиция;количество_символов)

Текст - это текстовая строка, содержащая извлекаемые символы.

Начальная_позиция - это позиция первого символа, извлекаемого из текста. Первый символ в тексте имеет начальную позицию 1 и так далее.

Если начальная_позиция больше, чем длина текста, то функция ПСТР возвращает строку "" (пустой текст).

Если начальная_позиция меньше, чем длина текста, но начальная_позиция плюс количество_символов превышают длину текста, то функция ПСТР возвращает символы вплоть до конца текста.

Если начальная_позиция меньше 1, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.

Количество_символов указывает, сколько символов нужно вернуть. Если количество_символов отрицательно, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.

Примеры

ПСТР("Поток жидкости"; 1; 5) равняется "Поток"

ПСТР("Поток жидкости"; 7; 20) равняется "жидкости"

ПСТР("1234"; 5; 5) равняется "" (пустой текст)

См. также примеры к функциям КОДСИМВ и НАЙТИ.

Т – Возвращает текст, ссылка на который задается аргументом значение.

Синтаксис – Т(значение)

Значение - это проверяемое значение. Если значение является текстом или ссылается на текст, то функция Т возвращает само это значение. Если значение не является текстом и не ссылается на текст, то функция Т возвращает "" (пустой текст).

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

Примеры

Если ячейка B1 содержит текст "Снегопад", то:

Т(B1) равняется "Снегопад"

Если ячейка B2 содержит число 19, то:

Т(B2) равняется ""

Т("Истина") равняется "Истина"

Т(ИСТИНА) равняется ""

Текст – Преобразует значение в текст в заданном числовом формате.

Синтаксис: ТЕКСТ(значение; формат)

Значение - это либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение.

Формат - это числовой формат в текстовой форме с вкладки Число диалога Формат ячеек. Формат не может содержать звездочку (*) и не может быть Общим числовым форматом.

Форматирование ячейки с помощью вкладки Число (команда Ячейки... меню Формат) меняет только формат, но не значение. Использование функции ТЕКСТ преобразует значение в форматированный текст, и результат больше не участвует в вычислениях как число.

Примеры

ТЕКСТ(2,715; "0,00 р.") равняется "2,72 р."

ТЕКСТ("15.4.91"; "Д МММ, ГГГГ") равняется "15 Апр, 1991"

ЗНАЧЕН – Преобразует строку текста, отображающую число, в число.

Синтаксис – ЗНАЧЕН(текст)

Текст - это текст в кавычках или ссылка на ячейку, содержащую текст, который нужно преобразовать. Текст может быть в любом формате, допускаемом Microsoft Excel для числа, даты и времени. Если текст не удовлетворяет ни одному из этих форматов, то функция ЗНАЧЕН возвращает значение ошибки #ЗНАЧ!.

Обычно нет необходимости использовать функцию ЗНАЧЕН в формулах, поскольку Microsoft Excel автоматически преобразует текст в число при необходимости. Эта функция предназначена для совместимости с другими программами электронных таблиц.

Примеры

ЗНАЧЕН("1000 р.") равняется 1000

ЗНАЧЕН("16:48:00")-ЗНАЧЕН("12:00:00") равняется "16:48:00"-"12:00:00" равняется 0,2, то есть времени в числовом формате, эквивалентному 4 часам 48 минутам.

ЛИНЕЙН – Расчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Для получения дополнительных сведений о формулах массива нажмите кнопку.

Уравнение для прямой линии имеет следующий вид:

y = mx + b или y = m1x1 + m2x2 +... + b (в случае нескольких интервалов значений x)

где зависимое значение y является функцией независимого значения x. Значения m - это коэффициенты, соответствующие каждой независимой переменной x, а b - это постоянная. Заметим, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn;mn-1;...;m1;b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис: ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика)

ЭФФЕКТ – Возвращает фактическую годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов, составляющих год.

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

Синтаксис:

ЭФФЕКТ(номинальная_ставка;периодов_в_году)

Номинальная_ставка - это номинальная годовая процентная ставка.

Периодов_в_году - это количество периодов, составляющих год.

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

Аргументами финансовых функций часто являются следующие величины:

- будущее значение – стоимость вложения или ссуды по завершении всех отложенных платежей;

- количество выплат – общее количество платежей или периодов выплат;

- выплата – объем периодической выплаты по вложению или ссуде;

- текущее значение – начальная стоимость вложения или ссуды. Так, начальная стоимость ссуды равна, собственно, сумме займа;

- ставка – процентная ставка или скидка по вложению или ссуде;

- режим выплат – режим выплат, с которым осуществляются выплаты (в конце или в начале

Функции ссылки и автоподстановки – Если необходимо осуществлять поиск в списках или таблицах или если необходимо найти ссылку к ячейке, воспользуйтесь функциями ссылки и автоподстановки. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке — функцию ПОИСКПОЗ.

Информационные функции предназначены для определения типа данных, хранимых в ячейке. Информационные функции проверяют выполнение какого-то условия и возвращают в зависимости от результата значение ИСТИНА или ЛОЖЬ. Так, если ячейка содержит четное значение, функция ЕЧЁТН возвращает значение ИСТИНА. Если в диапазоне функций имеется пустая ячейка, можно воспользоваться функцией СЧИТАТЬПУСТОТЫ, либо ЕПУСТО.

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

§ И

§ ЛОЖЬ

§ ЕСЛИ

§ НЕ

§ ИЛИ

§ ИСТИНА

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

Синтаксис: ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина - это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.

Значение_если_ложь - это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

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

· Функция ЕСЛИ всегда возвращает значение, возвращаемое вычисленным аргументом значение_если_истина и значение_если_ложь.

· Если какой-либо аргумент функции ЕСЛИ является массивом, то при выполнении функции ЕСЛИ вычисляется каждый элемент массива. Если какой-либо из аргументов значение_если_истина или значение_если_ложь является действием, то все действия выполняются.

Примеры

В следующем примере, если значение ячейки A10 - 100, то лог_выражение имеет значение ИСТИНА и вычисляется сумма для ячеек B5:B15. В противном случае лог_выражение имеет значение ЛОЖЬ и возвращается пустой текст (""), очищающий ячейку, которая содержит функцию ЕСЛИ.

ЕСЛИ(A10=100;СУММ(B5:B15);"")

Предположим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925.

Можно написать формулу для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений с помощью следующих формул:

ЕСЛИ(B2>C2;"Превышение бюджета";"OK") равняется "Превышение бюджета"

ЕСЛИ(B3>C3;"Превышение бюджета";"OK") равняется "OK"

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

Средний Балл	Категория
Больше 89	A
От 80 до 89	B
От 70 до 79	C
От 60 до 69	D
Меньше 60	F

Тогда можно использовать вложенные функции ЕСЛИ:

ЕСЛИ(СреднийБалл>89;"A";ЕСЛИ(СреднийБалл>79;"B";ЕСЛИ(СреднийБалл>69;"C";ЕСЛИ(СреднийБалл>59;"D";"F"))))

В предыдущем примере второе предложение ЕСЛИ является в то же время аргументом значение_если_ложь для первого предложения ЕСЛИ. Аналогично, третье предложение ЕСЛИ является аргументом значение_если_ложь для второго предложения ЕСЛИ. Например, если первое лог_выражение (Среднее>89) имеет значение ИСТИНА, то возвращается значение "A". Если первое лог_выражение имеет значение ЛОЖЬ, то вычисляется второе предложение ЕСЛИ и так далее.

Функция "И". Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синтаксис: И(логическое_значение1; логическое_значение2;...)

Логическое_значение1, логическое_значение2,... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

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

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

· Если указанный интервал не содержит логических значений, то И возвращает значение ошибки #ЗНАЧ!.

Примеры

И(ИСТИНА; ИСТИНА) равняется ИСТИНА

И(ИСТИНА; ЛОЖЬ) равняется ЛОЖЬ

И(2+2=4; 2+3=5) равняется ИСТИНА

Если интервал B1:B3 содержит значения ИСТИНА, ЛОЖЬ, и ИСТИНА, то:

И(B1:B3) равняется ЛОЖЬ

Если ячейка B4 содержит число между 1 и 100, то:

И(1<B4; B4<100) равняется ИСТИНА

Предположим, что нужно вывести на экран содержимое ячейки B4, если она содержит число строго между 1 и 100 и сообщение "Значение вне интервала" в противном случае. Тогда, если ячейка B4 содержит число 104, то выражение:

ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала") равняется "Значение вне интервала", а если ячейка B4 содержит число 50, то выражение:

ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала") равняется 50

ИЛИ – Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Синтаксис: ИЛИ(логическое_значение1;логическое_значение2;...)

Логическое_значение1, логическое_значение2,... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

· Аргументы должны быть выражены логическими значениями, такими как ИСТИНА или ЛОЖЬ, массивами или ссылками, которые содержат логические значения.

· Если аргумент, который является массивом или ссылкой, содержит тексты, пустые значения или значения ошибок, то эти значения игнорируются.

· Если заданный интервал не содержит логических значений, то функция ИЛИ возвращает значение ошибки #ЗНАЧ!.

· Можно использовать функцию ИЛИ как формулу массива, чтобы проверить, имеются ли значения в массиве. Для того, чтобы ввести формулу массива, нажмите CTRL+SHIFT в Microsoft Excel 97 для Windows или +ENTER в Microsoft Excel 97 для Macintosh.

Примеры

ИЛИ(ИСТИНА) равняется ИСТИНА

ИЛИ(1+1=1;2+2=5) равняется ЛОЖЬ

Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то:

ИЛИ(A1:A3) равняется ИСТИНА

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

Синтаксис: НЕ(логическое_значение)

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

Примеры

НЕ(ЛОЖЬ) равняется ИСТИНА

НЕ(1+1=2) равняется ЛОЖЬ

ИСТИНА – Возвращает логическое значение ИСТИНА.

Синтаксис: ИСТИНА()

Можно непосредственно ввести значение ИСТИНА в ячейки и формулы без использования этой функции. Функция ИСТИНА предназначена для совместимости с другими системами электронных таблиц.

ЛОЖЬ – Возвращает логическое значение ЛОЖЬ.

Синтаксис: ЛОЖЬ()

Можно непосредственно ввести слово ЛОЖЬ в рабочий лист или в формулу, и Microsoft Excel будет интерпретировать его как логическое значение ЛОЖЬ.

Средний Балл	Категория
Больше 89	A
От 80 до 89	B
От 70 до 79	C
От 60 до 69	D
Меньше 60	F

Требуется назначить буквенную категорию числам, на которые ссылаются по имени СреднийБалл. Категории приведены в следующей таблице. Расчёт провести для каждого студента, для каждой группы из трёх человек и для всех. Сравнить расчётные и итоговые данные. Также требуется построить таблицу пересчёта из 5 бальной системы в 100 бальную. Необходимо учесть, что наличие одной двойки сразу переводит студента в низшую категорию, независимо от остальных оценок.

Ссылки на ячейку или на группу ячеек

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

По умолчанию в Microsoft Excel используются ссылки A1, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65536). Чтобы указать ссылку на ячейку, введите букву заголовка столбца, а затем номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой. Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем — ссылку на правый нижний угол диапазона. В следующей таблице представлены варианты возможных ссылок.

Чтобы сослаться на Введите

Ячейку столбца A и 10-й строки A10

Диапазон ячеек столбца A с 10-й строки по 20-ю A10:A20

Диапазон ячеек в 15-й строке со столбца B по столбец E B15:E15

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

Все ячейки между 5-й и 10-й строками включительно 5:10

Все ячейки в столбце H H:H

Все ячейки между столбцами H и J включительно H:J

Можно воспользоваться стилем, в котором и столбцы, и строки листа пронумерованы. Этот стиль, называемый R1C1, наиболее полезен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок. В стиле R1C1, после буквы “R” указывается номер строки ячейки, после буквы “C” — номер столбца.

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

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

· Воспользуйтесь трехмерными ссылками, если необходимо провести анализ данных в одних и тех же ячейках или группах ячеек листов рабочей книги. Для задания трехмерной ссылки необходимо указать ссылку или группу ссылок, а также диапазон листов. Microsoft Excel использует все листы, указанные между этими двумя листами включительно.

Лекция № 4 (12) Ошибки в Excel.

Причины возникновения ошибки #####

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

Ошибка может появиться при определении числа дней между двумя датами, а также при определении количества часов между двумя временными промежутками. В этом случае проверьте правильность ввода формулы. Время и дата в Microsoft Excel должны быть положительными. Если результатом выполнения формулы, обрабатывающей даты и значения времени, является отрицательное число, то в ячейке отобразится ошибка ####. Чтобы вывести правильное значение, выберите команду Ячейки в меню Формат, а затем — вкладку Число; выберите формат отличный от формата даты или времени.

Причины возникновения ошибки #ДЕЛ/0!

Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на ноль (0).

Возможная причина	Предлагаемые действия
В качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение (если операнд является пустой ячейкой, то ее содержимое интерпретируется как ноль).	Измените ссылку или введите ненулевое значение в ячейку, используемую в качестве делителя. Кроме того, в качестве делителя можно ввести значение #Н/Д. В этом случае ошибка #ДЕЛ/0! сменится на #Н/Д, указывающая, что значение делителя не определено.
В формуле содержится явное деление на ноль (например =5/0).	Исправьте формулу.
Используется макрос, вызывающий макрофункцию, которая возвращает в определенных случаях значение #ДЕЛ/0!	Найдите описание используемых функций и проверьте, не возвращают ли они в определенных случаях значение #ДЕЛ/0!.

Причины возникновения ошибки #Н/Д

Значение ошибки #Н/Д является сокращением термина “Неопределенные Данные”. Это значение помогает предотвратить использование ссылки на пустую ячейку. Введите в ячейки листа значение #Н/Д, если они должны содержать данные, но в настоящий момент эти данные отсутствуют. Формулы, ссылающиеся на эти ячейки, тоже будут возвращать значение #Н/Д вместо того, чтобы пытаться производить вычисления.

Возможная причина	Предлагаемые действия
Для функций ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР задан недопустимый аргумент искомое_значение.	Задайте правильный аргумент искомое_значение, например значение или ссылка, но не диапазон ссылок.
Используются функции ВПР или ГПР, просматривающие значение в несортированной таблице.	По умолчанию для функций просмотра таблиц сведения должны располагаться в возрастающем порядке. Функции ВПР и ГПР содержат аргумент интервальный_просмотр, позволяющий искать определенное значение в несортированной таблице. Чтобы найти определенное значение, аргумент интервальный_просмотр должен иметь значение ЛОЖЬ.
В формуле массива используется аргумент не соответствующий диапазону, определяющимся числом строк и столбцов, который указан в формуле массива.	Если формула массива введена в несколько ячеек, проверьте диапазон ссылок формулы на соответствие числу строк и столбцов или введите формулу массива в недостающие ячейки. Например, если формула массива введена в первые 15 ячеек столбца С (C1:C15), а сама формула ссылается на первые 10 ячеек столбца А (A1:A10), то в ячейках C11:C15 будет отображаться ошибка #Н/Д. Чтобы исправить эту ошибку, уменьшите диапазон формулы (например C1:C10) или увеличьте диапазон, на который ссылается формула (например A1:A15).
Не заданы один или несколько аргументов стандартной или пользовательской функции листа.