Для упрощения ввода формул и расчетов в excel используют: Обзор формул — Служба поддержки Майкрософт

EHLEKTRONNAJA_TABLICA_EXCEL-teorija

ЭЛЕКТРОННАЯ ТАБЛИЦА EXCEL

Цели работы

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

Microsoft Excel.

2.Научиться вводить данные, формулы, вставлять функции, строить диаграммы.

I. Контрольные вопросы

1.Перечислите основные возможности Microsoft Excel?

2.Что собой представляет рабочее поле Microsoft Excel?

3.С чего должен начинаться ввод формулы в Microsoft Excel?

4.Что является основным элементом электронных таблиц?

5.Что такое абсолютная и относительная адресация в формулах Excel?

6.Что представляет собой функция автозаполнения в Excel?

7.В чем заключается форматирование содержимого ячеек в Excel?

II. Теоретическая часть

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

Основные понятия электронных таблиц

Запуск программы и структура окна Excel

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

Рис. 1

Первая строка — шапка окна с кнопками управления окном. Вторая строка — операционное меню Excel.

Третья строка — панель инструментов Стандартная. Четвертая строка — панель инструментов Форматирование.

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

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

Меню Excel

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

Строки, столбцы, ячейки

Рабочее поле электронной таблицы состоит из строк и столбцов. Максимальное количество строк 16384, столбцов — 256. Номер строки —

определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля. Буква столбца — определяет колонку в электронной таблице. Колонки нумеруются в следующем порядке: A — Z, AA — AZ, затем BA — BZ и т.д.

Каждое пересечение строки и столбца образует ячейку, в которую можно вводить данные (текст, число или формулы). Таким образом, каждая ячейка имеет адрес, состоящий из буквы столбца и номера строки. Например: B3, C128, AB15 и т.п. Указатель ячейки — выделенный прямоугольник, определяющий текущую ячейку. Указатель можно перемещать по таблице с помощью мыши или стрелок управления курсором. Текущая ячейка помечена указателем. По умолчанию ввод данных и некоторые другие действия относятся к текущей ячейке. Блок представляет собой прямоугольную область смежных ячеек, может состоять из одной или нескольких ячеек, строк, столбцов. Адрес блока состоит из координат противоположных углов, разделенных двоеточием. Например: D14:L20, A2:C16 или D:F. Блок можно задавать при выполнении команд или вводе формул посредством указания координат или выделения на экране. Электронная таблица Excel имеет трехмерную структуру. Она состоит из листов, как книга (таких листов 16). На экране виден только один лист — верхний. Нижняя часть листа содержит ярлычки других листов. Щелкая кнопкой мыши на ярлычках листов, можно перейти к другому листу.

Выделение столбцов, строк, блоков, таблицы

Выделение блока с помощью клавиатуры: 1) удерживая нажатой клавишу Shift, нажимать на соответствующие клавиши управления курсором; 2) нажать клавишу F8, что соответствует режиму выделения (индикатор режима работы Excel, в левом нижнем углу, будет иметь вид ВДЛ) и выделить блок с помощью клавиш управления курсором. ESC — выход из режима выделения.

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

Выделение несмежных блоков:

1) выделить первую ячейку или блок;

2)нажать и удерживать клавишу Ctrl;

3)выделить все последующие ячейки или блоки;

4)отпустить клавишу Ctrl .

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

Ввод данных

Содержимое ячеек

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

Вводимые данные Excel определяет по первому символу. Если первый символ — буква или знак (‘), то Excel считает, что вводится текст. Если первый символ цифра — вводится число, если первый символ — знак (=), то Excel считает, что вводится формула. Вводимые данные отображаются одновременно и в ячейке и в строке формул и помещаются в ячейку только при нажатии клавиши Enter или клавиши перемещения курсора.

Ввод текста и чисел

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

Если вы допустили ошибку, то выполненное действие можно отменить, используя команды операционного меню Правка, Отменить или кнопкой Отмена панели инструментов. Если при вводе чисел ширина введенного числа окажется больше, чем ширина ячейки на экране, то Excel отображает его в экспоненциальной форме (621000000 выглядит как 6,21E+08 или 0,000000003 в виде 3E-9) или вместо числа ставит символы ##### (при этом число сохраняется в памяти).

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

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

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

Рис. 3

Задание: Введите следующие данные:

вячейку В1 — Медицинский университет

вячейку С1 — Лечебный факультет

вячейку D1 — Педиатрический факультет

вячейку E1 — Стоматологический факультет

вячейку F1 — Фармацевтический факультет

вячейку С2 — 350

вячейку D2 — 75

вячейку E2 — 50

вячейку F2 — 45

Подберите размеры столбцов по содержимому.

Ввод формул

В виде формулы может быть записано арифметическое выражение. Оно представляет собой последовательность чисел или ссылок на ячейки, объединенных знаками арифметических операций или функций. Формула должна начинаться со знака (=). Она может включать до 240 символов и не должна содержать пробелов. Например: =15+6 или =32*16-325. Вместо конкретного числа можно указывать адрес ячейки, где это число хранится. Например: =C3+H6 или =A1*B2E28.

При обращении к ячейке используются три типа адресации: 1. Абсолютная адресация.

2.Относительная адресация.

3.Смешанная адресация.

Наиболее часто используемой является относительная адресация: A12, C5, H8 и т.п. При ее использовании в формулах Excel запоминает расположение относительно текущей ячейки. Если вы желаете сохранить ссылку на конкретную ячейку, то необходима абсолютная адресация. Для ее задания необходимо перед именем столбца и перед номером строки ввести символ ($). Например: $A$5, $B$18 .

Смешанная адресация — символ $ ставится там, где он необходим. Например: F$3, $C23. Адресация будет особенно важна при копировании ячеек.

Функции

Для упрощения ввода формул и расчетов в Excel используют функции. Пример содержимого ячейки с функцией =B3+SIN(E5), где SIN функция, в скобках ее аргумент (число или ссылка на ячейку, содержащую данные).

Некоторые функции: SQRT — квадратный корень, MAX — определение максимального числа из списка, SUM — сумма чисел списка. В русифицированных версиях имена функций могут задаваться на русском языке. СУММ — сумма, СРЗНАЧ — определение среднего значения списка. Например: СУММ(А1:А10) подсчитывает сумму чисел в ячейках с А1 по А10. Часто используемая функция суммирования вынесена на панель инструментов — кнопка суммы ∑ .

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

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

функции (рис.). По окончании задания аргументов, нажмите кнопку

. Необходимая вам формула будет введена в выделенную ячейку.

Рис. 5

При автосуммировании, после нажатия кнопки ∑ , можно с помощью мыши выделить необходимые строки или столбцы.

Редактирование таблиц

Изменение ширины столбцов и высоты строк

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

Редактирование данных в ячейке

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

Редактирование данных после завершения ввода:

выделите нужную ячейку, нажмите клавишу F2 для перехода в режим редактирования (индикатор режима высветит слово Правка) или дважды щелкните мышью на ячейке. После редактирования нажмите клавишу Enter или клавишу перемещения курсора.

Перемещение данных

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

Копирование данных

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

Удаление данных

Для удаления содержимого ячейки или блока выделите их и нажмите клавишу Del или выберите в операционном меню команду

Правка, Очистить.

Заполнение и автозаполнение

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

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

кнопку мы ши и, не отпуская, протащите мы шь. В выделенные ячейки будут введены авто матически остальные названия месяцев. Аналогично дни недели. Если в первую я чейку поместит ь число 2 , а во вторую 4, то после автозаполнен ия получим ряд 2, 4, 6, 8, 10 и т. д.

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

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

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

PLEX подробно :: Планета Excel

Возможности PLEX детально

Power Query

Набор инструментов для упрощения и ускорения работы с запросами Power Query.

Авто-отступы в формулах

Автоматическое добавление отступов и переносов строк в длинные и сложные формулы для наглядности.

Автогруппировка

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

АнтиФильтр

Инвертирование видимости всех отфильтрованных в данный момент строк.

Библиотека формул

Мощный инструмент для автоматизации ввода сложных формул. Позволяет ввести шаблон сложной формулы и использовать его в будущем для повторного ввода в расчетах. Cодержит более 50 готовых формул и пользовательских функций PLEX разных категорий.

Быстрый ввод времени

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

Бэкап книги в заданную папку

Быстрое и удобное сохранение резервных копий текущей книги с добавлением отметки даты-времени в заданную папку.

Вставка курса валюты на заданную дату

Вставка курса любой заданной валюты (доллар, евро, гривна, фунты и т.д. — всего 17 валют + ставки рефинансирования и ключевая) на любую заданную дату в прошлом. Данные берутся с официальных сайтов национальных банков России, Украины, Беларуси, Казахстана.

Выделятор

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

Выпадающий список с автопоиском

Диалоговое окно с выпадающим списком со встроенным поиском по первым введенным символам.

Генератор случайных данных

Все ячейки в выделенном диапазоне заполняются случайными числами (или датами) из заданного числового интервала. Целыми или дробными, с повторами или без — на выбор.

Гиперссылки

Инструмент для работы с гиперссылками (URL и Email) в ячейках листа Excel.

Динамический именованный диапазон

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

Диспетчер горячих клавиш

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

Заполнение пустых ячеек соседними значениями

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

Изменение размеров диапазона

Быстрое изменение размеров диапазона с сохранением всех данных. Например, диапазон 3 на 4 (12 ячеек) можно вывести в вариантах 2 на 6, 6 на 2, 1 на 12, 4 на 3 и т.д. Причем данные из исходного диапазона можно считывать по строчкам или по столбцам.

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

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

Инструменты сводных таблиц

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

Исправить UDF

Исправление путей к пользовательским функциям (User Defined Functions = UDF).

Исправление УФ-ада

Исправление месива из дубликатов правил условного форматирования с раздробленными диапазонами, возникающего при вставке-удалении строк и вырезании-копировании данных в таблице с УФ.

Календарь для ввода дат

Удобный календарик для быстрого ввода в ячейку любой даты.

Конвертация формул в значения

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

Копирование-вставка только видимых ячеек

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

Менеджер листов

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

Мини-калькулятор

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

Настройка точности

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

Нормализация (Редизайн кросс-таблицы в плоскую)

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

Обратный порядок значений

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

Объединение ячеек с сохранением текста

Ячейки выделенного диапазона объединяются, при этом текст из всех ячеек сливается в одно целое с заданным символом-разделителем.

Операции с текстом

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

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

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

Отправить текущий лист по почте

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

Очистка диапазона ячеек

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

Очистка книги

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

Парная подсветка дубликатов

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

Перехват ошибок в формулах

Добавляет ко всем формулам функцию ЕСЛИОШИБКА (IFERROR) для перехвата ошибок и замены их на желаемое значение (ноль, пусто и т.д.)

Переход к источнику

Быстрый переход по первой ссылке в формуле текущей ячейки

Подсветка/Удаление повторов внутри ячеек

Удаляет или выделяет цветом повторяющиеся фрагменты текста внутри ячеек.

Пользовательские стили

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

Поменять местами

Быстро поменять местами два любых выделенных столбца/строки/диапазона на листе Excel.

Построение микрографиков

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

Почтовая рассылка

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

Прицел

Подсветка текущей строки и столбца для наглядности при работе с большой таблицей.

Разгруппировать в столбцы

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

Разделение объединенных ячеек с сохранением текста

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

Разделение таблицы по разным листам

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

Разорвать/Открыть связи с другими книгами

Управление внешними связями книги: разрыв связей (замена ссылок на значения), открытие внешних файлов-источников (для обновления), работа с гиперссылками.

Режим ссылок A1 — R1C1

Переключение между режимами ссылок в A1 и R1C1 для текущей книги, т.е. с обычного (столбцы листа обозначаются буквами A,B,C…) на альтернативный режим R1C1 (столбцы листа обозначаются цифрами) и обратно.

Сборка листов из нескольких книг

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

Свёртка

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

Новая функция «LET» в Excel может упростить ваши формулы

22 августа 2020 г.

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

Основы LET

Программисты «старой школы», без сомнения, помнят дни объявления переменных в компьютерных программах с помощью функции LET. При написании кода программисты могут включать такое утверждение, как «LET x = 100». Установив « x» как значение « 100 », они затем использовали эту переменную в других расчетах. Новая функция LET в Excel обеспечивает практически те же функции, но с одним заметным отличием. С помощью LET вы можете использовать только ту переменную, которую объявляете, в той же формуле, которая содержит функцию LET.

В качестве упрощенного первого примера работы с оператором LET рассмотрим следующую формулу в Excel.

=LET(x, 100, SUM(x, 1))

Формула, показанная первой, устанавливает « x » как переменную со значением « 100 ». Затем он добавляет это значение к «1», чтобы получить вычисленное значение «101». Представленный фундаментальный пример — это просто пример для введения LET. Давайте обратим наше внимание на некоторые более практические применения LET. Но сначала давайте опишем, почему LET потенциально полезен для пользователей Excel.

Преимущества LET

По данным Microsoft, LET предлагает два основных преимущества: 1) улучшенная производительность и 2) простота чтения и компоновки. Более конкретно:

1. Улучшенная производительность. Если вы впишите одно и то же выражение в формулу несколько раз, Excel вычислит этот результат несколько раз. LET позволяет вызывать выражение по имени и для Excel вычислять его один раз.
2. Легкое чтение и композиция. Больше не нужно запоминать, на что ссылается конкретная ссылка на диапазон/ячейку, что делает ваш расчет или копировать/вставлять одно и то же выражение. Имея возможность объявлять и называть переменные, вы можете дать значимый контекст себе и потребителям вашей формулы.

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

Практический пример того, как функция LET может упростить ваши формулы

На этом рисунке предположим, что кредитный инспектор решает, одобрять ли ипотеку на дом. Кроме того, политика банка заключается в том, чтобы взимать проценты на 0,5% больше, если кредитный рейтинг заявителя меньше 800. В этом случае мы можем использовать следующую формулу для расчета ежемесячного платежа на основе входных данных, показанных в 9.0008 Рисунок 1 .

=LET(Ставка,(IF(B4>799,B2,B2+0,005)),PMT(Ставка/12,B3*12,-B1))

a Credit Score

В приведенной выше формуле функция LET объявляет переменную с именем «Rate». Кроме того, функция LET использует вложенную функцию ЕСЛИ для извлечения значения из ячейки B4 и определения, меньше ли оно 800. Если это так, процентная ставка увеличивается на 0,5%. Наконец, формула рассчитывает ежемесячный платеж, включая переменную «Ставка», установленную функцией LET. В этом примере использование LET облегчает создание более кратких вычислений, чем это было бы возможно в противном случае.

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

Сводка

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

Facebook

Twitter

Узнайте больше о LET от Microsoft по номеру , нажав здесь .  

Хотите узнать больше об Excel? Рассмотрите возможность проведения обучающего мероприятия K2 Enterprises! Щелкните здесь , чтобы узнать больше о доступных вам вариантах.

Узнайте больше о LET в этом видео

Томми Стивенс

Томми является одним из акционеров K2 Enterprises (www.k2e.com), присоединился к фирме в 2003 году и стал акционером в 2007 году. В K2 Томми занимается созданием и доставкой контента и отвечает за многие из управленческих и маркетинговых функций фирмы. Вы можете связаться с ним по адресу [email protected].

Итерация в Excel с поиском цели

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

Вы можете найти инструмент поиска цели в разделе «Анализ возможных вариантов» на вкладке «Данные»:

Содержание

• Итерация в Excel с поиском цели
• Поиск корня уравнения с итерацией Excel
• Итерационные вычисления в Excel
• Решение неявных уравнений в Excel
• Поиск цели и включение итеративного вычисления
• Word of C Aution

Итерация в Excel с поиском цели

Самый очевидный способ использования поиска цели — это именно то, что было задумано Microsoft: найти входное значение, которое дает целевой результат. Это позволяет нам «поиграть» с нашими моделями и попробовать разные варианты без необходимости переставлять уравнения или рабочий лист.

1. Открыть поиск цели
2. Выберите ячейку, с помощью которой вы хотите достичь определенной цели, во вводе «установить ячейку».
3. Введите целевое значение, которого вы хотите достичь («до значения»).
4. Укажите ячейку, которую вы хотите изменить для достижения результата, или «изменив ячейку».
5. Выберите ОК

Поиск цели повторяет ввод для достижения цели.

Вы изо всех сил пытаетесь найти правильные решения ваших технических проблем в Excel?

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

Поиск корня уравнения с помощью итерации Excel

Одним из конкретных применений поиска целевого результата является использование поиска цели для поиска корня (или корней) уравнения. В этом случае целевое значение равно просто 0.

Итерационные вычисления в Excel

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

Решение неявных уравнений в Excel

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

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

Поиск цели и включение итерационных вычислений

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

Поиск цели лучше, чем этот вариант, поскольку он выполняет итерацию более контролируемым образом.

Предупреждение

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