Системы обработки текстовой, табличной и графической информации

Теория

Пересчет формул в книгах

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

Осуществление пересчета в Microsoft Excel и управление этим процессом

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

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

Точность расчета

Точность 15 разрядов. Хранение и пересчет осуществляются в Microsoft Excel с точностью 15 разрядов.

В вычислениях используются хранимые, а не отображаемые, значения. Отображаемые и печатаемые значения зависят от формата, отображающего хранимые значения. Например, ячейка, в которой отображается дата «22.06.96», содержит число, которое является хранимым значением даты в ячейке. Задавая другой формат ячейки, можно изменять отображение на экране (например «22-июн-96»), но хранимое значение в ячейке не изменяется

Изменение точности вычислений. При выполнении вычислений по формуле Microsoft Excel, как правило, использует значения, хранящиеся в ячейках, на которые ссылается формула. Например, если каждая из двух ячеек содержит значение 10,005. и ячейкам присвоен денежный формат, то в каждой ячейке отображается значение 10.01 р. При сложении значений ячеек результат будет равен 20,01 р., так как Microsoft Excel складывает хранящиеся в ячейках значения 10,005 и 10,005, а не отображаемые значения. Можно изменить точность вычислений, с тем чтобы при пересчете формул вместо значений, хранящихся в ячейках, Microsoft Excel использовал отображаемые значения.

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

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

Пересчет книг, созданных более ранними версиями Microsoft Excel

Процесс первого открытия старой книги в Microsoft Excel отличается от процесса открытия книги, созданной в текущей версии Microsoft Excel, что позволяет проверить, правильно ли пересчитаны старые книги.

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

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

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

Численный диапазон

Возможности
Наибольшее число
Точность представления чисел
15 разрядов
Наибольшее число, которое можно ввести в ячейку
9.99999999999999Е307
Наибольшее положительное число
1 ,797693 1 34S623 1 E308
Наименьшее отрицательное число
2,2250738585072Е-308
Наименьшее положительное число
2.229Е-308
Наибольшее отрицательное число
-1Е-ЗО7
Длина записи для формулы
1024 знака
Число итераций
32767
Размер массива книги
Ограничивается объемом доступной оперативной памяти.

Например, массив не может ссылаться на весь столбец С С или на диапазон ячеек С1:С65536. Однако массив может ссылаться на диапазон С 1:D65535.
Число выделенных диапазонов
2048
Число аргументов функции
30
Число уровней вложенности функций
7
Число функций листа Microsoft Excel
329
Наименьшая используемая в расчетах дата
1 января 1900 г. (1 января 1904 г. для системы дат 1904)
Наибольшая используемая в расчетах дата
31 декабря 9999 г.
Наибольший временной интервал, который можно ввести
9999:99:99

Поиск и исправление ошибок в формулах

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

Исправление значения ошибки, например, #ИМЯ #ЗНАЧ! #ДЕЛ/0! #Н/Д #ССЫЛКА! #ЧИСЛО! ШУСТО!?

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

#####

Данная ошибка возникает, если столбец недостаточно широк или дата и время являются отрицательными числами.

Возможные причины и решения

Столбец недостаточно широк для отображения содержимого

Увеличьте ширину столбца. Выделите столбец, выберите команду Столбец в меню Формат, затем — команду Ширина и введите необходимую ширину.

Автоматически подберите ширину столбца. Выделите столбец, выберите команду Ячейки в меню Формат, перейдите на вкладку Выравнивание и установите флажок Автоподбор ширины.

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

Если используется система дат 1900. дата и время в Microsoft Excel должны быть положительными величинами.

При определении числа дней между двумя датами убедитесь в том, что формула составлена правильно.

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

#ЗНАЧ!

Данная ошибка возникает при использовании недопустимого типа аргумента или операнда.

Щелкните ячейку с ошибкой, нажмите появившуюся кнопку, а затем нажмите кнопку Источник ошибки, если она появится.

Возможные причины и решения В формулу вместо числа или логического значения (ИСТИНА или ЛОЖЬ) введен текст,

Microsoft Excel не может преобразовать текст в нужный тип данных. Проверьте правильность задания типов операндов или аргументов в функции или формуле, а также значений ячеек, на которые ссылается формула. Например, если ячейка А5 содержит число, а ячейка А6 содержит текст "Недоступно", формула =А5+А6 возвратит значение ошибки #ЗНАЧ!. После ввода или редактирования формулы массива нажимается клавиша ENTER

Для редактирования формулы укажите ячейку или диапазон ячеек, содержащих формулу массива, нажмите клавишу F2, а затем — клавиши CTRL+SH1ET+ENTER. Ссылка, формула или функция указана как миссии

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

Введите вместо диапазона одно значение.

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

Убедитесь в правильном указании размерности матрицы.

Используется макрос, вызывающий макрофункцию, в определенных случаях возвращающую значение #3 HA Ч!.

Найдите описание используемых функций и проверьте условия возврата значения #ЗНАЧ!. #Д ЕЛ/0!

Данная ошибка возникает при делении числа на 0 (нуль).

Щелкните ячейку с ошибкой, нажмите появившуюся кнопку, а затем нажмите кнопку Источник ошибки, если она появится.

Возможные причины и решения

В формуле содержится явное деление на ноль (например =5/0).

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

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

Измените ссылку на ячейку.

Введите ненулевое значение в ячейку, используемую в качестве делителя.

Введите значение #Н/Д. В этом случае ошибка #ДЕЛ/0! сменится на #Н/Д, указывающую, что значение делителя не определено.

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

Исправьте делитель в функции или формуле, чтобы он не был равным 0 или пустым.

#ИМЯ?

Данная ошибка возникает, если Microsoft Excel не может распознать имя. используемое в формуле.

Щелкните ячейку с ошибкой, нажмите появившуюся кнопку , а затем нажмите кнопку Источник

ошибки, если она появится.

Возможные причины и решения

Используется функция, входящая в состав настройки "Пакет анализа", которая не загружена

Установите и загрузите надстройку «Пакет анализа». ( В меню Сервис выберите команду Надстройки. В списке надстроек выберите Пакет анализа и нажмите кнопку ОК.

Выполните инструкции программы установки, если это необходимо. ) Используется имя, которое не было определено

Определите имя. Выберите команду Имя в меню Вставка, а затем команду Присвоить. Кроме того, команда Присвоить используется для добавления имени, отсутствующего в списке. Ошибка в написании имени

Проверьте орфографию. Выберите имя в строке формул, нажмите клавишу F 3. выберите требуемое имя. а затем нажмите ОК. В формуле без включенных заголовков используется заголовок

Разрешите использование заголовков. Выберите команду Параметры в меню Сервис, затем выберите вкладку Вычисления. В группе Параметры книги установите флажок допускать названия диапазонов. Ошибка в написании имени функции

Исправьте написание имени. Вставьте в формулу правильное имя функции, выполнив команду Функция меню Вставка. В формулу «веден текст, не заключенный в двойные кавычки.

Microsoft Excel пытается распознать такую запись как имя. даже если это не предполагалось. Заключите в двойные кавычки содержащийся в формуле текст. Пример правильной записи формулы, объединяющей текстовый фрагмент «Всего: » со значением ячейки В50: -"Всего: "&В50 В ссылке на диапазон ячеек пропущено двоеточие (: )

Исправьте формулу так. чтобы во всех ссылках на диапазон ячеек использовался знак двоеточия (: ). например: СУММ(А1: С10) Изменен другой лист, не заключенный в ординарные кавычки

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

Данная ошибка возникает, если значение недоступно функции или формуле. Щелкните ячейку с ошибкой, нажмите появившуюся кнопку, а затем нажмите кнопку Источник ошибки, если она появится. Возможные причины и решения. Выли введены недостающие данные и # H /Д или НД()

Замените #Н/Д новыми данными.

Примечание. #Н/Д можно ввести в ячейки, данные из которых еще не доступны. Формулы, содержащие ссылки на эти ячейки будут возвращать значение #Н/Д. В функциях ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР указано неверное значение аргумента " искомое_значение"

Задайте верный аргумент «искомое_значение». который может быть значением или ссылкой на ячейку, но не ссылкой на диапазон.

Для просмотра значений в несортированной таблице используются функции ВПР, ГПР или ПОИСКПОЗ

По умолчанию сведения для функций просмотра таблиц должны располагаться в возрастающем порядке. Функции ВПР и ГПР содержат аргумент интервальный_ просмотр, позволяющий искать определенное значение в несортированной таблице. Чтобы найти определенное значение, аргумент интервальный_просмотр должен иметь значение ЛОЖЬ.

Функция ПОИСКПОЗ содержит аргумент тип сопоставления, задающий порядок, в котором список должен быть отсортирован для поиска соответствующего значения Если невозможно найти соответствующее значение, попробуйте изменить аргумент тип_сопоставления Для поиска точного соответствия, задайте аргумент тип_сопоставления, равный 0.

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

Если формула массива введена в несколько ячеек, проверьте диапазон ссылок формулы на соответствие числу строк и столбцов или введите формулу массива в недостающие ячейки. Например, если формула массива введена в первые 15 ячеек столбца С (С1: С15), а сама формула ссылается на первые 10 ячеек столбца А (А1: А10). то в ячейках С11: С15 будет отображаться ошибка #Н/Д. Чтобы исправить эту ошибку, уменьшите диапазон формулы (например CI : С10) или увеличьте диапазон, на который ссылается формула (например А1: А15) Не заданы один или несколько необходимых аргументов стандартной или пользовательской функции листа

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

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

Проверьте правильность указания и размещения аргументов в функции.

ПОСЫЛКА!

Данная ошибка возникает, если ссылка на ячейку указана неверно. Щелкните ячейку с ошибкой, нажмите появившуюся кнопку, а затем нажмите кнопку Источник ошибки, если она появится.

Возможные причины и решения

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

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

Запустите программу. Используется ссылка на недоступный объект DDE ( Dynamic Data Exchange ), например " system "

Проверьте, что используется правильный раздел DDE . Используется макрос, вызывающий макрофункцию, в определенных случаях возвращающую значение #ССЫЛКА!.

Проверьте аргументы функции и удостоверьтесь, что они ссылаются на допустимые ячейки или диапазоны ячеек. Например, если макрос вызывает функцию из ячейки, расположенной выше первой строки, то отобразится ошибка #ССЫЛКА!, поскольку такой ячейки не существует.

#ЧИСЛО!

Данная ошибка возникает при неправильных числовых значениях в формуле или функции.

Щелкните ячейку с ошибкой, нажмите появившуюся кнопка, а затем нажмите кнопку Источник ошибки, если она появится.

Рассмотрите все возможные причины и решения.

Возможные причины и решения

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

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

Попробуйте использовать другое начальное приближение для этой функции.

Измените число итераций при расчете формул в Microsoft Excel . (В меню Сервис выберите команду Параметры, а затем— вкладку Вычисления. Установите флажок Итерации. В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа. В поле Относительная погрешность введите относительную погрешность вычислений Чем меньше погрешность, тем больше время пересчета листа и точнее результаты обработки формул. )

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

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

#ПУСТО!

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

Возможные причины и решения

Используется ошибочный оператор диапазона.

Для указания ссылки на непрерывный диапазон ячеек используйте двоеточие (:) в качестве разделителя между первой и последней ячейками диапазона. Например, СУММ(А1:А10) ссылается на диапазон ячеек с A 1 до А10 включительно.

Для создания ссылки на две непересекающиеся области используйте оператор объединения, обозначаемый точкой с запятой (;). Например, если формула суммирует два диапазона, то между диапазонами должна быть поставлена точка с запятой: СУММ(А1: А10;С 1 :С 10). Диапазоны не имеют общих ячеек

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

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

Выполните одно из следующих действий:

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

чтобы включить в диапазон больше или меньше ячеек, перетащите угол границы:

в формуле выберите ссылку, а затем введите новую.

Нажмите клавишу ENTER . Изменение ссылок ни именованный диапазон

Выполните одно из следующих действий

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

В меню Вставка укажите на пункт Имя и выберите команду Применить.

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

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

Выделите ячейки для просмотра.

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

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

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

Нажмите кнопку Добавить.

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

Чтобы изменить ширину столбца, перетащите правую границу заголовка столбца.

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

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

Можно просмотреть значения различных частей сложной формулы, вычисляемые в соответствии с порядком действий. Ниже приведен пример таких вычислений в формуле, где функция CP 3 HA Ч( F 2: F 5) показана в виде ее значения: 80.

=ЕСЛИ( CP 3 HA Ч( F 2: F 5)>50; C У MM ( G 2: G 5):0) как

=ЕСЛИ(80>50;СУММ( G 2: G 5);0)

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

В меню Сервис выберите пункт Зависимости формул и выполните команду Вычислить формулу. Нажмите кнопку Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычислений показан курсивом.

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

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

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

В меню Сервис выберите пункт Параметры и откройте вкладку Вид. Убедитесь, что в меню Объекты выбрано показать все или отображать очертания. В меню Сервис укажите на команду Зависимости формул, а затем выберите пункт Панель зависимостей.

Выполните одно из следующих действий: • Трассировка влияющих ячеек

Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки

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

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

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

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

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

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

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

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

инструментов Зависимости.

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

Чтобы выделить цветом влияющие аргументы в формуле, выделите ячейку и нажмите кнопку F 2.

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

Ссылки на ячейки и диапазоны ячеек

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

Стиль ссылок А1

По умолчанию Microsoft Excel использует стиль ссылок A 1, определяющий столбцы буквами (от А до IV . всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, а следом номер строки. Например, ссылка В2 указывает на ячейку, расположенную на пересечении столбца В и строки 2.

Ячейка или диапазон | Использование
Ячейку в столбце А и строке 10 | А10
Диапазон ячеек: столбец А. строки 10-20. | А10.А20
Диапазон ячеек: строка 15. столбцы В-Е. | В15:Е15
Все ячейки в строке 5. | 5:5
Все ячейки в строках с 5 по 10. | 5:10
Все ячейки в столбце Н. | Н:Н
Все ячейки в столбцах с Н по J . | H : J
Диапазон ячеек: столбцы А-Е. строки 10-20. | А10:Е20

Ссылка на другой лист. При этом имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек.

Различия между относительными и абсолютными ссылками

Относительные ссылки.

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

10

в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки В2 в ячейку ВЗ. она автоматически изменяется с =А1 на=А2. А бсолютные ссылки.

Абсолютная ссылка ячейки в формуле, например $ A $1. всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр Например, при копировании абсолютной ссылки из ячейки В2 в ячейку ВЗ, она остается прежней =$А$1 Смешанные ссылки.

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид SA 1, SB 1 и т. д . Абсолютная ссылка строки приобретает вид A $1, B $1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при

копировании смешанной ссылки из ячейки А2 в ячейку ВЗ, она изменяется с =А$1 на =В$1.

Стиль трехмерных ссылок

Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!В5) суммирует все значения, содержащиеся в ячейке В5 на всех листах в диапазоне от Лист2 до Лист 13 включительно.

Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС,МАКСА. МИН. МИНА. ПРОИЗВЕЛ, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, Д ИСПА, ДИСПР и ДИСПРА.

Трехмерные ссылки нельзя иснотьзовать в формулах массива (Формула массива. Формула, выполняющая

несколько вычислений над одним или несколькими наборами значении, а затем возвращающая один или несколько | результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш CTRL + SHIFT + ENTER ).

Трехмерные ссылки нельзя использовать вместе с оператором ( Оператор. Знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок.) пересечения (пробел), а также в формулах, использующих неявное пересечение (Неявное пересечение. Ссылка на диапазон ячеек вместо одной ячейки, из которого выбирается одна соответствующая ячейка. Например, если в ячейке (10 содержится формула =В5:В15*5, значение в ячейке В10 будет умножено на 5, потому что ячейки В10 и С10 находятся в одной строке).

Изменения в трехмерных ссылках при перемещении, копировании, вставке или удалении листов

Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. Предположим, что используется формула " =СУММ(Лист2:Лист6! А2:А5 )". суммирующая содержимое ячеек с А2 по А5 с лист2 по листб включительно.

Вставка или копирование. Если между листом 2 и листом 6 книги вставить новые листы. Microsoft Excel добавит в сумму содержимое ячеек с А2 по А5 на новых листах.

Удаление. Если между листом 2 и листом 6 книги удалить листы. Microsoft Excel исключит из суммы содержимое ячеек удаленных листов.

Перемещение. Если переместить листы, находящиеся между листом 2 и листом 6 книги, и разместить их таким образом, что они будут расположены перед листом 2 или после листа 6, Microsoft Excel исключит из суммы содержимое ячеек перемещенных листов.

Перемещение граничного листа. Если переместить лист 2 или лист 6 в новое место книги, Microsoft Excel включит в сумму содержимое ячеек листов, находящихся между листом 2 и листом 6 включительно.

Удаление граничного листа. Если удалить лист 2 или лист 6, Microsoft Excel включит в сумму содержимое ячеек листов, находившихся между ними.

Стиль ссылок R 1 C 1

Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R 1 C 1 полезен при вычислении положения столбцов и строк в макросах (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций. Макросы записываются на языке программирования Visual Basic для приложений). В стиле ссылок R 1 C 1 Microsoft Excel указывает положение ячейки буквой « R », за которой идет номер строки, и буквой «С», за которой идет номер столб ца.

Ссылка | Значение
R [-2] C | Относительная ссылка (Относительная ссылка. В формуле - адрес ячейки. определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется. Относительные ссылки задаются в форме А1.) на ячейку, расположенную на две строки выше и в том же столбце
R [2] C [2] | Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее

R 2 C 2 | Абсолютная ссылка (Абсолютный адрес ячейки. Часть формулы, являющаяся адресом ячейки, и ссылающаяся на данную ячейку независимо от местоположения ячейки с формулой. Абсолютный адрес ячейки имеет формат SASl .) Ha ячейку, расположенную во второй строке и во втором столбце
R [- l ] | Относительная ссылка на строку, расположенную выше текущей ячейки
R | Абсолютная ссылка на текущую строку

При записи макроса Microsoft Excel записывает некоторые команды с использованием стиля ссылок RIC 1. Например, если записывается такая команда как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Microsoft Excel использует при записи формулы стиль ссылок R 1 C 1, а не А1.

Чтобы включить или выключить стиль ссылок R 1 CI

Выберите пункт Параметры в меню Сервис и перейдите на вкладку Общие.

В меню Сервис установите или снимите флажок Стиль ссылок R 1С1.

Создание ссылки на другую ячейку, книгу или программу

Создание связи между ячейками в одной или разных книгах

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

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

Создание ссылки на ячейки в другой книге

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

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

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

Выделите ячейки, на которые требуется сослаться.

Допишите формулу. Для завершения ввода формулы нажмите клавишу ENTER .

Связывание и внедрение содержимого другой программы через интерфейс OLE

Интерфейс OLE ( Object Linking and Embedding — связывание и внедрение объектов) поддерживается множеством различных программ и используется для помещения документа, созданного в одной программе, в другую программу. Например, можно вставить документ Microsoft Word в Microsoft Excel Чтобы просмотреть, содержимое какого типа можно вставить, выберите в меню Вставка команду Объект. В поле Тип объекта отображаются только объекты программ, установленных на данном компьютере и поддерживающих интерфейс О L Е ( OLE . Технология связи программ, позволяющая приложениям совместно использовать данные. Все программы Microsoft Office поддерживают технологию OLE . что позволяет совместно работать с данными посредством внедрения и связывания объектов.).

Главным различием между связанными и внедренными объектами являются место хранения объекта и способ его обновления после помещения в конечный файл (Файл назначения . Файл, в который вставлен связанный или внедренный объект. Файл-источник содержит данные, используемые для создания этого объекта. При изменении данных в файле назначения данные в файле-источнике не изменяются).

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

Создание связи с существующим файлом

Выберите лист, на который необходимо поместить связанный объект (Связанный объект. Объект, созданный в

файле-источнике и вставленный в файл назначения с поддержанием связи между этими двумя файлами. Связанный объект в файле назначения может быть обновлен при обновлении файла-источника.).

В меню Вставка выберите команду Объект. Откройте вкладку Из файла. В поле Имя файла введите имя файла или нажмите кнопку Обзор для выбора файла из списка. Установите флажок Связь с файлом. Чтобы просмотреть содержимое, снимите флажок В виде значка. Чтобы внедренный объект отображался в виде значка. установите флажок В виде значка.

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

Внедрение документа

Выберите лист, на который необходимо поместить внедренный объект (Внедренный объект, Данные (объект), содержащиеся в файле - источнике и вставленные в файл назначения. После внедрения объект становится частью файла назначения. Изменения, вносимые во внедренный объект, отражаются в файле назначения. ).

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

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

Нажмите кнопку Копировать. Выберите лист, на который необходимо поместить данные, а затем выберите позицию. в которой данные должны находиться. В меню Правка выберите команду Специальная вставка. Для вставки данных в качестве связанного объекта выберите команду Связать. Для вставки данных в качестве внедренного объекта выберите команду Вставить. В поле Как выберите элемент, содержащий в своем имени слово «объект». Например, если данные были скопированы из документа Microsoft Word , выберите параметр Документ Microsoft Word .

Примечание, Вставить ячейки или диаграмму в качестве связанного или внедренного объекта в книгу Microsoft Excel невозможно. Однако для создания связи с ячейками или другим листом книги можно использовать команду Копировать рисунок (для этого откройте меню Правка при нажатой клавише SHIFT ).

Создание ссылки на ячейки в этой же или другой книге

Приведенные ниже формулы содержат относительные ссылки (Относительная ссылка. В формуле - адрес ячейки, определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется. Относительные ссылки задаются в форме А1. ) на другие ячейки и их имена (Имя. Слово или строка знаков, представляющие ячейку, диапазон ячеек, формулу или константу. Используйте описательные имена, такие как "Продукты", для ссылок на диапазоны, значение которых трудно запомнить, такие как Продажи!С20: С30). Ячейка, содержащая формулу, называется зависимой ячейкой, если ее значение зависит от значений в других ячейках. Например, ячейка В2 является зависимой, если она содержит формулу

Пример формулы | Описание
=С2 | Использует значение в ячейке С2
=Лист2!В2 | Использует значение в ячейке В2 на Лист2
=Ответственность-Актив | Вычитает ячейку Ответственность из ячейки Актив

Щелкните ячейку, в которую требуется ввести формулу .

В строку формул (Строка формул. Панель в верхней части окна Microsoft Excel , которая используется для ввода или изменения значений или формул в ячейках или на диаграммах. На ней отображается константа или формула, содержащаяся в активной ячейке. )Выполните одно из следующих действий. Чтобы создать ссылку, выделите ячейку, диапазон ячеек, место в другом листе или место в другой книге. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение. Чтобы создать ссылку на именованный диапазон, нажмите клавишу F 3, выберите имя в поле Вставка имени и нажмите кнопку ОК. Нажмите клавишу ENTER .

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

Для представления ячейки или диапазона ячеек в другой книге можно создавать описательные имена (Имя. Слово или строка знаков, представляющие ячейку, диапазон ячеек, формулу или константу. Используйте описательные имена, такие как "Продукты", для ссылок на диапазоны, значение которых трудно запомнить, такие как Продажи!С20: С30. ).

Ссылка на определенное имя в другой книге

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

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

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

Определение имени, ссылающегося на ячейки в другой книге

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

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

Перемещение и копирование формулы

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

Выделите ячейку, содержащую формулу.

Убедитесь, что ссылки на ячейки, используемые в формуле, дают требуемый результат. Выберите требуемый тип ссылки. Чтобы переместить формулу, используйте абсолютную ссылку (Абсолютный адрес ячейки. Часть формулы, являющаяся адресом ячейки, и ссылающаяся на данную ячейку независимо от местоположения ячейки с формулой. Абсолютный адрес ячейки имеет формат $А$1. ).

Выделите ячейку с формулой.

В строке формул (Строка формул. Панель в верхней части окна Microsoft Excel , которая используется для ввода или изменения значений или формул в ячейках или на диаграммах . На ней отображается константа или формула, содержащаяся в активной ячейке. ) выделите ссылку, которую требуется изменить. Нажмите клавишу F 4, чтобы преобразовать тип ссылки. В столбце "Новая ссылка" отображается способ изменения типа ссылки при копировании формулы, содержащей эту ссылку, на две ячейки вниз или на две ячейки вправо.

Копируемая формула

Описание | Новая ссылка
$ A $1 (абсолютный (Абсолютный адрес ячейки. Часть формулы, являющаяся адресом ячейки, и ссылающаяся на данную ячейку независимо от местоположения ячейки с формулой. Абсолютный адрес ячейки имеет формат $ A $1. ) столбец и абсолютная строка) | $ A $1
A $1 (относительный (Относительная ссылка. В формуле - адрес ячейки, определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется. Относительные ссылки задаются в форме А 1. ) столбец и абсолютная строка) | CS 1
$ A 1 (абсолютный столбец и относительная строка) | SA 3
А1 (относительный столбец и относительная строка) | C 3

В меню Правка выберите команду Копировать. Выделите ячейку, в которую требуется ее скопировать.

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

Советы

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

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

Операторы

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

Типы операторов

Арифметические операторы.

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

Арифметический оператор | Значение(пример)
+ (знак плюс) | Сложение (3+3)
— (знак минус) | Вычитание (3-1) Отрицание(-1)
* (звездочка) | Умножение (3*3)
/ (косая черта) | Деление (3/3)
% (знак процента) | Процент (20%)
^(крышка) | Возведение в степень(3^2)

Операторы сравнения.

Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.

Оператор сравнения | Значение(пример)
=(знак равенства) | Равно(А1=В1)
> (знак больше) | Больше (А1>В1)
< (знак меньше) | Меньше (А1<В1)
>= (знак больше или равно) | Больше или равно (А1>=В1)
<= (знак меньше или равно) | Меньше или равно (А1<=В1)
о (знак не равно) | Неравно(А1<>В1)

Текстовый оператор конкатенации. Амперсант (&) используется для объединения нескольких текстовых строк в одну строку.

Текстовый оператор | Значение(пример)
&(амперсант) | Объединение последовательностей знаков в одну последовательность ("Северный"&" ветер")
Оператор ссылки. Для описания ссылок на диапазоны ячеек используются следующие операторы.
Оператор ссылки | Значение(пример)
: (двоеточие) | Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (В5: В 15)
; (точка с запятой) | Оператор объединения. Объединяет несколько ссылок в одну ссылку ( C У MM ( B 5: BI 5; D 5: D 15))
(пробел) | Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов ( B 7: D 7 C 6: C 8)

Порядок выполнения действий в формулах

Формулы вычисляют значения в определенном порядке. Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том. что последующие знаки составляют формулу. Элементы , следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула вычисляется слева направо, в соответствии с определенным порядком для каждого оператора в формуле .

Приоритет оператора

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

Оператор | Описание
: (двоеточие) (один пробел) , (запятая) | Операторы ссылок.

- | Знак «минус»
% | Процент
| Возведение в степень
* и / | Умножение и деление
+ и - | Сложение и вычитание
& | Объединение двух текстовых строк в одну
= < > <= >=<> | Сравнение

Использование круглых скобок

Для того чтобы изменить порядок выполнения, заключите часть формулы, которая должна выполняться первой, в скобки. Например, результатом следующей формулы будет число 11, поскольку Microsoft Excel выполняет умножение до сложения. В данной формуле число 2 умножается на 3. а затем к результату добавляется число 5.

=5+2*3

Если же с помощью скобок изменить синтаксис. Microsoft Excel сложит 5 и 2. а затем умножит результат на 3: результатом этих действий будет число 21.

=5+2*3

В приведенном ниже примере скобки вокруг первой части формулы определяют следующий порядок вычислений: определяется значение В4+25. затем полученный результат делится на сумму значений в ячейках D 5. Е5 и F 5.

=(B4+25)/C У MM(D5: F5)

Формулы массива и константы массива

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

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

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

Использование формулы массива

Вычисление одного значения.

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

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

При вводе формулы = { C У MM ( B 2: D 2* B 3: D 3)} в качестве формулы массива она умножает «Акции» и «Цена» для каждой биржи, после чего складывает результаты этих вычислений друг с другом.

Вычисление нескольких значений.

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

Например, по заданному ряду из трех значений продаж (в столбце В) и ряду из трех месяцев (в столбце А) функция ТЕНДЕНЦИЯ определяет продолжение линейного ряда объемов продаж. Для отображения всех вычисляемых значений формула введена в три ячейки столбца С (С1: СЗ).

Формула =ТЕНДЕНЦИЯ(В1: ВЗ; А1: АЗ), введенная как формула массива, возвращает три значения (22196, 17079 и 11962), вычисленные по трем объемам продаж за три месяца.

Использование констант массива

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

Константы массива могут содержать числа, текст, логические значения, например ИСТИНА или ЛОЖЬ, или значения ошибки, такие как #Н/Д. Различные типы значений могут быть в одной константе массива, например { 1; 3; 4: ИСТИНА; ЛОЖЬ; ИСТИНА}. Числа в массиве могут быть целыми, с десятичной точкой или в экспоненциальном формате. Текст должен быть взят в двойные кавычки, например "Вторник"

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

Формат констант массива

Константы массива заключены в фигурные скобки ( { } ).

Столбцы разделяются точкой с запятой (; ). Например, чтобы представить значения 10. 20. 30 и 40. введите {10; 20: 30; 40}. Такой массив констант является матрицей размерности 1 на 4 и соответствует ссылке на 1 строку и 4 столбца.

Строки разделяются двоеточиями (: ). Например, чтобы представить значения 10. 20. 30. 40 и 50. 60, 70. 80. находящиеся в расположенных друг под другом ячейках, можно создать массив констант размерностью 2 на 4. причем строки будут отделены друг от друга двоеточиями, а значения в столбцах — точкой с запятой: {10; 20; 30: 40: 50: 60; 70; 80}.

Создание формулы массива

При вводе формулы массива ( Формула массива. Формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки | | и вводятся нажатием клавиш CTRL + SHIFT + ENTER . ) Microsoft Excel автоматически заключает ее в фигурные скобки ( {} ).

Вычисление одного значения

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

Щелкните ячейку, в которую требуется ввести формулу массива.

Введите формулу .

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

При вводе формулы = { C У MM ( B 2: D 2* B 3: D 3)} в качестве формулы массива, она умножает « Акции » и «Цена» для каждой биржи, после чего складывает результаты этих вычислений друг с другом . Нажмите сочетание клавиш CTRL - SHIFT - ENTER .

Вычисление нескольких значений

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

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

Введите формулу .

Например, по заданному ряду из трех значений продаж (столбец В) и ряду из трех месяцев (столбец А) функция ТЕНДЕНЦИЯ определяет продолжение линейного ряда объемов продаж. Для отображения всех вычисляемых значений формула введена в три ячейки столбца С (С 1: СЗ).

Формула =ТЕНДЕНЦИЯ(В1: ВЗ; А1: АЗ) введенная как формула массива, возвращает три значения (22196, 17079 и 11962), вычисленные по трем объемам продаж за три месяца. Нажмите сочетание клавиш CTRL + SHIFT + ENTER .

Список функций по категориям

Функции баз данных

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

ДСРЗНАЧ Возвращает среднее значение выбранных записей базы данных.

БСЧЁТ Подсчитывает количество числовых ячеек в базе данных.

БСЧЕТА Подсчитывает количество непустых ячеек в базе данных.

БИЗВЛЕЧЬ Извлекает из базы данных одну запись, удовлетворяющую заданному условию .

ДМАКС Возвращает максимальное значение среди выделенных записей базы данных.

ДМИН Возвращает минимальное значение среди выделенных записей базы данных.

БДПРОИЗВЕД Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию.

ДСТАНДОТКЛ Оценивает стандартное отклонение по выборке из выделенных записей базы данных.

ДСТАНДОТКЛП Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных.

БДСУММ Суммирует числа в поле для записей базы данных, удовлетворяющих условию.

БДДИСП Оценивает дисперсию по выборке из выделенных записей базы данных

БДДИСПП Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных.

ПОЛУЧИТЬ. ДАННЫЕ. СВОДНОЙ. ТАБЛИЦЫ Возвращает данные, хранящиеся в сводной таблице.

Функции даты и времени

ДАТА Возвращает заданную дату в числовом формате Microsoft Excel . ДАТАЗНАЧ Преобразует дату из текстового формата в числовой. ДЕНЬ Преобразует дату в числовом формате в день месяца.

ДНЕЙ360 Вычисляет количество дней между двумя датами на основе 360-дневного года.

ДАТАМЕС Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.

КОНМЕСЯЦА Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.

ЧАС Преобразует дату в числовом формате в часы.

МИНУТЫ Преобразует дату в числовом формате в минуты.

МЕСЯЦ Преобразует дату в числовом формате в месяцы.

ЧИСТРАБДНИ Возвращает количество рабочих дней между двумя датами.

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

СЕКУНДЫ Преобразует дату в числовом формате в секунды.

ВРЕМЯ Возвращает заданное время в числовом формате.

ВРЕМЗНАЧ Преобразует время из текстового формата в числовой формат.

СЕГОДНЯ Возвращает числовой формат текущей даты.

ДЕНЬНЕД Преобразует дату в числовом формате в день недели.

НОМНЕДЕЛИ Преобразует числовое представление в число, которое указывает, на какую неделю года приходится указанная дата.

РАБДЕНЬ Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.

ГОД Преобразует дату в числовом формате в год.

ДОЛЯГОДА Возвращает долю года, которую составляет количество дней между начальной и конечной датами.

Внешние функции

Эти функции загружаются с программами надстройки (Надстройка. Вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей. )

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

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

Инженерные функции

БЕССЕЛЬ. I Возвращает модифицированную функцию Бесселя In (х) .

БЕССЕЛЬ. J Возвращает функцию Бесселя Jn ( x ).

БЕССЕЛЬ. К Возвращает модифицированную функцию Бесселя К n (х).

БЕССЕЛЬ . Y Возвращает функцию Бесселя Yn ( x ).

ДВ. В. ДЕС Преобразует двоичное число в десятичное.

ДВ. В. ШЕСТН Преобразует двоичное число в шестнадцатиричное.

ДВ. В. ВОСЬМ Преобразует двоичное число в восьмеричное.

КОМПЛЕКСН Преобразует коэффициенты при вещественной и мнимой частях комплексного числа в комплексное число.

ПРЕОБР Преобразует число из одной системы мер в другую.

ДЕС. В. ДВ Преобразует десятичное число в двоичное.

ДЕС. В. ШЕСТН Преобразует десятичное число в шестнадцатиричное.

ДЕС. В. ВОСЬМ Преобразует десятичное число в восьмеричное.

ДЕЛЬТА Проверяет равенство двух значений.

ФОШ Возвращает функцию ошибки.

ДФОШ Возвращает дополнительную функцию ошибки.

ПОРОГ Проверяет, не превышает ли данное число порогового значения.

ШЕСТИ. В. ДВ Преобразует шестнадцатиричное число в двоичное.

ШЕСТН. В. ДЕС Преобразует шестнадцатиричное число в десятичное.

ШЕСТН. В. ВОСЬМ Преобразует шестнадцатиричное число в восьмеричное.

MH И M . ABS Возвращает абсолютную величину (модуль) комплексного числа.

МНИМ. ЧАСТЬ Возвращает коэффициент при мнимой части комплексного числа.

МНИМ. АРГУМЕНТ Возвращает значение аргумента комплексного числа (тета) - угол, выраженный в радианах.

МНИМ. СОПРЯЖ Возвращает комплексно-сопряженное комплексное число.

МНИМ. COS Возвращает косину с комплексного числа.

МНИМ. ДЕЛ Возвращает частное от деления двух комплексных чисел.

МНИМ. ЕХР Возвращает экспоненту комплексного числа.

MH И M . LN Возвращает натуральный логарифм комплексного числа.

МНИМ. LOG 10 Возвращает обычный (десятичный) логарифм комплексного числа.

MH И M . LOG 2 Возвращает двоичный логарифм комплексного числа.

МНИМ. СТЕПЕНЬ Возвращает комплексное число, возведенное в целую степень.

МНИМ. ПРОИЗВЕД Возвращает произведение двух комплексных чисел.

МНИМ. ВЕЩ Возвращает коэффициент при вещественной части комплексного числа.

МНИМ. SIN Возвращает синус комплексного числа.

МНИМ. КОРЕНЬ Возвращает значение квадратного корня из комплексного числа.

МНИМ. РАЗН Возвращает разность двух комплексных чисел

МНИМ. СУММ Возвращает сумму комплексных чисел.

ВОСЬМ. В. ДВ Преобразует восьмеричное число в двоичное.

ВОСЬМ. В. ДЕС Преобразует восьмеричное число в десятичное.

ВОСЬМ. В. ШЕСТН Преобразует восьмеричное число в шестнадцатиричное.

Финансовые функции

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

НАКОПДОХОД Возвращает накопленный доход по ценным бумагам с периодической выплатой процентов.

НАКОПДОХОДПОГАШ Возвращает накопленный доход по ценным бумагам, процент по которым выплачивается в срок вступления в силу.

АМОРУМ Возвращает величину амортизации для каждого периода, используя коэффициент амортизации.

АМОРУВ Возвращает величину амортизации для каждого отчетного периода.

ДНЕЙКУПОНДО Возвращает количество дней между началом периода купона и датой соглашения.

ДНЕИКУПОН Возвращает число дней в периоде купона, который содержит дату соглашения.

ДНЕЙКУПОНПОСЛЕ Возвращает число дней от даты соглашения до срока следующего купона.

ДАТАКУПОНПОСЛЕ Возвращает следующую дату купона после даты соглашения.

ЧИСЛКУПОН Возвращает количество купонов, которые могут быть оплачены между датой соглашения и сроком вступления в силу.

ДАТАКУПОНДО Возвращает предыдущую дату купона перед датой соглашения.

ОБЩПЛАТ Возвращает общую выплату, произведенную между двумя периодическими выплатами.

ОБЩДОХОД Возвращает общую выплату по займу между двумя периодами.

ФУО Возвращает амортизацию имущества на заданный период, используя метод постоянного учета амортизации.

ДДОБ Возвращает величину амортизации имущества для указанного периода при использовании метода двукратного учета амортизации или иного явно указанного метода.

СКИДКА Возвращает норму скидки для ценных бумаг.

РУБЛЬ. ДЕС Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.

РУБЛЬ. ДРОБЬ Преобразует цену в рублях , выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.

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

ЭФФЕКТ Возвращает действующие ежегодные процентные ставки.

БС Возвращает будущее значение вклада.

БЗРАСПИС Возвращает будущее значение начального предложения после учета ряда сложных процентных ставок.

ИНОРМА Возвращает ставку доходности полностью обеспеченной ценной бумаги.

ПРПЛТ Возвращает величину выплаты прибыли на вложения заданный период.

ВСД Возвращает внутреннюю ставку доходности (отдачи) для серии потоков денежных средств.

ПРОЦПЛАТ Вычисляет выплаты за указанный период инвестиции.

МДЛИТ Возвращает модифицированную длительность Маколея для ценных бумаг с предполагаемой номинальной стоимостью 100 рублей.

МВСД Возвращает внутреннюю ставку доходности, при которой положительные и отрицательные денежные потоки имеют разную ставку.

НОМИНАЛ Возвращает номинальную годовую процентную ставку.

КПЕР Возвращает общее количество периодов выплаты для данного вклада.

ЧПС Возвращает чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.

ЦЕНАПЕРВНЕРЕГ Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным первым периодом.

ДОХОДПЕРВНЕРЕГ Возвращает доход по ценным бумагам с нерегулярным первым периодом.

ЦЕНАПОСЛНЕРЕГ Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным последним периодом.

ДОХОДПОСЛНЕРЕГ Возвращает доход по ценным бумагам с нерегулярным последним периодом.

ПЛТ Возвращает величину выплаты за один период годовой ренты.

ОСПЛТ Возвращает величину выплат на основной капитал для вклада в заданный период.

ЦЕНА Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг, по которым производится периодическая выплата процентов.

ЦЕНАСКИДКА Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг, на которые сделана скидка.

ЦЕНАПОГАШ Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг, по которым выплачивается прибыль в момент вступления в силу.

ПС Возвращает приведенную (к настоящему моменту ) стоимость инвестиции.

СТАВКА Возвращает процентную ставку по аннуитету за один период.

ПОЛУЧЕНО Возвращает сумму , полученную в срок вступления в силу полностью обеспеченных ценных бумаг.

АПЛ Возвращает величину непосредственной амортизации имущества за один период.

АСЧ Возвращает величину амортизации актива за данный период, рассчитанную методом "суммы (годовых) чисел".

РАВНОКЧЕК Возвращает эквивалентный облигации доход по казначейскому чеку.

ЦЕНАКЧЕК Возвращает цену за 100 рублей нарицательной стоимости для казначейского чека.

ДОХОДКЧЕК Возвращает доход по казначейскому чеку.

ПУО Возвращает величину амортизации имущества для явно указанного или соответствующего периода при использовании метода разового учета амортизации.

ЧИСТВНДОХ Возвращает внутреннюю ставку доходности запланированных непериодических денежных потоков.

ЧИСТНЗ Возвращает чистую текущую стоимость инвестиции, вычисляемую на основе ряда поступлений наличных, которые не обязательно являются периодическими.

ДОХОД Возвращает доход от ценных бумаг, по которым производятся периодические выплаты процентов.

ДОХОДСКИДКА Возвращает годовой доход по ценным бумагам , на которые сделана скидка. Пример -казначейские чеки.

ДОХОДПОГАШ Возвращает годовой доход от ценных бумаг , процент по которым выплачивается в срок погашения.

Информационные функции

ЯЧЕЙКА Возвращает информацию о формате, местоположении или содержимом ячейки.

СЧИТАТЬПУСТОТЫ Подсчитывает количество пустых ячеек в заданном диапазоне.

ТИП. ОШИБКИ Возвращает номер, соответствующий одному из типов ошибок Microsoft Excel .

ИНФОРМ Возвращает информацию о текущей операционной среде.

ЕПУСТО Возвращает логическое значение ИСТИНА, если аргумент является ссылкой на пустую ячейку.

ЕОШ Возвращает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки, кроме #Н/Д.

ЕОШИБКА Возвращает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки.

ЕЧЁТН Возвращает логическое значение ИСТИНА, если аргумент - четное число.

ЕЛОГИЧ Возвращает логическое значение ИСТИНА, если аргумент ссылается на логическое значение.

ЕНД Возвращает логическое значение ИСТИНА, если аргумент ссылается на значение ошибки #Н/Д (значение недоступно).

ЕНЕТЕКСТ Возвращает логическое значение ИСТИНА, если аргумент ссылается на значение, которое не является текстом.

ЕЧИСЛО Возвращает логическое значение ИСТИНА, если аргумент ссылается на число.

ЕНЕЧЁТ Возвращает логическое значение ИСТИНА, если аргумент - нечетное число.

ЕССЫЛКА Возвращает логическое значение ИСТИНА, если аргумент ссылается на ссылку.

ЕТЕКСТ Возвращает логическое значение ИСТИНА, если аргумент ссылается на текст.

Ч Возвращает значение, преобразованное в число.

НД Возвращает значение ошибки #Н/Д.

ТИП Возвращает тип значения

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

И Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.

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

ЕСЛИ Выполняет проверку условия.

НЕ Меняет на противоположное логическое значение своего аргумента.

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

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

Функции ссылок и подстановок

АДРЕС Возвращает ссылку на отдельную ячейку рабочего листа в виде текста.

ОБЛАСТИ Возвращает количество областей в ссылке.

ВЫБОР Выбирает значение из списка значений по индексу.

СТОЛБЕЦ Возвращает номер столбца, на который указывает ссылка.

ЧИСЛСТОЛБ Возвращает количество столбцов в массиве или ссылке.

ГПР Ищет значение в первой строке массива и возвращает значение из ячейки в найденном столбце и указанной строке.

ГИПЕРССЫЛКА Создает ссылку , открывающую документ, находящийся на жестком диске, сервере сети или в Интернете.

ИНДЕКС Использует индекс для выбора значения из ссылки или массива.

ДВССЫЛ Возвращает ссылку, заданную текстовым значение.

ПРОСМОТР Ищет значения в векторе или массиве.

ПОИСКПОЗ Ищет значения в ссылке или массиве.

СМЕЩ Возвращает смещение ссылки относительно заданной ссылки.

СТРОКА Возвращает номер строки, определяемой ссылкой.

ЧСТРОК Возвращает количество строк в ссылке.

ДРВ Извлекает дату в режиме реального времени для программ, поддерживающих автоматизацию СОМ (Программирование объектов. Стандартное средство для работы с объектами некоторого приложения из другого приложения или средства разработки. Программирование объектов (ранее называемое программированием OLE ) является функцией модели COM ( Component Object Model - модель компонентных объектов). ).

ТРАНСП Возвращает транспонированный массив.

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

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

ABS Возвращает модуль (абсолютную величину) числа. ACOS Возвращает арккосинус числа. ACOSH Возвращает гиперболический арккосинус числа. ASIN Возвращает арксинус числа. ASINH Возвращает гиперболический арксинус числа. ATAN Возвращает арктангенс числа.

ATAN 2 Возвращает арктангенс для заданных координат х и у. ATANH Возвращает гиперболический арктангенс числа.

ОКРВВЕРХ Округляет число до ближайшего целого или до ближайшего кратного указанному значению. ЧИСЛКОМБ Возвращает количество комбинаций для заданного числа объектов. COS Возвращает косинус числа. COSH Возвращает гиперболический косинус числа.

СЧЁТЕСЛИ Подсчитывает количество непустых ячеек, удовлетворяющих заданному условию внутри диапазона.

ГРАДУСЫ Преобразует радианы в градусы.

ЧЁТН Округляет число до ближайшего четного целого.

ЕХР Возвращает число е, возведенное в указанную степень.

ФАКТР Возвращает факториал числа.

ДВФАКТР Возвращает двойной факториал числа.

ОКРВНИЗ Округляет число до ближайшего меньшего по модулю целого.

НОД Возвращает наибольший общий делитель.

ЦЕЛОЕ Округляет число до ближайшего меньшего целого.

НОК Возвращает наименьшее общее кратное.

LN Возвращает натуральный логарифм числа.

LOG Возвращает логарифм числа по заданному основанию.

LOG 10 Возвращает десятичный логарифм числа.

МОПРЕД Возвращает определитель матрицы, хранящейся в массиве.

МОБР Возвращает обратную матрицу (матрица хранится в массиве).

МУМНОЖ Возвращает произведение матриц, хранящихся в массивах.

ОСТАТ Возвращает остаток от деления.

ОКРУГЛТ Возвращает число, округленное с требуемой точностью.

МУЛЬТИНОМ Возвращает мультиномиальный коэффициент множества чисел.

НЕЧЁТ Округляет число до ближайшего нечетного целого.

ПИ Возвращает число "пи".

СТЕПЕНЬ Возвращает результат возведения числа в степень.

ПРОИЗВЕД Возвращает произведение аргументов.

ЧАСТНОЕ Возвращает целую часть частного при делении.

РАДИАНЫ Преобразует градусы в радианы.

СЛЧИС Возвращает случайное число в интервале от 0 до 1.

СЛУЧМЕЖДУ Возвращает случайное число в заданном интервате.

РИМСКОЕ Преобразует число в арабской записи к числу в римской как текст.

ОКРУГЛ Округляет число до указанного количества десятичных разрядов.

ОКРУТЛВНИЗ Округляет число до ближайшего меньшего по модулю целого.

ОКРУГЛВВЕРХ Округляет число до ближайшего по модулю большего целого.

РЯД. СУММ Возвращает сумму степенного ряда, вычисленную по заданной формуле.

ЗНАК Возвращает знак числа.

SIN Возвращает синус заданного угла.

SINH Возвращает гиперболический синус числа.

КОРЕНЬ Возвращает положительное значение квадратного корня.

КОРЕНЬПИ Возвращает значение квадратного корня из (число * "пи").

ПРОМЕЖУТОЧНЫЕ. ИТОГИ Возвращает промежуточный итог в списке или базе данных.

СУММ Суммирует аргументы.

СУММЕСЛИ Суммирует ячейки, удовлетворяющие заданному условию.

СУММПРОИЗВ Возвращает сумму произведений соответствующих элементов массивов.

СУММКВ Возвращает сумму квадратов аргументов ,

СУММРАЗНКВ Возвращает сумму разностей квадратов соответствующих значений в двух массивах.

СУММСУММКВ Возвращает сумму сумм квадратов соответствующих элементов двух массивов.

СУММКВРАЗН Возвращает сумму квадратов разностей соответствующих значений в двух массивах.

TAN Возвращает тангенс числа.

TANH Возвращает гиперболический тангенс числа.

ОТБР Отбрасывает дробную часть числа.

Статистические функции

СРОТКЛ Возвращает среднее абсолютных значений отклонений точек данных от среднего.

СРЗНАЧ Возвращает среднее арифметическое аргументов.

СРЗНАЧА Возвращает среднее арифметическое аргументов , включая числа, текст и логические значения.

БЕТАРАСП Возвращает интегральную функцию плотности бета-вероятности.

БЕТАОБР Возвращает обратную функцию к интегральной функции плотности бета-вероятности.

БИНОМРАСП Возвращает отдельное значение биномиального распределения.

ХИ2РАСП Возвращает одностороннюю вероятность распределения хи-квадрат.

ХИ2ОБР Возвращает обратное значение односторонней вероятности распределения хи-квадрат.

ХИ2ТЕСТ Возвращает тест на независимость.

ДОВЕРИТ Возвращает доверительный интервал для среднего значения по генеральной совокупности.

КОРРЕЛ Возвращает коэффициент корреляции между двумя множествами данных.

СЧЁТ Подсчитывает количество чисел в списке аргументов.

СЧЁТЗ Подсчитывает количество значений в списке аргументов.

КОВАР Возвращает ковариацию. то есть среднее произведений отклонений для каждой пары точек.

КРИТБИНОМ Возвращает наименьшее значение, для которого биномиальная функция распределения меньше или равна заданному значению.

КВАДРОТКЛ Возвращает сумму квадратов отклонений.

ЭКСПРАСП Возвращает экспоненциальное распределение.

FPAC П Возвращает F -распределение вероятности.

ГРАСПОБР Возвращает обратное значение для F -распределения вероятности.

ФИШЕР Возвращает преобразование Фишера.

ФИШЕРОБР Возвращает обратное преобразование Фишера.

ПРЕДСКАЗ Возвращает значение линейного тренда.

ЧАСТОТА Возвращает распределение частот в виде вертикального массива.

ФТЕСТ Возвращает результат F -теста.

ГАММАРАСП Возвращает гамма-распределение.

ГАММАОБР Возвращает обратное гамма-распределение.

ГАММАНЛОГ Возвращает натуральный логарифм гамма функции, ?(х).

СРГЕОМ Возвращает среднее геометрическое.

РОСТ Возвращает значения в соответствии с экспоненциальным трендом.

СРГАРМ Возвращает среднее гармоническое.

ГИПЕРГЕОМЕТ Возвращает гипергеометрическое распределение.

ОТРЕЗОК Возвращает отрезок, отсекаемый на оси линией линейной регрессии.

ЭКСЦЕСС Возвращает эксцесс множества данных.

НАИБОЛЬШИЙ Возвращает k -ое наибольшее значение из множества данных.

ЛИНЕЙН Возвращает параметры линейного тренда.

ЛГРФПРИБЛ Возвращает параметры экспоненциального тренда.

ЛОГНОРМОБР Возвращает обратное логарифмическое нормальное распределение.

ЛОГНОРМРАСП Возвращает интегральное логарифмическое нормальное распределение.

МАКС Возвращает максимальное значение из списка аргументов.

МАКСА Возвращает максимальное значение из списка аргументов, включая числа, текст и логические значения.

МЕДИАНА Возвращает медиану заданных чисел.

МИН Возвращает минимальное значение из списка аргументов.

МИНА Возвращает минимальное значение из списка аргументов, включая числа, текст и логические значения.

МОДА Возвращает значение моды множества данных.

ОТРБИНОМРАСП Возвращает отрицательное биномиальное распределение.

НОРМРАСП Возвращает нормальную функцию распределения.

НОРМОБР Возвращает обратное нормальное распределение.

НОРМСТРАСП Возвращает стандартное нормальное интегральное распределение.

НОРМСТОБР Возвращает обратное значение стандартного нормального распределения.

ПИРСОН Возвращает коэффициент корреляции Пирсона.

ПЕРСЕНТИЛЬ Возвращает k -ую персентиль для значений из интервала.

ПРОЦЕНТРАНГ Возвращает процентную норму значения в множестве данных .

ПЕРЕСТ Возвращает количество перестановок для заданного числа объектов.

ПУАССОН Возвращает распределение Пуассона.

ВЕРОЯТНОСТЬ Возвращает вероятность того, что значение из диапазона находится внутри заданных пределов.

КВАРТИЛЬ Возвращает квартиль множества данных.

РАНГ Возвращает ранг числа в списке чисел.

КВПИРСОН Возвращает квадрат коэффициента корреляции Пирсона.

СКОС Возвращает асимметрию распределения.

НАКЛОН Возвращает наклон линии линейной регрессии.

НАИМЕНЬШИЙ Возвращает k -ое наименьшее значение в множестве данных.

НОРМАЛИЗАЦИЯ Возвращает нормализованное значение.

СТАНДОТКЛОН Оценивает стандартное отклонение по выборке.

СТАНДОТКЛОНА Оценивает стандартное отклонение по выборке, включая числа, текст и логические значения.

СТАНДОТКЛОНП Вычисляет стандартное отклонение по генеральной совокупности.

СТАНДОТКЛОНПА Вычисляет стандартное отклонение по генеральной совокупности, включая числа, текст и логические значения.

CTO Ш YX Возвращает стандартную ошибку предсказанных значений у для каждого значения х в регрессии.

СТЬЮДРАСП Возвращает t -распределение Стьюдента.

СТЬЮДРАСПОБР Возвращает обратное t -распределение Стьюдента.

ТЕНДЕНЦИЯ Возвращает значения в соответствии с линейным трендом.

УРЕЗСРЕДНЕЕ Возвращает среднее внутренности множества данных.

ТТЕСТ Возвращает вероятность, соответствующую критерию Стьюдента.

ДИСП Оценивает дисперсию по выборке.

ДИСПА Оценивает дисперсию по выборке, включая числа, текст и логические значения.

ДИСПР Вычисляет дисперсию для генеральной совокупности.

ДИСПРА Вычисляет дисперсию для генеральной совокупности, включая числа, текст и логические значения.

ВЕЙБУЛЛ Возвращает распределение Вейбулла.

ZTECT Возвращает двустороннее Р-значение z -теста.

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

ASC Преобразует полноширинные (двухбайтные) английские буквы или знаки катакана в текстовой строке в полуширинные (один байт).

БАТТЕКСТ Преобразует число в текст, используя денежный формат. (БАТ).

СИМВОЛ Возвращает знак с заданным кодом.

ПЕЧСИМВ Удаляет все непечатаемые знаки из текста. КОДСИМВ Возвращает числовой код первого знака в текстовой строке.

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

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

СОВПАД Проверяет идентичность двух текстов. НАЙТИ Ищет вхождение одного текста в другой (с учетом регистра).

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

JIS Преобразует полуширинные (однобайтные) английские буквы или знаки катакана в текстовой строке в полноширинные (два байта).

ЛЕВСИМВ Возвращает самые левые знаки текстового значения.

ДЛСТР Возвращает количество знаков в текстовой строке. СТРОЧН Делает все буквы в тексте строчными.

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

PHONETIC Извлекает фонетические (фуригана) знаки из текстовой строки.

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

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

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

ПРАВСИМВ Возвращает самые правые знаки текстовой строки. ПОИСК Ищет вхождение одного текста в другой (без учета регистра).

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

Т Преобразует аргумент в текст.

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

СЖПРОБЕЛЫ Удаляет из текста пробелы. ПРОПИСН Делает все буквы в тексте прописными.

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

Отображение и скрытие нулевых значений

Отображение или скрытие всех нулевых значений на рабочем листе

В меню Сервис выберите пункт Параметры и откройте вкладку Вид. Выполните одно из следующих действий.

о Для отображения в ячейках нулевых (0) значений установите флажок Нулевые значения.

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

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

Выделите ячейки, содержащие нули (0), которые требуется скрыть. В меню Формат выберите команду Ячейки , а затем откройте вкладку Число . В списке Числовые форматы выберите пункт (все форматы). В поле Тип введите 0; -0;; @..

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

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

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

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

2. В меню Формат выберите команду Условное форматирование

3. В поле слева выберите вариант значение.

4. Во втором поле слева выберите вариант равно.

5. В поле справа введите «0».

6. Нажмите кнопку Формат, а затем откройте вкладку Шрифт .

7. В списке Цвет выберите белый цвет.

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

Для выполнения этой задачи используется функция ЕСЛИ.

Создайте пустую книгу или лист. Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите сочетание клавиш CTRL - C На листе выделите ячейку А1 и нажмите сочетание клавиш CTRL + V .

Чтобы переключиться между просмотром результатов и просмотром формул , возвращающих эти результаты, нажмите сочетание клавиш CTRL +' (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

| D |
1 | Данные |
2 | 10 |
3 | 10 |
| Формула | Описание (результат)
4 | =А2-А3 | Из первого (0) номера вычтен второй
5 | =ЕСЛИ(А2-АЗ=0; "": А2-АЗ) | Возвращает пустую ячейку, если ячейка содержит нулевые значения (пустая ячейка)
6 | =ЕСЛИ(А2-АЗ=0; "-"; А2-АЗ) | Возвращает «-», если ячейка содержит нулевые значения.

Поиск значений в списке

Создание формулы подстановки с помощью мастера подстановок

Мастер подстановок создает формулу подстановки, основанную на списке (Список. Набор строк листа, содержащий однородные данные, например база данных счетов или набор адресов и телефонов клиентов. Первая строка в этом списке содержит заголовки столбцов. ) листа, содержащем заголовки строк и столбцов. Мастер подстановок помогает найти другие значения в строке, если известно значение в одном столбце, и наоборот. В формулах, создаваемых мастером подстановок, используются функции ИНДЕКС и ПОИСКПОЗ.

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

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

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

Поиск значений путем их сравнения со значениями из первого столбца

Для выполнения этой задачи используется функция ВПР.

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

Пример: Создайте пустую книгу или лист. Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите сочетание клавиш CTRL - C На листе выделите ячейку А1 и нажмите сочетание клавиш CTRL + V . Чтобы переключиться между просмотром результатов и просмотром формул , возвращающих эти результаты, нажмите сочетание клавиш CTRL -' (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

В данном примере известна периодичность и требуется найти связанный цвет.

|  | д |  | R
1 | Л 1 Л |  |  |

1 | Л 1 0 |
1 | < 1 7 |
Л | Я 11 |
| А 10 |
| Формула | Описание (результат)
6 | =ВПР(5. 77; А1: В6; 2) | Поиск 5. 77 в столбце А и возвращение значения из столбца В. находящегося в той же строке (зеленый)

Описание функций

ВПР Поиск значений путем их сравнения со значениями из первой строки

Для выполнения этой задачи используется функция ВПР.

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

Пример: Чтобы этот пример п po ще было понять, скопируйте его на пустой лист. Создайте пустую книгу или лист. Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите сочетание клавиш CTRL + C . На листе выделите ячейку А1 и нажмите сочетание клавиш CTRL + V . Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL - ` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

| А | В | С
| Состояние | Оси | Болты
1 | В биржевых | 4 | 9
2 | В порядке | 5 | 10
3 | Обратный порядок | 6 | 11
4 | Формула | Описание (результат) |
| =ВПР("Болты"; A 1: C 4; 3) | Поиск "Болты" в строке 1. и возвращение значения из строки 3. находящейся в том же столбце (10) |

ГПР Поиск значений в несортированном диапазоне

Для выполнения этой задачи используются функции ИНДЕКС и ПОИСКПОЗ.

Пример: Создайте пустую книгу или лист. Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите c очетание клавиш CTRL + C . На листе выделите ячейку A 1 и нажмите сочетание клавиш CTRL + V . Чтобы переключиться между просмотром результатов и просмотром формул , возвращающих эти результаты, нажмите сочетание клавиш CTRL +` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

| А | В
1 | Произведение | Кол-во значений
2 | Бананы | 38
3 | Апельсины | 25
4 | Яблоки | 41
5 | Груши | 40
| Формула | Описание (результат)
6 | =ИНДЕКС(А2: В5: ПОИСКПОЗ("Груши"; А2: А5; 0); 2) | Поиск «Груши» в столбце А и возвращение значения для «Груши» в столбец В (40).

Формула поиска значений в несортированном списке

А2: В5: Весь список, в котором осуществляется поиск значений

ПОИСКПОЗ("Груши"; А2: А5; 0): Функция ПОИСКПОЗ определяет число строк

"Груши": Значение для поиска в столбце подстановки.

А2: А5: Столбец для поиска для функции ПОИСКПОЗ.

2: Столбец, из которого возвращается значение. 1 — это крайний левый столбец.

Описание функций

ИНДЕКС ПОИСКПОЗ Поиск значений в несортированном диапазоне неопределенного размера Для выполнения этой задачи используются функции СМЕЩ и ПОИСКПОЗ.

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

Пример: Создайте пустую книгу или лист. Выделите пример в разделе справки Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите сочетание клавиш CTRL + C . На листе выделите ячейку A 1 и нажмите сочетание клавиш CTRL + V . Чтобы пёреключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL +` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

| e | n
1 | n—» |
2 | Nee. |
3 | »-"¦«-- | •><
4 |  | /( 1
5 | a- - | /10
| m —« |
| =СМЕЩ(А1; ПОИСКПОЗ("Груши"; А2: А5; 0); 1) | Поиск «Груши» в столбце А и возвращение значения для «Груши» в столбец В (40).

Приведенная формула использует следующие аргументы.

А1: Левая верхняя ячейка списка, называемая начальной ячейкой.

ПОИСКПОЗ("Груши"; А2: А5; 0): Функция ПОИСКПОЗ определяет число строк под начальной ячейкой для поиска искомого значения.

"Груши": Значение для поиска в столбце подстановки.

А2А5: Столбец для поиска для функции ПОИСКПОЗ. Не включайте начальную ячейку в этот диапазон. Убедитесь, что диапазон позволяет расширять список.

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

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

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

Нажмите стрелку рядом с кнопкой Автосумма, выберите команду Минимум (поиск наименьшего) или Максимум (поиск наибольшего), а затем нажмите клавишу ENTER .

Для ячеек, расположенных вразброс

Для выполнения этой задачи используется функция МИН. МАКС. НАИМЕНЬШИЙ или НАИБОЛЬШИЙ.

Пример: Создайте пустую книгу или лист. Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите сочетание клавиш CTRL +С. На листе выделите ячейку А1 и нажмите сочетание клавиш CTRL + V . Чтобы переключиться между просмотром результатов и просмотром формул , возвращающих эти результаты, нажмите сочетание клавиш CTRL +` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

| А |
1 | Данные |
2 | 10 |
3 | 7 |
4 | 9 |
5 | 27 |
6 | 0 |
7 | 4 |
| Формула | Описание (результат)
| =МИН(А2: А7) | Поиск наименьшего числа в диапазоне (0)
| =МАКС(А2: А7) | Поиск наибольшего числа в диапазоне (27)
| =НАИМЕНЬШИЙ(А2: А7; 2) | Поиск второго наименьшего числа в диапазоне (4)
| =НАИБОЛЬШИЙ(А2: А7; 3) | Поиск третьего наибольшего числа в диапазоне (9)

Аппроксимация значений

ПРЕДСКАЗ

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

Синтаксис

ПРЕДСКАЗ(х; известные_значения_ y ; известные_значення_х)

х — это точка данных, для которой предсказывается значение. Известные_ значения_у — это зависимый массив или интервал данных. Известные_значениях — это независимый массив или интервал данных. Заметки

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

Если известные_значения_у и известные_значения_х пусты или содержат различное количество точек данных. то функция ПРЕДСКАЗ возвращает значение ошибки #Н/Д.

Если дисперсия аргумента известные значениях равна нулю, то функция ПРЕДСКАЗ возвращает значение ошибки #ДЕЛ/0!. Пример

Создайте пустую книгу или лист. Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите сочетание клавиш CTRL + C . На листе выделите ячейку А1 и нажмите сочетание клавиш CTRL + V . Чтобы переключиться между просмотром результатов и просмотром формул. возвращающих эти результаты, нажмите сочетание клавиш CTRL +' (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

| А | В
1 | Известные значения у | Известные значения х
2 | 6 | 20
3 | 7 | 28
4 | 9 | 31
5 | 15 | 38
6 | 21 | 40
| Формула | Описание (результат)
| =ПРЕДСКАЗ(30; А2: А6: В2: В6) | Предсказывает значение — это у — значение. соответствующее заданному х-значению 30 (10, 60725)

ТЕНДЕНЦИЯ

Возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные_значения_у и известные_значения_х. Возвращает значения у. в соответствии с этой прямой для заданного массива новые_значения_х. Синтаксис

ТЕНДЕНЦИЯ(нзвестные_значения_у; известные_значения_х; новые_значения_х; конст) Известные_значения_у — множество значений у. которые уже известны для соотношения у = m х + b .

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

Если массив известные_значения_у имеет одну строку, то каждая строка массива известные_значения_х интерпретируется как отдельная переменная. Известные_значения_х — необязательное множество значений х, которые уже известны для соотношения у = m х + b .

Массив известные_значения_х может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_у и известные_значения_х могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_ y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

Если известные_значения_х опущены, то предполагается, что это массив { l ; 2; 3;... } такого же размера, как и известные_значения_у. Новые_значения_х — новые значения х, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения у.

Новые_значения_х должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_х. Таким образом, если известные_значения_у — это один столбец, то известные_значения_х и новые_значения_х должны иметь такое же количество столбцов. Если известные_значения_у — это одна строка, то известные_значения_х и новые_значения_х должны иметь такое же количество строк.

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

Если опущены оба массива известные_значения_х и новые значениях, то предполагается, что это массив {1; 2; 3;... } такого же размера, что и известныс_значения_у. Конст— логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.

Если конст имеет значение ЛОЖЬ, то b полагается равным 0. и значения m подбираются таким образом, чтобы выполнялось соотношение у = m х.

Заметки

Для получения информации о том. как Microsoft Excel аппроксимирует данные прямой, см. ЛИНЕИН.

Можно использовать функцию ТЕНДЕНЦИЯ для аппроксимации полиномиальной кривой, проводя регрессионный анализ для той же переменной, возведенной в различные степени. Например, пусть столбец А содержит значения у. а столбец В содержит значения х. Можно ввести х^2 в столбец С, х^3 в столбец D , и так далее, а затем провести регрессионный анализ столбцов от В до D со столбцом А.

Формулы, которые возвращают массивы, должны быть введены как формулы массива.

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

Создайте пустую книгу или лист. Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите сочетание клавиш CTRL + C . На листе выделите ячейку А1 и нажмите сочетание клавиш CTRL + V . Чтобы переключиться между просмотром результатов и просмотром формул. возвращающих эти результаты, нажмите сочетание клавиш CTRL +' (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

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

| А | В | С
1 | Месяц | Начальная стоимость | Формула (соответствующая цена)
| 1 | 133, 890 руб. | =ТЕНДЕНЦИЯ(В2: В13; А2: А13)
3 | 2 | 135. 000 руб. |
4 | з | 135, 790 руб. |
5 | 4 | 137, 300 руб. |
6 | 5 | 138, 130 руб. |
7 | 6 | 139, 100 руб. |
8 | 7 | 139. 900 руб. |
9 | 8 | 141. 120 руб. |
10 | 9 | 141, 890 руб. |
11 | 10 | 143. 230 руб. |
12 | 11 | 144. 000 руб. |
13 | 12 | 145. 290 руо. |
| Месяц | Формула (предполагаемая цена) |
| 13 | =ТЕНДЕНЦИЯ(В2: В 13; А2: А 13; А 1 5: А19) |
| 14 |  |
| 15 |  |
| 16 |  |
| 17 |  |

Примечание. Формулу в этом примере необходимо ввести как формулу массива. После копирования этого примера на пустой лист выделите диапазон С2: С13 или В 15: В19, начиная с ячейки, содержащей формулу . Нажмите клавишу F 2, а затем нажмите клавиши CTRL + SHIFT + ENTER . Если формула не будет введена как формула массива. единственные значения будут равны 133953. 3333 и 146171, 5152.

РОСТ

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

РОСТ (известные_значения_у; известные_значения_х; новые_значения_х; конст)

Известные_значения_у — это множество значений у. которые уже известны в соотношении у = b * m ^х.

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

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

Если какие-либо числа в массиве известные_значения_у равны 0 или отрицательны, то функция РОСТ возвращает значение ошибки #ЧИСЛО!.

Известны_езначения_х — это необязательное множество значений х, которые уже известны для соотношения у = b * m ^ x .

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

Если известные_значения_х опущены, то предполагается, что это массив {1; 2; 3;... } такого же размера, как и известные_значения _у. Новые_ значения_х — это новые значения х, для которых РОСТ возвращает соответствующие значения у.

Новые_значения_х должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_х. Таким образом, если известные_значения_у — это один столбец, то известные_значения_х и новые значениях должны иметь такое же количество столбцов. Если известные_значения_у — это одна строка, то известные_значения_х и новые_значения_х должны иметь такое же количество строк.

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

Если оба аргумента известные_значения_х и новые_значения_х опущены, то предполагается, что это массив { l ; 2; 3;... } такого же размера, как и известные_значения_у. Конст — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1.

Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.

Если конст имеет значение ЛОЖЬ, то b полагается равным 1, а значения m подбираются так. чтобы у = m ^ x . Заметки

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

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

Чтобы этот пример проще было понять, скопируйте e г o на пустой лист. Создайте пустую книгу или лист. Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца. Выделение примера в справке. Нажмите сочетание клавиш CTRL + C На листе выделите ячейку A 1 и нажмите сочетание клавиш CTRL + V .

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты ,
нажмите сочетание клавиш CTRL +' (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите
режим Режим проверки формул .

В этом примере используются те же данные, что и в примере для функции ЛГРФПРИБЛ. Первая формула сопоставляет соответствующие значения известным значениям. Вторая формула предсказывает значения следующих месяцев, если экспоненциальный тренд сохраняется.

А | В | С
Месяц | Единицы | Формула (Соответственные единицы)
1 1 | 33 100 | =РОСТ(В2: В7; А2: А7)
12 | 47 300 |
13 | 69 000 |
14 | 102 000 |
15 | 150 000 |
16 | 220 000 |
Месяц | Формула (Предполагаемые единицы) |
17 | =РОСТ(В2: В7; А2: А7; А9: А10) |
18 |  |

Примечание. Формулу в этом примере необходимо ввести как формулу массива. После копирования этого примера на пустой лист выделите диапазон С2: С7 или В9: В10, начиная с ячейки, содержащей формулу . Нажмите клавишу F 2, а затем нажмите клавиши CTRL + SHIFT + ENTER . Если формула не будет введена как формула массива, единственные значения будут равны 32618, 20377 и 320196, 7184.

Заполнение серии ячеек числами, датами либо другими элементами

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

о Если требуется получить ряд 2, 3, 4, 5..., введите 2 и 3 в первые две ячейки. Если требуется получить ряд 2, 4, 6. 8..., введите 2 и 4. Если требуется получить ряд 2, 2, 2, 2,..., вторую ячейку можно оставить пустой.

Чтобы задать тип ряда значений, перетащите маркер заполнения правой кнопкой мыши, а затем выберите соответствующую команду в контекстном меню. Например, если начальное значение — дата «янв-2002». то для получения ряда «фев-2002», «мар-2002» и т. д. выберите команду Заполнить по месяцам, а для получения ряда «янв-2003», «янв-2004» и т. д. выберите команду Заполнить по годам. Для того чтобы управлять созданием ряда вручную или использовать для заполнения ряда значений клавиатуру, используйте команду контекстного меню — Прогрессия. Выделите ячейку или ячейки, содержащие начальные значения. Перетащите маркер заполнения через заполняемые ячейки. Для заполнения в возрастающем порядке перетащите маркер вниз или вправо. Для заполнения в убывающем порядке перетащите маркер вверх или влево.

Разрешение вопросов в диаграммах

Данные и текст в диаграммах

Точечная диаграмма не использует заданные значения вдоль горизонтальной (х) оси.

Убедитесь, что выбран не график. Если вместо нужных значений на оси категорий выводятся числа 1, 2, 3, 4 и т. д., то возможно, при создании диаграммы в качестве типа был выбран график, а не точечная диаграмма. Удалите диаграмму, выделите на листе данные, на которых будет основана диаграмма, а затем нажмите кнопку Мастер диаграмм. Выберите вариант Точечная в поле Тип диалогового окна Мастер диаграмм (шаг 1 из 4): тип диаграммы. Если требуется, чтобы маркеры данных (Маркер данных. Полоса, область, точка, сегмент или другой объект на диаграмме, соответствующий одному значению одной ячейки листа. Маркеры данных одного цвета на диаграмме образуют ряд данных) были соединены линиями, выберите один из видов точечной диаграммы с линиями.

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

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

Примечание. Можно также создать точечную диаграмму с разными значениями по оси х для каждого ряда значений по оси у. Дня этого на вкладке Ряд диалогового окна Исходные данные (меню Диаграмма) выделите ряды, для которых необходимо изменить значения х. Затем в поле Значения х введите ссылки на ячейки либо нужные значения х, разделяя их точкой с запятой, например: 0, 02; 0, 03; 0, 04.

В диаграмме с датами отсутствует ось времени

Параметр «ось времени». Проверьте установку флажка ось времени на вкладке Оси в диалоговом окне Параметры диаграммы (меню Диаграмма). Недоступность этого параметра может свидетельствовать о неправильном выборе типа диаграммы или отсутствии значений дат на оси категорий диаграммы.

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

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

Значения дат на оси категорий. Удостоверьтесь в наличии дат вдоль оси категорий диаграммы.

Многоуровневые подписи категорий. Ось времени не может быть включена в диаграмму с многоуровневыми подписями категорий. (Многоуровневые подписи категорий. Подписи категорий на диаграмме, отображаемые в виде структуры в зависимости от данных ячейках на листе. Например, заголовок "Фрукты" может отображаться над строкой с заголовками "Сливы", Яблоки" и "Груши"). Чтобы подписи категорий в диаграмме. основанной на данных листа, были одноуровневыми, подписи в исходных данных не следует располагать в несколько строк.

Вдоль горизонтальной оси отсутствует текст.

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

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

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

Дважды щелкните кнопку мыши на названии оси. а затем на вкладке Выравнивание измените угол поворота. Если угол составляет 0 о , 90° или -90°, то текст будет повернут.

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

Если вводится новый текст или значения ряда данных (Ряд данных. Набор связанных между собой элементов данных , отображаемых на диаграмме. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных. ) и категорий на вкладке Ряд диалогового окна Исходные данные (меню Диаграмма, команда Исходные данные), Microsoft Excel разрывает связь между диаграммой и данными листа, по которому построена диаграмма. Microsoft Excel также разрывает связи между подписями данных (Подпись данных. Подпись с дополнительными сведениями о маркере данных, представляющем одну точку данных или значение ячейки листа. ) и соответствующими данными на листе. Можно переустановить все эти связи, используя один из перечисленных ниже способов.

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

Пример: Выберите изменяемую диаграмму. Выберите в меню Диаграмма команду Исходные данные, а затем
перейдите на вкладку Диапазон данных. Убедитесь, что выделена полностью вся ссылка в поле Диапазон данных. На
рабочем листе выберите ячейки, содержащие данные, которые должны появиться в диаграмме. Чтобы заголовок
столбца или строки для новых данных появился в диаграмме, в выбираемые ячейки нужно включить те. которые
Содержат этот заголовок. Внедренные диаграммы. Для переустановки связей между листом и внедренной диаграммой
(Внедренная диаграмма. Диаграмма, помещенная на обычный лист, а не на отдельный лист диаграммы. Внедренные
диаграммы удобны для просмотра или печати отчета сводной диаграммы вместе с исходными данными и другими
сведениями, содержащимися на листе), необходимо заново указать диапазон ячеек или с помощью мыши изменить
размер выделенного цветом диапазона, чтобы включить требуемые данные.

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

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

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

Чтобы добавить данные на внедренную диаграмму (Внедренная диаграмма. Диаграмма, помещенная на

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

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

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

Нажмите кнопку Копировать. Щелкните диаграмму. Выполните одно из следующих действий:

чтобы автоматически вставить данные в диаграмму, выберите команду Вставить:

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

вставка, а затем установите необходимые параметры.

Добавление данных на диаграмму с использованием выделенного цветом диапазона

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

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

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

Выполните одно из следующих действий:

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

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

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

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

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

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

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

Примечание. Если отчет создан на основе файла автономного куба (Файл автономного куба. Файл, созданный на локальном жестком диске или сетевом ресурсе, в котором хранятся исходные данные OLAP для отчета сводной таблицы или отчета сводной диаграммы. Файлы автономного куба позволяют продолжать работу при отсутствии подключения к серверу OLAP . ), при его обновлении файл куба будет полностью собран заново из данных с OLAP ( OLAP . Технология работы с базами данных, ориентированная на запросы и отчеты, а не на обработку транзакций. Данные OLAP упорядочены иерархически и хранятся не в таблицах, а в кубах. )-сервера, что может занять продолжительное время (необходимое для создания файла куба ).

Щелкните отчет.

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

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

Пример: На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма и выберите команду Параметры таблицы или Параметры.

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

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

Совет:

Если при каждом обновлении необходимо указывать пароль на доступ к базе данных, можно сохранить этот пароль в Microsoft Excel для автоматического ввода. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма, выберите команду Параметры таблицы или Параметры, а затем установите флажок сохранить пароль. В этом случае пароль сохраняется в виде открытого текста, поэтому если безопасность важнее удобства, не следует использовать этот режим.

Опубликованную диаграмму не удается просмотреть в обозревателе.

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

Форматирование

Размер шрифта на диаграмме постоянно изменяется.

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

Меню Формат выберите команду Выделенная область диаграммы, перейдите к вкладке Шрифты, а затем снимите флажок Автомасштабирование.

Промежуток между датами, представленными на диаграмме

Если данные листа для оси категорий содержат формат дат, то Microsoft Excel автоматически использует в диаграмме особый тип оси, называемый осью времени. Ось времени выводит пустые категории для дат, по которым нет никаких данных . Если эти пустые категории выводить не нужно, например, если имеются данные на 1 января. 15 января. 3 февраля, 12 февраля и 2 марта и необходимо, чтобы даты располагались рядом друг с другом, то можно заменить ось времени на стандартную ось категорий. Для этого выделите диаграмму, а затем в меню Диаграмма выберите команду Параметры диаграммы. Потом на вкладке Оси выберите команду Категории под Осью категорий ( X ).

Смещение многоуровневых подписей категорий на диаграмме.

Диаграмма связана с закрытой книгой. Если в диаграмме используются данные из закрытой книги, многоуровневые подписи категорий (Многоуровневые подписи категорий. Подписи категорий на диаграмме, отображаемые в виде структуры в зависимости от данных в ячейках на листе. Например, заголовок "Фрукты" может отображаться над строкой с заголовками "Сливы". "Яблоки" и "Груши". ) будут отображаться не так, как при создании диаграммы. Например, нижние подписи могут быть смещены влево или расположены посередине. Чтобы изменить расположение подписей, откройте соответствующую книгу. Воспользуйтесь командой Связи (меню Правка) в книге с диаграммой. Выберите в списке исходный файл с данными диаграммы, а затем выполните команду Открыть В результате этих действий расположение подписей будет восстановлено.

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

Невозможно найти созданную или перемененную диаграмму.

Выполните одно из следующих действий.

Если ярлычок листа внизу окна листа был перемещен или создан.

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

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

Разрешение вопросов при работе с отчетами сводных диаграмм

Создание отчета

Появляется сообщение "недостаточно памяти".

Проверьте параметры использования памяти

Нажмите кнопку ОК в диалоговом окне сообщения.

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

Установите флажок оптимизировать память.

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

На шаге 3 мастера нажмите кнопку Макет. Перетащите одно или несколько полей в область СТРАНИЦА. Дважды щелкните кнопкой мыши каждое поле страницы. Нажмите кнопку Подробнее. Установите флажок обновлять внешние данные при выборе каждого элемента.

Упростите отчет. В зависимости от объема памяти компьютера и количества исходных данных (Исходные данные. Список или таблица, используемые для создания отчета сводной таблицы или отчета сводной диаграммы. Исходные данные могут быть взяты из списка или диапазона Microsoft Excel , внешней базы данных или куба либо другого отчета сводной таблицы. ) возможное число полей может изменяться. Если требуется использование большого числа полей, добавьте их как поля рядов (Поле ряда. Поле, отображаемое в области рядов отчета сводной диаграммы. Элементы в поле рядов указаны в легенде и дают имена отдельным рядам данных. ) или поля страницы. Поля страниц требуют наименьшего объема памяти. Поля рядов требуют меньше памяти, чем поля категорий (Поле категории. Поле, выводящееся в области категорий отчета сводной диаграммы. Элементы поля категории отображаются как подписи по оси категорий. ).

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

Упростите файл. Уменьшите количество отчетов в данной книге.

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

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

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

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

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

Трудности в использовании источника данных OLAP .

Создание кубов OLAP

Поля даты и времени имеют неправильные уровни

Проверьте поле со значением даты или времени в исходной базе данных. Если ожидаемые уровни (Уровень. Часть размерности OLAP . В пределах размерности данные делятся по уровням детализации. Например: "Год", "Квартал". "Месяц" и "День" в размерности "Время") для измерений (Размерность. Структура OLAP . упорядочивающая данные по уровням (например, Страна/Область/Город для размерности "География"). В сводной таблице или сводном отчете каждая размерность становится набором полей, сведения которого можно развертывать и свертывать. ) даты или времени в исходных данных (созданных в приложении Microsoft Query с помощью мастера создания куба) не отображаются, проверьте поле даты или времени в текущей реляционной базе данных, предоставляющей данные для куба База данных может хранить даты и время в текстовом формате, который мастер не распознает. Если вы полагаете, что именно это является причиной ошибки, обратитесь к администратору реляционной базы данных, чтобы проверить и исправить формат поля, который должен соответствовать формату времени и даты.

Проверьте верхний уровень измерения. При добавлении полей даты и времени в качестве нижнего уровня размерности мастер создания куба OLAP не производит автоматического разбиения данных на уровни год/квартал/месяц/неделя и час/минута/секунда. Мастер выполняет эту операцию восстановления, только когда поле, содержащее значение даты или времени, добавляется на верхний уровень нового измерения. Если поле, содержащее значение даты или времени, находится не на самом верхнем уровне, преобразуйте куб данных, открыв файл с расширением. oqy в Microsoft Query , либо обратитесь к лицу, создавшему данный куб. для выполнения соответствующих изменений.

Недоступна нужная итоговая функция

В мастере куба OLAP для полей данных доступны только следующие итоговые функции: СУММ, СЧЕТ. МИН и МАКС.

Создайте отчет непосредственно из записей базы данных. Если можно упростить и уменьшить запрос (Запрос. В Microsoft Query или Microsoft Access средство поиска данных, отвечающих на определенный вопрос о данных, хранящихся в источнике данных), чтобы система могла обработать объем возвращаемых данных, попробуйте отправлять данные из Microsoft Query непосредственно в Microsoft Excel без создания куба. При создании сводной таблицы или отчета сводной диаграммы непосредственно из записей базы данных будет доступно большее число итоговых функций: СУММ, СЧЁТ, СРЗНАЧ, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНПА, ДИСП иДИСПР.

Попробуйте настроить сервер OLAP для базы данных. Серверный программный продукт, выполняющий операции OLAP . Microsoft SQL Server OLAP Services , позволяет задать более широкий диапазон полей, для которых будет вычисляться итоговая функция, чем клиентское программное обеспечение OLAP , поставляемое вместе с Microsoft Office .

Не удается изменить куб О LAP

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

Проверьте изменения в исходной базе данных. Если при создании куба из реляционной базы данных использовался мастер куба OLAP , таблицы в базе данных могли быть переименованы или удалены, вследствие чего исходные данные для куба стали недоступны. Подключитесь к базе данных в Microsoft Query и проверьте наличие изменений в структуре или схеме базы данных. Если база данных была изменена, возможно, потребуется создать новый куб.

Отображается сообщение о потере данных

Включенное в куб поле больше недоступно в исходной базе данных.

Проверьте изменения в исходной базе данных. При работе с файлом автономного куба. (Файл автономного куба. Файл, созданный на локальном жестком диске или сетевом ресурсе, в котором хранятся исходные данные OLAP для отчета сводной таблицы или отчета сводной диаграммы. Файлы автономного куба позволяют продолжать работу при отсутствии подключения к серверу OLAP . ) созданным на основе базы данных OLAP на сервере, выполните повторное подключение отчета к серверу базы данных, после чего проверьте доступность полей в окне Список полей сводной таблицы. Если поле более недоступно на сервере, можно использовать команду Автономный режим OLAP в меню Сводная таблица, чтобы создать файл автономного куба.

Если куб был создан в Microsoft Query , откройте файл запроса (Запрос. В Microsoft Query или Microsoft Access средство поиска данных, отвечающих на определенный вопрос о данных, хранящихся в источнике данных. ) (. dqy ), использовавшийся для создания куба, или, если такой файл недоступен, создайте новое соединение с исходной реляционной базой данных. Проверьте доступные поля. Подробные инструкции см. в справке Microsoft Query . Если поля были удалены из базы данных, то можно создать новый куб.

Сохранение файла куба занимает много времени

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

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

Чтобы уменьшить размер файла, исключите данные, отображение которых в отчете не требуется. На шаге 2 мастера автономного куба выберите только нужные измерения и уровни. На шаге 3 выберите только те измерения, которые используются в отчете как поля данных (Поле данных. Поле исходного списка, таблицы или базы данных, которое является источником данных для вычислений в отчете сводной таблицы или в отчете сводной диаграммы. Поле данных обычно содержит числа, такие как статистические значения или объемы продаж. ) и в каждом измерении исключите ненужные элементы (Элемент. Подкатегория поля в отчетах сводной таблицы и сводной диаграммы. Например, поле "Месяц" будет включать такие элементы как "Январь". "Февраль" и т. п. ).

Не хватает пространства на диске для сохранения куба

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

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

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

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

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

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

Исключите некоторые элементы данных. На шаге 3 работы с мастером щелкните знак рядом с каждым из выбранных измерений и снимите флажки для элементов, не отображаемых в отчете.

Не отключайтесь от сервера. Если были исключены все ненужные данные, однако сохранить файл автономного куба по-прежнему не удается, не разрывайте соединение с сервером OLAP , чтобы отчет был непосредственно связан с базой данных.

Использование кубов OLAP

После создания файла куба данные из отчета пропадают

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

Используйте команду Автономный режим OLAP в меню Сводная таблица, чтобы выполнить повторное подключение отчета к серверу базы данных и убедиться в том. что в отчете отображаются необходимые данные. Затем внесите изменения в файл автономного куба; убедившись в том. что в автономный куб включены все измерения, поля данных и уровни детализации, которые используются в отчете.

Отчет использует поля из запроса, а не из куба

Если в окне Список полей сводной таблицы отображаются поля из реляционной базы данных, использовавшейся для создания куба, то возвращаются данные запроса в Microsoft Excel вместо файла, созданного с помощью мастера создания куба OLAP (. oqy ). В этом файле хранится определение куба и доступ к файлу автономного куба ( cub ), если такой существует. Если не был определен другой путь для файла. oqy , он был сохранен в папке «мои документы\ my data sources » или в папке « winnt \ profiles \ имя пальзователя \ ту data sources », в зависимости от версии операционной системы Microsoft Windows .

Чтобы данные для отчета брались из нового куба , выполните команду Открыть (меню Файл Microsoft Excel ), в списке Тип файлов выберите пункт Файлы запросов (*. dqy ). после чего найдите и дважды щелкните мышью нужный файл (. oqy ). Если требуется отчет сводной диаграммы, щелкните отчет сводной таблицы, созданный при открытии файла. oqy , а затем нажмите кнопку Мастер диаграмм на панели инструментов Сводные таблицы.

Данные, имеющиеся в базе данных, отсутствуют в кубе

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

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

Обратитесь к создателю куба. Если куб был получен откуда-либо еще, и в окне Список полей сводной таблицы отсутствуют нужные поля или уровни детализации, обратитесь к создателю куба для включения дополнительных данных.

Измените содержимое файла автономного куба. Если файл автономного куба был создан в Microsoft Excel из базы данных OLAP -сервера, используйте команду Автономный режим OLAP в меню Сводная таблица для изменения файла. Убедитесь в том. что включены все измерения, поля данных и уровни детализации, используемые в отчете.

Проверьте содержимое куба, созданного в Microsoft Query . Невозможно добавить данные в куб, созданный с помощью мастера создания куба OLAP в Microsoft Query , но можно изменять способ организации куба и удалять данные из куба. Если в кубе отсутствуют необходимые данные, находящиеся в исходной реляционной базе данных, при помощи Microsoft Query можно создать новый куб. который будет содержать нужные поля. В Microsoft Query откройте файл dqy , использовавшийся для запроса данных для куба OLAP , или создайте новый запрос если такой файл не был сохранен. Добавьте в запрос любые дополнительные поля, а затем используйте команду Создать куб OLAP в меню Файл. Подробные инструкции см. в справке Microsoft Query .

Не обновляются данные отчета

Файл автономного куба (Файл автономного куба. Файл, созданный на локальном жестком диске или сетевом ресурсе, в котором хранятся исходные данные OLAP для отчета сводной таблицы или отчета сводной диаграммы. Файлы автономного куба позволяют продолжать работу при отсутствии подключения к серверу OLAP . ) или куба , созданного в Microsoft Query , может не подключаться к исходной базе данных для получения новых данных.

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

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

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

Microsoft Excel не может найти автономный файл куба

Файл. cub мог быть переименован или перемещен.

Убедитесь в наличии файла. Если открываемый файл . oqy был получен откуда-либо еще, убедитесь в наличии доступа к файлу. cub .

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

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

Примечание. По дальнейшим вопросам, связанным с кубами и мастером создания куба OLAP , обращайтесь к справочной системе Microsoft Query .

Работа с макетом отчета

Кнопки полей исчезли, и невозможно изменить макет

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

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

При добавлении поля данные появляются с задержкой.

Если внешний источник или источник, созданный с помощью мастера куба OLAP , содержит слишком большой объем данных, перетаскивание полей из списка полей на рабочий лист происходит с заметной задержкой.

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

Отключите отображение элементов рядов и категорий. Убедитесь, что на панели инструментов Сводная таблица кнопка Всегда отображать элементы не нажата. Элементы (Элемент. Подкатегория поля в отчетах сводной таблицы и сводной диаграммы. Например, поле "Месяц" будет включать такие элементы как "Январь". "Февраль" и т. п. ) не будут отображаться при перетаскивании в область рядов и категорий, но будут отображаться только при добавлении поля данных (Поле данных. Поле исходного списка, таблицы или базы данных, которое является источником данных для вычислений в отчете сводной таблицы или в отчете сводной диаграммы. Поле данных обычно содержит числа, такие как статистические значения или объемы продаж).

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

Невозможно перетащить поле

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

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

Проверьте используемые исходные данные OLAP . В отчетах, основанных на исходных данных OLAP ( OLAP . Технология работы с базами данных, ориентированная на запросы и отчеты, а не на обработку транзакций. Данные OLAP упорядочены иерархически и хранятся не в таблицах, а в кубах . ), некоторые поля могут быть использованы только как ряды (Поле ряда. Поле, отображаемое в области рядов отчета сводной диаграммы. Элементы в поле рядов указаны в легенде и дают имена отдельным рядам данных. ), категории (Поле категории. Поле, выводящееся в области категорий отчета сводной диаграммы . Элементы поля категории отображаются как подписи по оси категорий. ) или поля страниц (Поле страницы. Поле отчета сводной таблицы или сводной диаграммы, соответствующее странице в отчете сводной таблицы или сводной диаграммы. Можно выводить как итоги по всем элементам в поле страницы, так и элементы по отдельности (при этом выполняется отбор данных всех других позиций). ). Эти поля обозначены

значками в окне Список полей сводной таблицы. Поля со значками могут быть использованы только как поля

данных (Поле данных. Поле исходного списка, таблицы или базы данных, которое является источником данных для вычислений в отчете сводной таблицы или в отчете сводной диаграммы. Поле данных обычно содержит числа, такие как статистические значения или объемы продаж).

Проверьте макросы. Макросы (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций. Макросы записываются на языке программирования Visual Basic для приложений. ) на языке Visual Basic для приложений ( VBA ) могут отключать возможность изменения макета отчета перетаскиванием полей. Отображение в качестве указателя знака недопустимости операции при попытке перемещения поля означает, что для этого поля отключена возможность перемещения. Обратитесь за консультацией к разработчику макроса.

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

Список полей отсутствует

Щелкните отчет. Список полей отображается, если выбран отчет.

Отобразите список полей. Если список полей по-прежнему не отображается, нажмите кнопку Показать имена полей на панели инструментов Сводные таблицы.

Поле со списком не отображается

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

Поле должно соответствовать размерности куба. В отчетах, основанных на исходных данных OLAP ( OLAP . Технология работы с базами данных, ориентированная на запросы и отчеты, а не на обработку транзакций. Данные OLAP упорядочены иерархически и хранятся не в таблицах, а в кубах. ), поля размерности (Размерность. Структура OLAP , упорядочивающая данные по уровням (например, Страна/Область/Город для размерности "География"). В сводной таблице или сводном отчете каждая размерность становится набором полей, сведения которого можно развертывать и свертывать. ) в области рядов и категорий имеют стрелку в кнопке поля (Кнопка поля. Кнопка, обычно определяющая поле в отчете сводной таблицы или отчете сводной диаграммы. Кнопки полей можно перетаскивать для изменения макета отчета либо нажимать стрелки рядом с ними для выбора уровня подробных данных, отображаемых в отчете. ) только в том случае, если соответствующее поле относится к самому верхнему уровню размерности. Эта стрелка позволяет отображать или скрывать различные уровни подробности данных, которые представлены в размерности.

Проверьте, отображены ли верхние уровни. Если верхние уровни (Уровень. Часть размерности OLAP . В пределах размерности данные делятся по уровням детализации. Например: "Год". "Квартал". "Месяц" и "День" в размерности "Время" ) размерности скрыты, верхнее поле также не отображается, а выводимые на экран поля не имеют стрелки. Щелкните правой кнопкой мыши любую кнопку поля в измерении, а затем в контекстном меню выберите команду Показать уровни.

Поле со списком не работает

Убедитесь, что на панели инструментов Сводная таблица нажата кнопка Всегда отображать элементы. Если включать это средство нежелательно. перетащите поле в область данных (Область данных. Часть отчета сводной таблицы, содержащая итоговые данные. Значение каждой ячейки области данных представляет собой итог данных, содержащихся в исходных записях или строках ). Если область данных будет содержать поле, поля со списком будут работать для всех полей в отчете.

Форматирование отчета

Не отображается форматирование, линии тренда и планки погрешности

Проверьте изменение в отображаемых данных. При внесении изменений, влияющих на отображаемые данные в отчете сводной диаграммы или связанном отчете сводной таблицы (Связанный отчет сводной таблицы. Отчет сводной таблицы, предоставляющий данные для отчета сводной диаграммы. Он создается автоматически при создании отчета сводной диаграммы. При изменении макета любого из этих отчетов также изменяется и другой макет), все форматирование, примененное к подписям значений (Подпись данных. Подпись с дополнительными сведениями о маркере данных, представляющем одну точку данных или значение ячейки листа. ), точкам данных (Элементы данных. Отдельные значения, отображаемые на диаграмме. Соседние элементы данных образуют ряд данных. Элементы данных отображаются в виде полос, столбцов, линий, секторов, точек или других объектов. Эти объекты называются маркерами данных. ) и рядам данных (Ряд данных. Набор связанных между собой элементов данных, отображаемых на диаграмме. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных), включая все добавленные линии тренда (Линия тренда. Графическое представление направления изменения ряда данных, например повышающаяся линия обозначает увеличение продаж за определенное количество месяцев. Линии тренда используются для анализа ошибок предсказания, что также называется регрессионным анализом . ) и планки погрешности (Планки погрешностей. Обычно используются на диаграммах со статистическими или научными данными. Они показываю! возможную погрешность или степень неопределенности по отношению ко всем маркерам данных в ряде данных), утрачивается.

К изменениям, которые приводят к утере форматирования, относятся: изменение макета, добавление и удаление полей (Поле. В отчете сводной таблицы или сводной диаграммы категория данных, получаемых из поля исходных данных. Отчеты сводной таблицы содержат поля строк, столбцов, страниц и данных. Отчеты сводной диаграммы содержат поля рядов, категорий, страниц и данных), отображение и скрытие элементов (Элемент. Подкатегория поля в отчетах сводной таблицы и сводной диаграммы. Например, поле «Месяц» будет включать такие элементы как «Январь». «Февраль» и т. п. ), отображение разных страниц в поле страницы (Поле страницы. Поле отчета сводной таблицы или сводной диаграммы, соответствующее странице в отчете сводной таблицы или сводной диаграммы. Можно выводить как итоги по всем элементам в поле страницы, так и элементы по отдельности (при этом выполняется отбор данных всех других позиций). ), группировка и разгруппировка элементов, отображение и скрытие элементов и сведений, сортировка, изменение групповой функции (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или

базах данных. Примерами итоговых функций могут служить функции СУММ, СЧЕТ и СРЕДНЕЕ. ) для поля, изменение Отображения промежуточных итогов, указание различных исходных данных (Исходные данные. Список или таблица. используемые для создания отчета сводной таблицы или отчета сводной диаграммы. Исходные данные могут быть взяты из списка или диапазона Microsoft Excel , внешней базы данных или куба либо другого отчета сводной таблицы), включая изменение запроса (Запрос. В Microsoft Query или Microsoft Access средство поиска данных, отвечающих на определенный вопрос о данных, хранящихся в источнике данных. ) внешних данных и обновление (Обновление. Повторное построение отчета сводной таблицы или отчета сводной диаграммы для отражения изменения исходных данных. Если отчет создан на основе внешних данных, при обновлении будет запущен запрос на получение новых или измененных данных. ) отчета.

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

Запишите макрос, применяемый к форматированию. Если требуется частое изменение отчета, можно записать макрос (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций. Макросы записываются на языке программирования Visual Basic для приложений), что позволит автоматизировать задачу форматирования отчетов.

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

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

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

Изменение размера заголовков. Невозможно переместить заголовок, но можно изменить его размер, изменив размер шрифта. Щелкните заголовок, выберите Выделенное название диаграммы в меню Формат, перейдите на вкладку Шрифт, а затем выберите нужный размер.

Отчет не использует позицию или размер элементов на основе пользовательского типа диаграммы

Невозможно переместить или изменить размер легенды (Легенда. Подпись, определяющая узор или цвета рядов или категорий данных на диаграмме), заголовков (Названия на диаграммах. Описательный текст, располагающийся вдоль осей или по центру в верхней части диаграммы. ) или области построения (Область построения. На плоских диаграммах ограниченная осями область, содержащая все ряды данных. На объемных диаграммах ограниченная осями область, содержащая все ряды данных, имена категорий, подписи делений и названия осей. ) в отчете сводной диаграммы. Microsoft Excel автоматически размешает и определяет размер этих элементов каждый раз. когда отчет обновляется. Таким образом, при определении пользовательского типа диаграммы для отчета сводной диаграммы отчет использует автоматическую позицию и размер легенды, заголовков и области построения вместо сохраненных в типе диаграммы.

Форматирование чисел не соответствует исходным данным.

Microsoft Excel не использует форматирование чисел из исходных данных Microsoft Excel на оси значений в отчете сводной диаграммы. Ось значений первоначально отображает форматирование чисел области данных (Область данных. Часть отчета сводной таблицы, содержащая итоговые данные. Значение каждой ячейки области данных представляет собой итог данных, содержащихся в исходных записях или строках. ) связанного отчета сводной таблицы (Связанный отчет сводной таблицы. Отчет сводной таблицы, предоставляющий данные для отчета сводной диаграммы. Он создается автоматически при создании отчета сводной диаграммы. При изменении макета любого из этих отчетов также изменяется и другой макет).

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

Данные и вычисления

Команда Исходные данные недоступна в меню Диаграмма

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

Чтобы преобразовать поля рядов (Поле ряда. Поле, отображаемое в области рядов отчета сводной диаграммы. Элементы в поле рядов указаны в легенде и дают имена отдельным рядам данных. ) в поля категорий (Поле категории. Поле, выводящееся в области категорий отчета сводной диаграммы. Элементы поля категории отображаются как подписи по оси категорий. ) и наоборот, перетащите поля на соответствующие области (Область размещения. Область в окне отчета сводной таблицы или сводной диаграммы, в которую можно перетаскивать поля из списка полей для отображения данных поля. Надписи на каждой области размещения указывают типы полей, которые можно создавать в этом отчете. ) Чтобы включить различные исходные данные в отчет, щелкните отчет, выберите команду Мастер сводных таблиц и диаграмм в меню Данные, нажмите кнопку Назад и на втором шаге мастера определите различные исходные данные или внешние данные для отчета.

Параметры поля страницы недоступны

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

Проверьте внешний источник данных. Возможно, отчет создан не на основе внешних данных. Эти параметры недоступны в отчетах, основанных на данных листа Microsoft Excel .

Проверьте источник данных OLAP . Эти параметры недоступны для отчетов, основанных на источнике данных OLAP ( OLAP Технология работы с базами данных, ориентированная на запросы и отчеты, а не на обработку транзакций. Данные OLAP упорядочены иерархически и хранятся не в таблицах, а в кубах). В отчетах, основанных на источнике данных OLAP .

Проверьте тип данных поля. Параметры поля страницы недоступны для полей MEMO и полей с объектами OLE ( OLE . Технология связи программ, позволяющая приложениям совместно использовать данные. Все программы Microsoft Office поддерживают технологию OLE . что позволяет совместно работать с данными посредством внедрения и связывания объектов).

Проверьте наличие поддержки параметрических запросов. Драйвер ODBC ( Драйвер ODBC (Open Database Connectivity). Программа, используемая для подключения к конкретной базе данных. Каждая база данных, например Microsoft Access или dBASE , или система управления базой данных, например SQL Server , требует своего драйвера ODBC . ) для внешней базы данных может не поддерживать параметрические запросы (Параметрический запрос. Тип запроса, запрашивающий при выполнении один или несколько параметров (условий), так что один и тот же запрос может использоваться несколько раз для разных целей или в разных ситуациях. ) Обсуждаемые здесь параметры доступны только в том случае, когда используемый источник данных (Источник данных. Хранящийся набор сведений об «источнике», служащих для подключения к базе данных. Источник данных может содержать сведения об имени и местоположении сервера баз данных, имени драйвера базы данных, а также данные, которые могут потребоваться для подключения к базе. ) поддерживает такие виды запросов.

Драйверы ODBC , поставляемые с Microsoft Query , поддерживают запросы с параметрами. Чтобы узнать, поддерживает ли запросы с параметрами драйвер стороннего производителя, обратитесь к поставщику драйвера.

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

Проверьте, не входит ли поле в группу. В связанном отчете сводной таблицы (Связанный отчет сводной таблицы. Отчет сводной таблицы, предоставляющий данные для отчета сводной диаграммы. Он создается автоматически при создании отчета сводной диаграммы. При изменении макета любого из этих отчетов также изменяется и другой макет. ) перетащите поле в область строк или столбцов, щелкните правой кнопкой мыши любые группированные элементы (Элемент. Подкатегория поля в отчетах сводной таблицы и сводной диаграммы. Например, поле «Месяц» будет включать такие элементы как «Январь». «Февраль» и т. п. ), в контекстном меню выберите команду Группа и структура, нажмите кнопку Разгруппировать, а затем перетащите поле обратно в область страницы, чтобы сделать установки поля страницы доступными.

Не удается изменить лист во время выполнения запроса Проверьте, не обновляется ли область, которую требуется изменить. Пока запрос выполняется в фоновом

режиме, невозможно изменить данный отчет и отчеты, основанные на данном.

Дождитесь завершения запроса или отмените его. Чтобы проследить выполнение запроса или отменить его.

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

Выполнение запроса в фоновом режиме. На панели инструментов Сводная таблица выберите вкладку

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

Для вычислений в поле данных в качестве итоговой функции вместо функции «Сумма» используется

функция «количество значений»

Проверьте поле на наличие текста или пустых ячеек. Если поле данных содержит текст или пустые ячейки, дня него по умолчанию используется итоговая функция (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных Примерами итоговых функций могут служить функции СУММ. СЧЕТ и СРЕДНЕЕ. ) <<Количество значений>>. Дважды щелкните это поле и выберите функцию Сумма в поле Операция.

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

Отсутствуют данные после обновления отчета

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

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

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

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

Выберите команду Сводная таблица в меню Данные.

Нажмите кнопку Назад.

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

Нажмите кнопку Готово.

Убедитесь, что запрос выбирает правильные данные. Для отчетов, основанных на внешних данных, проверьте запрос в Microsoft Query , чтобы убедиться в извлечении требуемых данных.

Проверьте куб или файл куба. Если отчет основан на кубе (Куб. Структура данных OLAP . Куб содержит размерности, такие как Страна/Область/Город, и поля данных, такие как «Сумма продаж». Размерности служат для иерархического представления данных по уровням детализации, а поля данных — для хранения количественных значений), созданном мастером куба OLAP , откройте файл с расширением. oqy в Microsoft Query и проверьте содержимое куба, чтобы убедиться в том, что он содержит требуемые данные. Если отчет основан на файле куба, проверьте содержимое файла куба. Щелкните связанный отчет сводной таблицы (Связанный отчет сводной таблицы. Отчет сводной таблицы, предоставляющий данные для отчета сводной диаграммы. Он создается автоматически при создании отчета сводной диаграммы. При изменении макета любого из этих отчетов также изменяется и другой макет. ) для отчета сводной диаграммы, выберите Сводная таблица на панели инструментов Сводная таблица, нажмите Автономный режим OLAP . а затем нажмите кнопку Изменить автономный файл данных. Если отчет основан на исходных данных из базы данных на сервере OLAP . обратитесь к администратору базы данных для выяснения возможных изменений в базе данных.

Отчет выглядит совершенно иначе после обновления либо его не удается обновить

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

Проверьте изменения в исходной базе данных. Если отчет основан на исходных данных OLAP ( OLAP . Технология работы с базами данных, ориентированная на запросы и отчеты, а не на обработку транзакций Данные OLAP упорядочены иерархически и хранятся не в таблицах, а в кубах . ), возможно, были внесены изменения в данные куба на сервере Дополнительные подробности можно узнать у администратора сервера OLAP .

О надстройке «Поиск решения»

Поиск решений является частью блока задач, который иногда называют анализ "что-если" (Анализ «что-если». Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе. Например, изменение процентной ставки, используемой в таблице амортизации для определения сумм платежей). Процедура поиска решения позволяет найти оптимальное значение формулы (Формула. Совокупность значений, ссылок на другие ячейки, именованных объектов, функций и операторов, позволяющая получить новое значение. Формула всегда начинается со знака =. ) содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения (Ограничения. Ограничения на значения изменяемых ячеек, конечных ячеек или других ячеек, прямо или косвенно связанных друг с другом, задаваемые при постановке задачи). Эти ограничения могут ссылаться на другие влияющие ячейки.

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

Пример поиска решения: В приведенном ниже примере объем продаж в каждом квартале зависит от уровня рекламы, что косвенно определяет сумму доходов, издержки, а также прибыль. Чтобы найти максимальную возможную сумму общего дохода, процедура поиска решения может повышать ежеквартальные расходы на рекламу (ячейки В5: С5). пока общие расходы не превысят ограничения в 20 тысяч (ячейка F 5). Значения во влияющих ячейках служат для вычисления дохода за каждый квартал, поэтому они связаны с формулой в целевой ячейке F 7, =СУММА( Q 1 Прибыль: Q 2 Прибыль).

Изменяемые ячейки Ячейка с ограничениями Целевая ячейка

Примеры расчетов с использованием процедуры поиска решения: В составе Microsoft Excel в папке Office Samples находится книга с примерами ( Solvsamp . xls ) использования процедуры поиска решения ( Solver . xls ).

Примеры, содержащиеся в книге Solvsamp . xls , помогут разрешить ваши вопросы. Чтобы применить любой из шести примеров: «Структура производства». «Транспортная задача». «График занятости». «Управление капиталом», «Портфель ценных бумаг» и «Проектирование цепи». — откройте книгу, перейдите к нужному листу и выберите команду Поиск решения в меню Сервис В примерах уже подобраны целевая и влияющие ячейки, а также ограничения.

Visual Basic

Добавление кнопки, меню или команды

Выполните одно или оба следующих действия.

Добавьте кнопку, меню или команду на панель инструментов

Убедитесь, что панель инструментов, которую требуется изменить, отображается (В меню Вид выберите команду Панели инструментов Выполните одно из следующих действий: щелкните название панели инструментов, которую требуется отобразить; для просмотра большего числа панелей инструментов выберите команду Настройка. В диалоговом окне Настройка откройте вкладку Панели инструментов, а затем установите флажок для панели инструментов, которую требуется отобразить. Нажмите кнопку Закрыть.

Нажмите кнопку со стрелкой Параметры панелей инструментов. Выберите команду Добавить или удалить кнопки, а затем — команду Настройка. Выполните одно из следующих действий. Добавьте кнопку. Откройте вкладку Команды. В поле Категории выберите категорию для команды, которую должна выполнять кнопка. Перетащите нужную команду или макрос (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций. Макросы записываются на языке программирования Visual Basic для приложений. ) из поля Команды на отображаемую панель инструментов.

Добавьте встроенное меню на панель инструментов

Откройте вкладку Команды.

В поле Категории выберите Встроенные меню.

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

Добавьте настроенное меню на панель инструментов

Откройте вкладку Команды.

В поле Категории выберите Новое меню.

Перетащите строку Новое меню из поля Команды на отображаемую панель инструментов.

Щелкните новое меню на панели инструментов правой кнопкой мыши, введите имя в поле Имя в
контекстном меню, а затем нажмите клавишу ENTER .

Нажмите кнопку Закрыть в диалоговом окне Настройка.

Добавьте команду в меню

Если меню, которое требуется изменить, находится на панели инструментов (Панель инструментов. Панель с кнопками и параметрами, которые используются для выполнения команд. Для отображения панелей инструментов используется диалоговое окно Настройка (выберите в меню Вид команду Панели инструментов, а затем — команду Настройка). Для просмотра дополнительных кнопок нажмите кнопку Другие кнопки в конце панели инструментов), убедитесь, что эта панель инструментов отображается (В меню Вид выберите команду Панели инструментов. Выполните одно из следующих действий: щелкните название панели инструментов, которую требуется отобразить; для просмотра большего числа панелей инструментов выберите команду Настройка. В диалоговом окне Настройка откройте вкладку Панели инструментов, а затем установите флажок для панели инструментов , которую требуется отобразить Нажмите кнопку Закрыть. В меню Вид выберите команду Панели инструментов. Выберите команду Настройка, а затем откройте вкладку Команды. В поле Категории выберите категорию для команды. Перетащите нужную команду из поля Команды и. не отпуская кнопку мыши, наведите указатель на меню, которое требуется изменить. Когда откроется список команд меню, наведите указатель на то место, где должна отображаться команда, а затем отпустите кнопку мыши )

Выполнение макроса

Выполнение макроса

Установите Средний или Низкий уровень безопасности (В меню Сервис выберите команду Параметры. Откройте вкладку Безопасность. В группе Безопасность макросов нажмите кнопку Безопасность макросов. Откройте вкладку Уровень безопасности, а затем выберите нужный уровень безопасности. )

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

В меню Сервис выберите пункт Макрос, а затем выполните команду Макросы. В поле Имя макроса введите имя того макроса, который требуется выполнить. Чтобы прервать выполнение, нажмите кнопку ESC .

Совет

Чтобы выполнить другой макрос, находясь в редакторе Visual Basic (Редактор Visual Basic Среда разработки новых и редактирования существующих программ и процедур Visual Basic для приложений. Редактор Visual Basic включает полный набор средств отладки, обеспечивающих обнаружения ошибок синтаксиса, ошибок выполнения и логических ошибок в программах . ), выберите команду Macros в меню Tools В поле Macro name введите имя того макроса, который нужно выполнить, а затем нажмите кнопку Run .

Назначение клавиши для запуска макроса

В меню Сервис выберите пункт Макрос, а затем выполните команду Макросы.

В поле Имя макроса введите имя того макроса (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций. Макросы записываются на языке программирования Visual Basic для приложений), которому нужно присвоить сочетание клавиш (Клавиши быстрого вызова. Функциональные клавиши или сочетания клавиш, такие как F 5 или CTRL + A , которые служат для выполнения команд меню. В отличие от них. клавишами доступа называют сочетания клавиш, такие как ALT +Ф, которые переводят фокус в меню, на команду или на элемент управления).

Нажмите кнопку Параметры.

Если этот макрос потребуется запускать нажатием сочетания клавиш (Клавиши быстрого вызова. Функциональные клавиши или сочетания клавиш, такие как F 5 или CTRL +А. которые служат для выполнения команд меню. В отличие от них, клавишами доступа называют сочетания клавиш, такие как ALT +Ф. которые переводят фокус в меню, на команду или на элемент управления. ) на клавиатуре, введите букву в поле Сочетание клавиш. Допускается использование сочетаний CTRL + буква (для строчных букв) или CTRL + SHIFT + буква (для прописных букв), где буква — любая буквенная клавиша на клавиатуре. Нельзя использовать сочетания клавиш с цифрами и специальными знаками, такими как @ или #.

Примечание. Выбранное сочетание клавиш заменяет все совпадающие стандартные сочетания клавиш Microsoft Excel на то время, пока открыта книга, содержащая данный макрос.

Назначение кнопки или графического объекта для запуска макроса Щелкните кнопку или графический объект, чтобы появились маркеры изменения размера (Маркер изменения

размера. Один из маленьких кружков или квадратиков, отображаемых в углах и на сторонах выделенного объекта. При

перетаскивании этих маркеров изменяются размеры объекта).

Выделив этот объект, щелкните правой кнопкой мыши его маркер изменения размера, чтобы вызвать

контекстное меню (Контекстное меню. Меню, содержащее список команд, относящихся к конкретному объекту. Для

вызова контекстного меню щелкните объект правой кнопкой мыши или нажмите клавиши SHIFT + F 10. ). Выберите в контекстном меню команду Назначить макрос.

Выполните одно из следующих действий.

Чтобы назначить ранее записанному макросу (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций. Макросы записываются на языке программирования Visual Basic для приложений. ) выбранный графический объект, введите название макроса в поле Имя макроса, а затем нажмите кнопку ОК.

о Чтобы создать новый макрос и назначить ему выбранный графический объект, нажмите кнопку Запись . После завершения записи макроса нажмите кнопку Остановка записи на панели инструментов Остановить запись. о Чтобы написать новый макрос в редакторе Visual Basic (Редактор Microsoft Visual Basic . Среда,

позволяющая редактировать записанные макросы, а также создавать новые макросы и программы на языке Visual Basic для приложений), нажмите кнопку Создать. Чтобы отредактировать существующий макрос, выберите его имя в списке Имя макроса и нажмите кнопку Правка.

Примечание. При назначении макроса объекту или кнопке, которые уже используются как гиперссылка (Гиперссылка. Цветной подчеркнутый текст или графический объект, по щелчку которого выполняется переход к файлу, фрагменту файла или странице HTML в интрасети или Интернете. Гиперссылки могут также указывать на группы новостей и узлы Gopher . Telnet и FTP ), информация о гиперссылке удаляется. При нажатии кнопки или выделении объекта будет выполняться макрос.

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

Выделив только что нарисованный объект, щелкните правой кнопкой мыши его маркер изменения размера (Маркер изменения размера. Один из маленьких кружков или квадратиков, отображаемых в углах и на сторонах выделенного объекта. При перетаскивании этих маркеров изменяются размеры объекта. ), чтобы вызвать контекстное меню (Контекстное меню. Меню, содержащее список команд, относящихся к конкретному объекту. Для вызова контекстного меню щелкните объект правой кнопкой мыши или нажмите клавиши SHIFT + F 10. ).

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

Чтобы назначить ранее записанному макросу (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций. Макросы записываются на языке программирования Visual Basic доя приложений. ) выбранный графический объект, введите название макроса в поле Имя макроса, а затем нажмите кнопку ОК.

о Чтобы создать новый макрос и назначить ему выбранный графический объект, нажмите кнопку Запись. После завершения записи макроса нажмите кнопку Остановка записи на панели инструментов Остановить запись. о Чтобы написать новый макрос в редакторе Visual Basic (Редактор Microsoft Visual Basic . Среда.

позволяющая редактировать записанные макросы, а также создавать новые макросы и программы на языке Visual Basic для приложений), нажмите кнопку Создать. Чтобы отредактировать существующий макрос, выберите его имя в списке Имя макроса и нажмите кнопку Правка. В меню Формат выберите команду Автофигура, а затем вкладку — Цвет и линии.

В группе Заливка выберите параметр Нет заливки в поле Цвет. В группе Линия выберите параметр Нет линий в поле Цвет.

Повторите эти шаги для всех областей, которые требуется создать. Нет необходимости создавать отдельный объект для каждой области.

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

Выберите команду Настройка в меню Сервис.

Выполните одно из следующих действий.

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

Если на панели инструментов отсутствует кнопка, которая должна запускать макрос (Макрос. Макрокоманда или набор макрокоманд, используемый для автоматического выполнения некоторых операций Макросы записываются на языке программирования Visual Basic для приложений), откройте вкладку Команды, выберите пункт Макросы в списке Категории. Из списка Команды перетащите мышью настраиваемую кнопку на панель инструментов.

Щелкните эту кнопку правой кнопкой мыши и выберите команду Назначить макрос в контекстном меню (Контекстное меню. Меню, содержащее список команд, относящихся к конкретному объекту. Для вызова контекстного меню щелкните объект правой кнопкой мыши или нажмите клавиши SHIFT + F 10).

Введите название макроса в поле Имя макроса, затем нажмите кнопку ОК.