Цепь в английском Excel

Обновлено: 03.07.2024

Пользователь может инициировать перерасчет в Microsoft Excel несколькими способами, например:

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

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

Зависимость, грязные ячейки и пересчитанные ячейки

Вычисление рабочих листов в Excel можно рассматривать как трехэтапный процесс:

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

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

Когда в книгу вносятся структурные изменения, например при вводе новой формулы, Excel восстанавливает дерево зависимостей и цепочку вычислений. При вводе новых данных или новых формул Excel помечает все ячейки, зависящие от этих новых данных, как требующие пересчета. Ячейки, помеченные таким образом, называются грязными. Все прямые и косвенные зависимости помечаются как грязные, поэтому, если B1 зависит от A1, а C1 зависит от B1, при изменении A1 и B1, и C1 помечаются как грязные.

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

После пометки ячеек как грязных при последующем выполнении пересчета Excel повторно оценивает содержимое каждой грязной ячейки в порядке, определяемом цепочкой вычислений. В приведенном ранее примере это означает, что сначала идет B1, а затем C1. Этот пересчет происходит сразу после того, как Excel закончит помечать ячейки как грязные, если режим пересчета является автоматическим; в противном случае это происходит позже.

Начиная с Microsoft Excel 2002, объект Range в Microsoft Visual Basic для приложений (VBA) поддерживает метод Range.Dirty, который помечает ячейки как требующие вычисления. Когда он используется вместе с методом Range.Calculate (см. следующий раздел), он включает принудительный пересчет ячеек в заданном диапазоне. Это полезно, когда вы выполняете ограниченные вычисления во время макроса, когда режим вычисления установлен на ручной, чтобы избежать накладных расходов на вычисление ячеек, не связанных с функцией макроса. Методы расчета диапазона недоступны через C API.

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

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

Асинхронные определяемые пользователем функции (UDF)

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

Изменяемые и неизменяемые функции

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

Следующие функции Excel являются энергозависимыми:

И VBA, и C API поддерживают способы информирования Excel о том, что определяемая пользователем функция (UDF) должна обрабатываться как изменчивая. С помощью VBA определяемая пользователем функция объявляется изменчивой следующим образом.

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

С помощью C API вы можете зарегистрировать функцию XLL как энергозависимую перед ее первым вызовом. Это также позволяет вам включать и выключать изменчивый статус функции рабочего листа.

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

Режимы расчета, команды, выборочный пересчет и таблицы данных

Excel поддерживает три режима расчета:

Автоматически
Автоматические таблицы исключений
Вручную

Если для расчета задан автоматический режим, пересчет происходит после каждого ввода данных и после определенных событий, таких как примеры, приведенные в предыдущем разделе. Для очень больших книг время пересчета может быть настолько большим, что пользователи должны ограничивать его, то есть пересчитывать только тогда, когда это необходимо. Для этого Excel поддерживает ручной режим. Пользователь может выбрать режим через систему меню Excel или программно, используя VBA, COM или C API.

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

Пересчет таблиц данных выполняется несколько иначе:

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

Учитывая другой способ, которым Excel обрабатывает пересчет таблиц данных, а также тот факт, что расчет больших таблиц, зависящих от сложных или длительных вычислений, может занять много времени, Excel позволяет отключить автоматический расчет таблиц данных. Для этого установите режим расчета «Автоматически, кроме таблиц данных». Когда расчет находится в этом режиме, пользователь пересчитывает таблицы данных, нажимая F9 или аналогичную программную операцию.

Excel предоставляет методы, с помощью которых можно изменить режим пересчета и управлять пересчетом. Эти методы улучшались от версии к версии, чтобы обеспечить более точный контроль. Возможности C API в этом отношении отражают те, которые были доступны в Excel версии 5, и поэтому не дают вам такого же контроля, как при использовании VBA в более поздних версиях.

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

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

VBA: Диапазон.Вычисление (представлено в Excel 2000, изменено в Excel 2007) и Range.CalculateRowMajorOrder (представлено в Excel 2007)

C API: не поддерживается

Ручной режим

Пересчитывает только ячейки в заданном диапазоне независимо от того, грязные они или нет. Поведение метода Range.Calculate изменено в Excel 2007; однако метод Range.CalculateRowMajorOrder по-прежнему поддерживает старое поведение.

Автоматический или автоматический режим кроме таблиц

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

Активное вычисление рабочего листа

VBA: ActiveSheet.Calculate

C API: xlcCalculateDocument

Все режимы

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

Указанный расчет рабочего листа

VBA: **Рабочие листы (**ссылка).Рассчитать

C API: не поддерживается

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

Excel 2000 и более поздние версии предоставляют логическое свойство рабочего листа, свойство EnableCalculation. Установка для этого параметра значения True вместо False загрязняет все ячейки на указанном листе. В автоматических режимах это также вызывает пересчет всей книги.

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

Перестроение дерева рабочей книги и принудительный перерасчет

Нажатие клавиши: CTRL+ALT+SHIFT+F9 (появилось в Excel 2002)

VBA: **Workbooks(**reference).ForceFullCalculation (появилось в Excel 2007)

C API: не поддерживается

Все режимы

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

Все открытые книги

VBA: Application.Calculate

C API: xlcCalculateNow

Все режимы

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

Перестроение дерева всех открытых книг и принудительный расчет

VBA: Application.CalculateFull

C API: не поддерживается

Все режимы

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКB в Microsoft Excel.

Описание

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

Эта функция возвращает 4, поскольку "n" — четвертый символ в слове "принтер".

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

возвращает 5, так как слово "база" начинается с пятого символа слова "база данных". Вы можете использовать функции SEARCH и SEARCHB, чтобы определить расположение символа или текстовой строки в другой текстовой строке, а затем использовать функции MID и MIDB, чтобы вернуть текст, или использовать функции REPLACE и REPLACEB, чтобы изменить текст. Эти функции продемонстрированы в примере 1 этой статьи.

Эти функции могут быть доступны не на всех языках.

SEARCHB считает 2 байта на символ только в том случае, если язык DBCS установлен в качестве языка по умолчанию. В противном случае SEARCHB ведет себя так же, как SEARCH, считая 1 байт на символ.

Языки, поддерживающие DBCS, включают японский, китайский (упрощенный), китайский (традиционный) и корейский.

Синтаксис

Функции SEARCH и SEARCHB имеют следующие аргументы:

find_text Обязательно. Текст, который вы хотите найти.

внутри_текста Обязательно. Текст, в котором вы хотите найти значение аргумента find_text.

start_num Необязательный. Номер символа в аргументе within_text, с которого вы хотите начать поиск.

Примечание

Функции ПОИСК и ПОИСКВ не чувствительны к регистру. Если вы хотите выполнить поиск с учетом регистра, вы можете использовать FIND и FINDB.

Вы можете использовать подстановочные знаки — вопросительный знак (?) и звездочку (*) — в аргументе find_text.Знак вопроса соответствует любому одиночному символу; звездочка соответствует любой последовательности символов. Если вы хотите найти фактический вопросительный знак или звездочку, введите тильду (~) перед символом.

Если аргумент start_num не указан, предполагается, что он равен 1.

Используйте start_num, чтобы пропустить указанное количество символов. Используя в качестве примера функцию ПОИСК, предположим, что вы работаете с текстовой строкой «AYF0093.YoungMensApparel». Чтобы найти позицию первой буквы «Y» в описательной части текстовой строки, установите start_num равным 8, чтобы часть текста с порядковым номером (в данном случае «AYF0093») не искал. Функция ПОИСК начинает операцию поиска с позиции восьмого символа, находит символ, указанный в аргументе найти_текст, на следующей позиции и возвращает число 9. Функция ПОИСК всегда возвращает количество символов от начала аргумента within_text, считая символы, которые вы пропускаете, если аргумент start_num больше 1.

Примеры

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

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

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

Описание

Заменяет полноразмерные (двухбайтовые) английские буквы или буквы катаканы в строке символов на полуширинные (однобайтовые) символы

Преобразует число в текст, используя формат валюты ß (бат)

Возвращает символ, заданный кодовым номером

Удаляет все непечатаемые символы из текста

Возвращает числовой код для первого символа в текстовой строке

Объединяет текст из нескольких диапазонов и/или строк, но не предоставляет разделитель или аргументы IgnoreEmpty.

Объединяет несколько текстовых элементов в один текстовый элемент

Функция DBCS

Заменяет полуширинные (однобайтовые) английские буквы или катакану в строке символов на полноразмерные (двухбайтовые) символы

Преобразует число в текст, используя формат денежной единицы $ (доллар США)

Проверяет, идентичны ли два текстовых значения

Находит одно текстовое значение внутри другого (с учетом регистра)

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

Возвращает крайние левые символы из текстового значения

Возвращает количество символов в текстовой строке

Преобразует текст в нижний регистр

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

Функция ЧИСЛОЗНАЧ

Преобразует текст в число независимо от региональных настроек

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

Первая буква в каждом слове текстового значения заглавная

Заменяет символы в тексте

Повторяет текст заданное количество раз

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

Находит одно текстовое значение внутри другого (без учета регистра)

Заменяет старый текст в текстовой строке новым текстом

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

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

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

Удаляет пробелы из текста

функция UNICHAR

Возвращает символ Unicode, на который ссылается заданное числовое значение

Функция UNICODE

Возвращает число (кодовую точку), соответствующее первому символу текста

Преобразует текст в верхний регистр

Преобразует текстовый аргумент в число

Важно! Расчетные результаты формул и некоторых функций листа Excel могут незначительно отличаться на ПК с Windows, использующем архитектуру x86 или x86-64, и ПК с Windows RT, использующем архитектуру ARM. Узнайте больше о различиях.

Использование функции в качестве одного из аргументов в формуле, использующей функцию, называется вложением, и мы будем называть эту функцию вложенной функцией. Например, при вложении функций СРЗНАЧ и СУММ в аргументы функции ЕСЛИ следующая формула суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50. В противном случае возвращается 0.

Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

В формулу можно вложить до 64 уровней функций.

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

Чтобы начать формулу с функцией, нажмите "Вставить функцию" в строке формул.

Excel вставит за вас знак равенства (=).

В поле Или выберите категорию выберите Все.

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

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

Чтобы ввести другую функцию в качестве аргумента, введите нужную функцию в поле аргумента.

Части формулы, отображаемые в диалоговом окне "Аргументы функции", отражают функцию, выбранную на предыдущем шаге.

Если вы нажали ЕСЛИ, в диалоговом окне Аргументы функции отобразятся аргументы функции ЕСЛИ. Чтобы вложить другую функцию, вы можете ввести ее в поле аргумента. Например, можно ввести СУММ(G2:G5) в поле Значение_если_истина функции ЕСЛИ.

Введите любые дополнительные аргументы, необходимые для завершения формулы.

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

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

После ввода аргументов формулы нажмите кнопку "ОК".

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

Чтобы начать формулу с функцией, нажмите "Вставить функцию" в строке формул.

В диалоговом окне "Вставить функцию" в поле "Выбрать категорию" выберите "Все".

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

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

Введите любые дополнительные аргументы, необходимые для завершения формулы.

После ввода аргументов формулы нажмите клавишу ВВОД.

Примеры

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

Читайте также: