Преобразование формул в значения
26659 10.01.2015 Скачать пример
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из «калькулятора-переростка» в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:
- Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
- Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
- Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.
- Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании «сползут» все ссылки в формулах.
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
- Выделите диапазон с формулами, которые нужно заменить на значения.
- Скопируйте его правой кнопкой мыши – Копировать (Copy).
- Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :
либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:
Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.
В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
- Копируем выделенный диапазон Ctrl+C
- Тут же вставляем обратно сочетанием Ctrl+V
- Жмём Ctrl, чтобы вызвать меню вариантов вставки
- Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
- Выделяем диапазон с формулами на листе
- Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
- В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно).
Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
Dim smallrng As Range
For Each smallrng In Selection.Areas
smallrng.Value = smallrng.Value
Next smallrng
End Sub
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
For Each ws In ActiveWorkbook.
Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
End Sub
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще — установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
В этом случае:
- всё будет максимально быстро и просто
- можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
- в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
- любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX
Ссылки по теме
- Что такое макросы, как их использовать, копировать и запускать
- Как скопировать формулы без сдвига ссылок
- Как считать в Excel без формул
Замена результата формулы на значение в Excel — TutorExcel.
RuРазберем как быстро сделать и получить результат формулы в виде значения (в любом: текстовом, числовом и прочих вариантах) в Excel.
Приветствую всех, уважаемые читатели блога TutorExcel.Ru!
Сегодня поговорим о небольшом полезном приеме, или выражаясь более современным языком, лайфхаке, который время от времени может существенно упростить нам работу по анализу формул и поиску ошибок в них.
На самом деле это очень простой инструмент, который позволяет нам быстро заменить часть формулы (или полную формулу) на ее значение.
В Excel мы довольно часто пишем большие и объемные формулы, состоящие из нескольких отдельных логических частей.
И далеко не всегда мы сразу понимаем насколько формула корректно написана и в этот момент перед нами встает задача действительно ли это то, что мы хотели получить и возможно есть необходимость посмотреть как посчитаны отдельные части формулы.
Давайте перейдем к примеру.
Перед нами небольшая таблица и формула состоящая из нескольких подформул.
Для удобства записи будем переводить число в тысячи и миллионы, с помощью функции ЕСЛИ. В данном случае сложность формулы из примера не имеет никакого значения, главное понять суть приема и чем он может быть полезен в работе.
Разберем 2 основных инструмента позволяющих более детально разобраться в вычислении формулы.
Первый из них лучше подходит если мы хотим разобраться в каждой из составляющих частей формулы, а второй — в конкретной части.
Пошаговое вычисление формулы
В Excel есть стандартный инструмент пошагового вычисления формулы.
Для этого встаем в ячейку с формулой и в панели вкладок выбираем вкладку Формулы, а далее в блоке Зависимости формул выбираем Вычислить формулу.
Во всплывающем диалоговом окне мы можем увидеть как пошагово вычисляется формула, и шаг за шагом нажимая кнопку Вычислить мы будем видеть все промежуточные вычисления.
При этом вычисления идут последовательно, другими словами, Excel читает формулу слева направо с учетом вложенности функций. Поэтому этот вариант отлично подойдет если нужно пройти формулу от самого начала и до самого конца, и понять всю логику вычисления.
Часто это бывает нужно если мы смотрим какие-либо «чужие» формулы, которые написаны не нами, и необходимо в них разобраться, чтобы понять что именно они делают.
Особо подробно останавливаться на этом инструменте не буду, практически уверен, что с ним многие из вас знакомы и он в целом достаточно интуитивно понятный.
Поэтому перейдем к еще одному способу детализации формулы.
Замена части формулы на ее значение
Если нам нужно посмотреть вычисления какой-то конкретной части, то предыдущий вариант уже не так удобен.
В этом случае нам сможет помочь другой прием.
Давайте зайдем в режим редактирования формулы (можем нажать F2 в активной ячейке с формулой) и будем работать непосредственно в строке формул. Все что нам теперь нужно — это выделить (мышкой или клавиатурой) ту часть формулы, по которой мы хотим посмотреть промежуточный результат и нажать F9.
Вот и все 🙂
В результате прямо в строке формул часть формулы заменилась на результат ее вычисления. Далее мы можем продолжить этот процесс и выделить еще одну часть формулы и смотреть что из этого получается. Таким образом, мы можем понять насколько правильно сработала та или иная часть формулы буквально всего одним действием.
Два важных момента.
Во-первых, выделять нужно только логически полную формулу, иначе Excel не поймет как ее считать. Условно говоря, выделить нужно такую формулу, чтобы ее можно было вставить в отдельную ячейку и она бы корректно сработала.
Во-вторых, заменяя часть формулы на результат ее вычисления мы меняем саму формулу.
Поэтому если вы просто хотите посмотреть как работает формула, то в конце не забудьте вернуть формулу в изначальный вид. Обычно процесс выглядит как просмотр частей формулы через F9 в режиме редактирования, а затем в конце с помощью Esc выходим из режима редактирования и формула возвращается в исходный вид.
Спасибо за внимание!
Если у вас есть вопросы и мысли по теме статьи — делитесь ими в комментариях.
Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!
Поделиться с друзьями:
Поиск по сайту:
Формулы
| Новые функции | |||
| XLOOKUP | Заменяет VLOOKUP, HLOOKUP и INDEX / MATCH | XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode]) | |
| Логический | да | ||
| И | Проверяет выполнение всех условий. ИСТИНА/ЛОЖЬ | И(лог1,лог2) | |
| IF | Если условие выполнено, сделайте что-нибудь, если нет, сделайте что-нибудь еще. | ЕСЛИ(логическая_проверка,значение_если_истина,значение_если_ложь) | |
| ЕСЛИОШИБКА | Если результатом является ошибка, сделайте что-нибудь другое. | ЕСЛИОШИБКА(ЗНАЧЕНИЕ,значение_если_ошибка) | |
| НЕ | Изменяет ИСТИНА на ЛОЖЬ и ЛОЖЬ на ИСТИНА. | НЕ(логическое) | |
| ИЛИ | Проверяет, выполняются ли какие-либо условия. ИСТИНА/ЛОЖЬ | ИЛИ(лог1,лог2) | |
| XOR | Проверяет, выполняется ли одно и только одно условие. ИСТИНА/ЛОЖЬ | Исключающее ИЛИ(лог1,лог2) | |
| Поиск и ссылка | да | ||
| ЛОЖЬ | Логическое значение: ЛОЖЬ. | ЛОЖЬ | |
| ИСТИНА | Логическое значение: ИСТИНА. | ИСТИНА | |
| АДРЕС | Возвращает адрес ячейки в виде текста. | АДРЕС(номер_строки,номер_столбца,номер_абс,C1,текст_листа) | |
| ОБЛАСТИ | Возвращает количество областей в ссылке. | ОБЛАСТИ (ссылка) | |
| ВЫБОР | Выбирает значение из списка на основе его номера позиции. | ВЫБОР(номер_индекса,значение1,значение2) | |
| КОЛОННА | Возвращает номер столбца ссылки на ячейку. | КОЛОННА (ссылка) | |
| СТОЛБЦЫ | Возвращает количество столбцов в массиве. | СТОЛБЦЫ (массив) | |
| HLOOKUP | Поиск значения в первой строке и возврат значения. | HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) | |
| ГИПЕРССЫЛКА | Создает интерактивную ссылку. | ГИПЕРССЫЛКА(link_location,friendly_name) | |
| ИНДЕКС | Возвращает значение на основе номеров столбца и строки. | ИНДЕКС(массив,номер_строки,номер_столбца) | |
| НЕПРЯМАЯ | Создает ссылку на ячейку из текста. | НЕПРЯМАЯ(ref_text,C1) | |
| ПРОСМОТР | Поиск значений по горизонтали или по вертикали. | ПРОСМОТР(искомое_значение,искомый_вектор,результирующий_вектор) | |
| ПОИСКПОЗ | Ищет значение в списке и возвращает его позицию. | ПОИСКПОЗ (искомое_значение, искомый_массив, тип_соответствия) | |
| СМЕЩЕНИЕ | Создает смещение привязки от начальной точки. | СМЕЩЕНИЕ(ссылка,строки,столбцы,высота,ширина) | |
| ROW | Возвращает номер строки ссылки на ячейку. | РЯД (ссылка) | |
| ROWS | Возвращает количество строк в массиве. | РЯДЫ (массив) | |
| ТРАНСП | Меняет ориентацию диапазона ячеек. | ТРАНСП(массив) | |
| ВПР | Поиск значения в первом столбце и возврат значения. | ВПР(искомое_значение,массив_таблиц,номер_индекса_столбца,диапазон_просмотра) | |
| Дата и время | да | ||
| ДАТА | Возвращает дату из года, месяца и дня. | ДАТА(год,месяц,день) | |
| DATEDIF | Количество дней, месяцев или лет между двумя датами. | РАЗНДАТ | |
| DATEVALUE | Преобразует дату, сохраненную в виде текста, в действительную дату | DATEVALUE(date_text) | |
| ДЕНЬ | Возвращает день в виде числа (1-31). | ДЕНЬ(серийный_номер) | |
| ДНЕЙ | Возвращает количество дней между двумя датами. | ДНЕЙ(дата_конца,дата_начала) | |
| DAYS360 | Возвращает количество дней между двумя датами в году из 360 дней. | ДНЕЙ360(дата_начала,дата_окончания,метод) | |
| EDATE | Возвращает дату, отстоящую от начальной даты на n месяцев. | ЭДАТА(дата_начала,месяцы) | |
| КОНМЕСЯЦ | Возвращает последний день месяца, n месяцев до даты. | КОНМЕСЯЦА(дата_начала,месяцы) | |
| ЧАС | Возвращает час в виде числа (0-23). | ЧАС(серийный_номер) | |
| МИНУТЫ | Возвращает минуты в виде числа (0-59). | МИНУТА(серийный_номер) | |
| МЕСЯЦ | Возвращает месяц в виде числа (1-12). | МЕСЯЦ(серийный_номер) | |
| ЧИСТРАБДНИ | Количество рабочих дней между двумя датами. | ЧИСТРАБДНИ(дата_начала,дата_окончания,праздники) | |
| NETWORKDAYS.INTL | Рабочие дни между двумя датами, настраиваемые выходные. | ЧИСТРАБДНИ.МЕЖД(дата_начала,дата_окончания,выходные,праздники) | |
| СЕЙЧАС | Возвращает текущую дату и время. | СЕЙЧАС() | |
| ВТОРАЯ | Возвращает секунды в виде числа (0-59) | SECOND(serial_number) | |
| ВРЕМЯ | Возвращает время из часов, минут и секунд. | ВРЕМЯ(час,минута,секунда) | |
| TIMEVALUE | Преобразует время, сохраненное в виде текста, в действительное время. | ВРЕМЗНАЧ(время_текст) | |
| СЕГОДНЯ | Возвращает текущую дату. | СЕГОДНЯ() | |
| ДЕНЬ НЕД | Возвращает день недели в виде числа (1-7). | ДЕНЬ НЕДЕЛИ(serial_number,return_type) | |
| WEEKNUM | Возвращает номер недели в году (1-52). | НОМЕР НЕДЕЛИ (серийный_номер, тип_возврата) | |
| РАБДЕНЬ | Дата n рабочих дней от даты. | РАБДЕНЬ(дата_начала,дни,праздники) | |
| WORKDAY.INTL | Дата n рабочих дней от даты, пользовательские выходные. | РАБДЕНЬ.МЕЖД(дата_начала,дни,выходные,праздники) | |
| ГОД | Возвращает год. | ГОД(серийный_номер) | |
| YEARFRAC | Возвращает долю года между двумя датами. | YEARFRAC(start_date,end_date,basic) | |
| Машиностроение | да | ||
| ПРЕОБРАЗОВАТЬ | Преобразование числа из одной единицы в другую. | ПРЕОБРАЗОВАТЬ(число,из_единицы,в_единицу) | |
| Финансовый | да | ||
| FV | Расчет будущей стоимости. | БС(коэффициент,кпер,тыс.т,пс,тип) | |
| PV | Вычисляет текущую стоимость. | PV(ставка,кпер,тысяча на тонну,б.с.,тип) | |
| КПЕР | Вычисляет общее количество периодов платежей. | КПЕР(ставка,pmt,pv,fv,тип) | |
| PMT | Расчет суммы платежа. | PMT(коэффициент,кпер,pv,fv,тип) | |
| СТАВКА | Расчет процентной ставки. | СТАВКА(nper,pmt,pv,fv,тип,предположение) | |
| NPV | Расчет чистой приведенной стоимости. | NPV(ставка,значение1,значение2) | |
| IRR | Внутренняя норма доходности для набора периодических CF. | IRR(значения,предположения) | |
| XIRR | Внутренняя норма доходности для набора непериодических КФ. | ЧИСТНДОХД(значения,даты,предположение) | |
| ЦЕНА | Рассчитывает цену облигации. | ЦЕНА(расчет,срок,ставка,лет,погашение,ЧАСТОТА,база) | |
| ДОХОД | Рассчитывает доходность облигации. | ДОХОД(расчет,срок,ставка,процент,погашение,ЧАСТОТА,база) | |
| INTRATE | Процентная ставка полностью инвестированной ценной бумаги. | ВНУТРЕННЯЯ (расчет, срок погашения, инвестиции, погашение, база) | |
| Информация | да | ||
| CELL | Возвращает информацию о ячейке. | ЯЧЕЙКА (тип_информации, ссылка) | |
ERROR. TYPE | Возвращает значение, представляющее ошибку ячейки. | ТИП ОШИБКИ(значение_ошибки) | |
| IПУСТО | Проверить, пуста ли ячейка. ИСТИНА/ЛОЖЬ | ЕПУСТО (ЗНАЧЕНИЕ) | |
| ISERR | Проверяет, является ли значение ячейки ошибкой, игнорирует #N/A. ИСТИНА/ЛОЖЬ | ЕОШИБКА(ЗНАЧЕНИЕ) | |
| ЕОШИБКА | Проверить, является ли значение ячейки ошибкой. ИСТИНА/ЛОЖЬ | ОШИБКА(ЗНАЧЕНИЕ) | |
| ISEVEN | Проверка четности значения ячейки. ИСТИНА/ЛОЖЬ | ISEVEN(число) | |
| ISFORMULA | Проверить, является ли ячейка формулой. ИСТИНА/ЛОЖЬ | ИФОРМУЛА (ссылка) | |
| НЕЛОГИЧНОСТЬ | Проверка логичности ячейки (ИСТИНА или ЛОЖЬ). ИСТИНА/ЛОЖЬ | НЕЛОГИЧНОЕ(ЗНАЧЕНИЕ) | |
| ISNA | Проверить, является ли значение ячейки #N/A. ИСТИНА/ЛОЖЬ | ISNA(ЗНАЧЕНИЕ) | |
| ISNONTEXT | Проверить, не является ли ячейка текстом (пустые ячейки не являются текстом). ИСТИНА/ЛОЖЬ | НЕТЕКСТ(ЗНАЧЕНИЕ) | |
| ISNUMBER | Проверить, является ли ячейка числом. ИСТИНА/ЛОЖЬ | IНОМЕР(ЗНАЧЕНИЕ) | |
| ISODD | Проверить, является ли значение ячейки нечетным. ИСТИНА/ЛОЖЬ | ISODD(число) | |
| ISREF | Проверить, является ли значение ячейки ссылкой. ИСТИНА/ЛОЖЬ | ISREF(ЗНАЧЕНИЕ) | |
| ISTEXT | Проверить, является ли ячейка текстовой. ИСТИНА/ЛОЖЬ | ИСТЕКСТ(ЗНАЧ) | |
| N | Преобразует значение в число. | N(ЗНАЧЕНИЕ) | |
| NA | Возвращает ошибку: #N/A. | нет данных() | |
| TYPE | Возвращает тип значения в ячейке. | ТИП(ЗНАЧЕНИЕ) | |
| Математика | да | ||
| ABS | Вычисляет абсолютное значение числа. | АБС(номер) | |
| АГРЕГАТ | Определение и выполнение вычислений для базы данных или списка. | АГРЕГАТ(номер_функции,параметры,массив,k) | |
| CEILING | Округляет число до ближайшего указанного кратного. | ПОТОЛОК(номер,значение) | |
| COS | Возвращает косинус угла. | COS(номер) | |
| ГРАДУСЫ | Преобразует радианы в градусы. | ГРАДУСЫ(угол) | |
| DSUM | Суммирует записи базы данных, соответствующие определенным критериям. | DSUM(база данных,поле,критерий) | |
| ЧЕТНЫЙ | Округляет до ближайшего четного целого числа. | ЧЕТНЫЙ(число) | |
| EXP | Вычисляет экспоненциальное значение для заданного числа. | EXP(число) | |
| ФАКТ | Возвращает факториал. | ФАКТ(номер) | |
| ЭТАЖ | Округляет число в меньшую сторону до ближайшего указанного кратного. | ЭТАЖ(номер,значение) | |
| НОД | Возвращает наибольший общий делитель. | НОД(число1,число2) | |
| INT | Округляет число в меньшую сторону до ближайшего целого числа. | INT(число) | |
| LCM | Возвращает наименьшее общее кратное. | LCM(номер1,номер2) | |
| LN | Возвращает натуральный логарифм числа. | ЛН(номер) | |
| LOG | Возвращает логарифм числа по указанному основанию. | LOG(номер,база) | |
| LOG10 | Возвращает логарифм числа по основанию 10. | LOG10(число) | |
| MOD | Возвращает остаток после деления. | MOD(число,делитель) | |
| MROUND | Округляет число до указанного кратного. | КРУГЛЫЙ(число,несколько) | |
| НЕЧЕТНОЕ | Округляет до ближайшего нечетного целого числа. | НЕЧЕТНЫЙ (число) | |
| PI | Значение PI. | ПИ() | |
| POWER | Вычисляет число, возведенное в степень. | МОЩНОСТЬ(число,мощность) | |
| ПРОИЗВЕД | Умножает массив чисел. | ПРОДУКТ(номер1,номер2) | |
| ЧАСТНОЕ | Возвращает целочисленный результат деления. | ЧАСТНОЕ(числитель,знаменатель) | |
| РАДИАНЫ | Преобразует угол в радианы. | РАДИАН(угол) | |
| СЛУЧАЙ | Вычисляет случайное число от 0 до 1. | СЛУЧАЙ() | |
| СЛУЧМЕЖДУ | Вычисляет случайное число между двумя числами. | СЛУЧАЙ МЕЖДУ(нижний,верхний) | |
| ОКРУГЛ | Округляет число до указанного количества цифр. | ОКРУГЛ(число,число_цифр) | |
| ОКРУГЛ ВНИЗ | Округляет число в меньшую сторону (к нулю). | ОКРУГЛВНИЗ(число,число_разрядов) | |
| ОКРУГЛВВЕРХ | Округляет число в большую сторону (от нуля). | ОКРУГЛВВЕРХ(число,число_разрядов) | |
| ЗНАК | Возвращает знак числа. | ЗНАК(номер) | |
| SIN | Возвращает синус угла. | SIN(номер) | |
| SQRT | Вычисляет квадратный корень из числа. | SQRT(номер) | |
| ПРОМЕЖУТОЧНЫЙ ИТОГ | Возвращает сводную статистику для ряда данных. | ПРОМЕЖУТОЧНЫЙ ИТОГ(номер_функции,REh2) | |
| СУММ | Складывает числа. | СУММ(число1,число2) | |
| СУММЕСЛИ | Суммирует числа, соответствующие критерию. | СУММЕСЛИ(диапазон,критерий,сумма_диапазон) | |
| СУММЕСЛИМН | Суммирует числа, соответствующие нескольким критериям. | СУММЕСЛИМН(сумма_диапазон,критерий_диапазон,критерий) | |
| СУММПРОИЗВ | Умножает массивы чисел и суммирует полученный массив. | СУММПРОИЗВ(массив1,массив2,массив3) | |
| TAN | Возвращает тангенс угла. | TAN(номер) | |
| TRUNC | Усекает число до определенного количества цифр. | TRUNC(число,число_цифр) | |
| Статистика | да | ||
| СРЕДНИЙ | Средние числа. | СРЕДНЕЕ(число1,число2) | |
| AVERAGEA | Средние числа. Включает текст и FALSE = 0, TRUE = 1. | СРЗНАЧА(значение1,значение2) | |
| СРЗНАЧЕСЛИ | Усредняет числа, соответствующие критерию. | СРЗНАЧЕСЛИ(диапазон,критерий,средний_диапазон) | |
| AVERAGEIFS | Усредняет числа, соответствующие нескольким критериям. | СРЗНАЧЕСЛИМН(средний_диапазон,критерий_диапазон,критерий) | |
| КОРРЕЛ | Вычисляет корреляцию двух рядов. | КОРРЕЛ(массив1,массив2) | |
| COUNT | Подсчет ячеек, содержащих число. | СЧЕТ(значение1,значение2) | |
| COUNTA | Подсчет непустых ячеек. | СЧЁТ(значение1,значение2) | |
| COUNTB004 COUNTB005 | Подсчет пустых ячеек. | СЧИТАТЬ ПУСТО (диапазон) | |
| СЧЁТЕСЛИ | Подсчитывает ячейки, соответствующие критерию. | СЧЁТЕСЛИ(диапазон,критерий) | |
| СЧЁТЕСЛИМН | Подсчитывает ячейки, соответствующие нескольким критериям. | СЧЁТЕСЛИМН(диапазон_критериев,критерий) | |
| ПРОГНОЗ | Прогноз будущих значений y на основе линейной линии тренда. | ПРОГНОЗ(x,известные_y,известные_x) | |
| ЧАСТОТА | Подсчитывает значения, попадающие в указанные диапазоны. | ЧАСТОТА(массив_данных,массив_бинов) | |
| РОСТ | Вычисляет значения Y на основе экспоненциального роста. | РОСТ(известный_ys,известный_x,новый_x,const) | |
| INTERCEPT | Вычисляет точку пересечения Y для линии наилучшего соответствия. | ПЕРЕХВАТ (известный_ys,известный_xs) | |
| НАИБОЛЬШИЙ | Возвращает k-е наибольшее значение. | БОЛЬШОЙ(массив,k) | |
| ЛИНЕЙН | Возвращает статистику по линии тренда. | ЛИНЕЙН(известный_ys,известный_xs,константа,статистика) | |
| MAX | Возвращает наибольшее число. | МАКС(число1,число2) | |
| МЕДИАНА | Возвращает медианное число. | МЕДИАНА(число1,число2) | |
| МИН | Возвращает наименьшее число. | МИН(число1,число2) | |
| РЕЖИМ | Возвращает наиболее распространенный номер. | РЕЖИМ(номер1,номер2) | |
| ПРОЦЕНТИЛЬ | Возвращает k-й процентиль. | ПРОЦЕНТИЛЬ(массив,k) | |
| PERCENTILE.INC | Возвращает k-й процентиль. Где k включительно. | ПРОЦЕНТИЛЬ.ВКЛ(массив,k) | |
| PERCENTILE.EXC | Возвращает k-й процентиль. Где k является исключительным. | ПРОЦЕНТИЛЬ.ИСКЛ(массив,k) | |
| КВАРТИЛЬ | Возвращает указанное значение квартиля. | КВАРТИЛЬ(массив,квартиль) | |
| QUARTILE.INC | Возвращает указанное значение квартиля. включительно. | КВАРТИЛЬ.ВКЛ(массив,кварта) | |
| КВАРТИЛЬ.ИСКЛ | Возвращает указанное значение квартиля. Эксклюзив. | КВАРТИЛЬ.ИСКЛ(массив,кварта) | |
| RANK | Ранг числа в серии. | РАНГ(номер,ссылка,порядок) | |
| RANK.AVG | Ранг числа в серии. Средние. | RANK.AVG(номер,ссылка,порядок) | |
| RANK.EQ | Ранг числа в серии. Высший ранг. | RANK.EQ(номер,ссылка,порядок) | |
| НАКЛОН | Вычисляет наклон на основе линейной регрессии. | НАКЛОН(известный_ys,известный_xs) | |
| МАЛЕНЬКИЙ | Возвращает k-е наименьшее значение. | МАЛЕНЬКИЙ(массив,k) | |
| СТАНДОТКЛОН | Вычисляет стандартное отклонение. | СТАНДОТКЛОН(номер1,номер2) | |
| STDEV.P | Вычисляет стандартное отклонение всей совокупности. | СТАНДОТКЛОН.П(число1,число2) | |
| STDEV.S | Вычисляет SD образца. | СТАНДОТКЛ.С(номер1,номер2) | |
| СТАНДОТКЛОН | Вычисляет стандартное отклонение всей совокупности | СТАНДОТКЛОН(число1,число2) | |
| TREND | Вычисляет значения Y на основе линии тренда. | TREND(известный_ys,известный_xs,новый_xs,постоянный) | |
| Текст | да | ||
| CHAR | Возвращает символ, заданный кодом. | СИМВОЛ(число) | |
| CLEAN | Удаляет все непечатаемые символы. | ОЧИСТКА(текст) | |
| КОД | Возвращает числовой код символа. | КОД(текст) | |
| СЦЕПИТЬ | Объединяет текст вместе. | СЦЕПИТЬ(текст1,текст2) | |
| ДОЛЛАР | Преобразует число в текст в денежном формате. | ДОЛЛАР(число,десятичные) | |
| EXACT | Проверить, равны ли ячейки. Деликатный случай. ИСТИНА/ЛОЖЬ | ТОЧНО(текст1,текст2) | |
| НАЙТИ | Находит положение текста в ячейке. С учетом регистра. | НАЙТИ(найти_текст,внутри_текста,начальный_номер) | |
| LEFT | Обрезает текст на несколько символов слева. | ЛЕВЫЙ(текст,число_знаков) | |
| LEN | Подсчитывает количество символов в тексте. | ДЛСТР(текст) | |
| LOWER | Преобразует текст в нижний регистр. | НИЖНИЙ (текст) | |
| MID | Извлекает текст из середины ячейки. | СРЕДНИЙ (текст, начальный_номер, число_знаков) | |
| PROPER | Преобразует текст в правильный регистр. | ПРАВИЛЬНЫЙ(текст) | |
| REPLACE | Заменяет текст в зависимости от его местоположения. | ЗАМЕНИТЬ (старый_текст, начальный_номер, число_символов, новый_текст) | |
| REPT | Повторяет текст несколько раз. | ПОВТОР(текст,количество_раз) | |
| ПРАВО | Усекает текст на несколько символов справа. | ПРАВО(текст,число_знаков) | |
| ПОИСК | Определяет положение текста в ячейке. Регистр не учитывается. | ПОИСК(найти_текст,внутри_текста,начальный_номер) | |
| ПОДСТАВИТЬ | Находит и заменяет текст. Деликатный случай. | ЗАМЕНИТЬ(текст,старый_текст,новый_текст,номер_экземпляра) | |
| ТЕКСТ | Преобразует значение в текст в определенном числовом формате. | ТЕКСТ(ЗНАЧЕНИЕ,format_text) | |
| ТРИМ | Удаляет все лишние пробелы из текста. | ОТДЕЛКА (текст) | |
| ПРОПИСНЫЕ | Преобразует текст в верхний регистр. | ВЕРХНИЙ (текст) | |
| ЗНАЧЕНИЕ | Преобразует число, сохраненное как текст, в число. | ЗНАЧЕНИЕ(текст) |
Формулы Excel: функции
Урок 5: функции
/en/excelformulas/relative-and-absolute-cell-references/content/
Введение
Функция представляет собой предопределенную формулу , которая выполняет вычисления с использованием определенных значений в определенном порядке. Все программы для работы с электронными таблицами включают общие функции, которые можно использовать для быстрого нахождения суммы , среднего , подсчета , максимального значения и минимального значения для диапазона ячеек. Чтобы правильно использовать функции, вам необходимо понимать различные частей функции 9.1791 и как создать аргументов для вычисления значений и ссылок на ячейки.
Посмотрите видео ниже, чтобы узнать больше об использовании функций в Excel.
Части функции
Для правильной работы функция должна быть записана особым образом, который называется синтаксисом . Базовый синтаксис функции — это знак равенства (=) , имя функции (например, SUM) и один или несколько аргументов 9.1791 . Аргументы содержат информацию, которую вы хотите вычислить. Функция в приведенном ниже примере добавит значения диапазона ячеек A1:A20.
Работа с аргументами
Аргументы могут относиться как к отдельным ячейкам , так и к диапазонам ячеек и должны быть заключены в скобок . Вы можете включить один аргумент или несколько аргументов, в зависимости от синтаксиса, необходимого для функции.
Например, функция =СРЗНАЧ(B1:B9) будет вычислять средних значений в диапазоне ячеек B1:B9.
Эта функция содержит только один аргумент.
Несколько аргументов должны быть разделены запятой . Например, функция =СУММ(A1:A3, C1:C2, E2) добавит значений всех ячеек в трех аргументах.
Использование функций
Существует множество функций. Вот некоторые из наиболее часто используемых функций:
- SUM : Эта функция складывает все значения ячеек в аргументе.
- СРЗНАЧ : Эта функция определяет среднее значений, включенных в аргумент. Он вычисляет сумму ячеек, а затем делит это значение на количество ячеек в аргументе.
- COUNT : Эта функция подсчитывает количество ячеек с числовыми данными в аргументе. Эта функция полезна для быстрого подсчета элементов в диапазоне ячеек.
- MAX : Эта функция определяет наибольшее значение ячейки , включенное в аргумент.
- MIN : Эта функция определяет наименьшее значение ячейки , включенное в аргумент.
Чтобы использовать функцию:
В приведенном ниже примере мы будем использовать базовую функцию для расчета средней цены за единицу для списка недавно заказанных товаров с использованием функции СРЗНАЧ.
- : Выберите ячейку , которая будет содержать функцию. В нашем примере мы выберем ячейку C11 .
- Введите знак равенства (=) и введите желаемое имя функции . В нашем примере мы введем =СРЕДНЕЕ .
- Введите диапазон ячеек для аргумента внутри круглых скобок . В нашем примере мы введем (C3:C10) . Эта формула добавит значения ячеек C3:C10, а затем разделит это значение на общее количество ячеек в диапазоне, чтобы определить среднее значение.
- Нажмите Введите на клавиатуре. Функция будет вычислена , а результат появится в ячейке. В нашем примере средняя цена за единицу заказанных товаров составила $15,93 .
Ваша электронная таблица не всегда сообщит вам , если ваша функция содержит ошибку, поэтому вы должны проверить все свои функции. Чтобы узнать, как это сделать, ознакомьтесь с уроком «Перепроверьте свои формулы».
Работа с незнакомыми функциями
Если вы хотите узнать, как работает функция, вы можете начать вводить эту функцию в пустую ячейку и посмотреть, что она делает.
Затем вы можете ввести открывающую скобку, чтобы увидеть какие аргументы ему нужны.
Понимание вложенных функций
Всякий раз, когда формула содержит функцию, эта функция обычно вычисляется до любых других операций, таких как умножение и деление.
Это связано с тем, что формула рассматривает всю функцию как одно значение — прежде чем использовать это значение в формуле, необходимо запустить функцию. Например, в приведенной ниже формуле функция СУММ будет вычисляться до деления:
Давайте рассмотрим более сложный пример, использующий несколько функций:
=РАБДЕНЬ(СЕГОДНЯ(),3)
Здесь у нас есть две разные функции, работающие вместе: функция РАБДЕНЬ и функция СЕГОДНЯ. Они известны как вложенных функций , поскольку одна функция помещается или вложенная в аргументы другой. Как правило, вложенная функция всегда вычисляется первой , точно так же, как скобки выполняются первыми в порядке операций. В этом примере функция СЕГОДНЯ будет вычисляться первой, поскольку она вложена в функцию РАБДЕНЬ.
Другие общие функции
Существует множество других функций , которые вы можете использовать для быстрого вычисления различных вещей с вашими данными.