Как установить в экселе формулу: Как заставить формулу всегда использовать одну и ту же ячейку в Excel?

Как сделать формулу в экселе разными способами?

Автор Дмитрий Костин Просмотров 1.4к. Опубликовано 15.02.2017 Обновлено 15.02.2017

Доброго всем времени суток, мои дорогие друзья и читатели моего блога. Занимаясь различными интернет-проектами, мне приходится вести свои таблицы по доходам, расходам, планам и т.д. И очень сильно в этом мне помогает табличный редактор excel, который входит в пакет Microsoft Office. Поэтому сегодня я вам хочу рассказать, как сделать формулу в экселе, чтобы облегчить себе выполнение многих задач.

Содержание

1. Основы
2. Математические функции
3. Простейшая формула
4. Сложение
5. Сложение с условием
6. Как сделать формулу с процентами?
7. Абсолютные и относительные формулы
8. Текстовые функции
9. Дата и время
10. Пример таблицы с применением формулы

Основы

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

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

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

Математические функции

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

Простейшая формула

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

Сложение

Допустим у вас есть некоторое количество чисел, которые вы хотите сложить. Все делается очень просто. Нажмите на ту ячейку, где вы хотите видеть сумму ваших чисел, после чего нажмите на кнопку «Математические» и из списка выберите СУММ.

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

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

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

Сложение с условием

А что, если вы хотите складывать все ячейки, а только те, которые, например меньше 20? Не будете же вы складывать каждое число в ячейке, если таких чисел 500? Вот здесь-то нам и поможет Сложение с условием.

Допустим, у нас есть много чисел, но нам надо сложить лишь те, которые меньше 20. Тогда мы выделяем как обычно пустую ячейку, и выбираем функцию СУММЕСЛИ. Встаем в первую строчку и выделяем весь диапазон ячеек. Затем встаем во вторую строчку и вставим нужный критерий, например «<20». Это и будет означать, что по формуле у нас выйдет сумма только тех ячеек, значение в которых меньше 20, а значит числа 42 и 43 не будут участвовать в этой сумме.

Точно также будут работать функции «ПРОИЗВЕД» и «ЧАСТНОЕ», только там уже, насколько вы поняли будет использоваться умножение и деление. Но функция ЧАСТНОЕ работает без остатка, поэтому, если с помощью нее вы попытаетесь разделить 5 на 3, то результат будет 1.

Оператора вычитания в экселе нет, но он в принципе и не нужен. Чтобы произвести вычитание, достаточно просто поставить обычный знак минус, например = B3-B2.

Давайте заодно посмотрим несколько других математических функций:

• КОРЕНЬ — Вычисляет квадратный корень из числа. Все, что вам нужно будет сделать — это выделить любую пустую ячейку, выбрать эту функцию, когда откроется новое окно, выбрать число, из которого вы будете извлекать корень.
• ЦЕЛОЕ — Округляет число, до ближайшего меньшего целого, т.е. если вы возьмете число 2,99, то функция округлит его до 2, а не до 3.
• ЧЕТН и НЕЧЕТ — Округляет любое число до ближайшего целого четного или нечетного числа в сторону увеличения
• Ну и множество других функций, которые вы сможете попробовать сами. Главное понять саму суть.

Как сделать формулу с процентами?

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

Допустим у нас есть общее число (200) в одном столбце и числа, из которых оно состоит состоит (35, 45, 50, 70) в другом столбце. Значит нам нужно встать в ячейку, где у нас будет отображаться результат, а затем выбрать процентный формат. Для этого можете нажать правой кнопкой мыши на этой ячейке и выбираем пункт «Формат ячеек». И уже во вновь открывшемся окне во вкладке «Число» выбрать процентный формат.

Теперь ставим знак равенства, нажимаем на ячейку меньшего числа (35), затем ставим знак деления (/) и нажимаем на ячейку общего числа (200). Ничего на 100 умножать не надо, так как мы уже поставили процентный формат. Значит просто нажимаем Enter. Вуаля. У нас получилось 17,5%. Точно также можно сделать и с остальными ячейками.

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

Это происходит из-за того, что результат берется относительно строк и столбцов, из которых мсы брали значения. То есть, если у нас был результат в ячейке C3 от деления A3 на B3, то потянув результат вниз, мы получим результат уже по другой формуле, например C4=A4/B4, C5=A5/B5. Все из-за относительности.

А для того, чтобы наша задумка сработала, нам нужно число 200 (наш итог) сделать постоянным. Для этого просто это значение нужно сделать абсолютным. Об этом читайте дальше.

Абсолютные и относительные формулы

Слышали вы про такие понятия, как абсолютные и относительные формулы. Если нет, то внимательно смотрите. Допустим у нас есть таблица с какими-то параметрами в несколько строк и два столбца. Давайте встаньте в пустую ячейку и напишите туда знак равенства. Затем сложите два первых значения из первой строки, так, чтобы у вас в формуле было прописано =A3+B3. У вас получится некий результат.

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

Давайте проделаем то же самое, только немного видоизменим B3, а именно проставим в формуле $B$3. Результат в ячейке останется абсолютно таким же. Но попробуйте теперь потянуть, как и в прошлом примере, за уголок. Вы заметили изменения? Все значения теперь другие. Всё потому, что мы сделали значение $B$3 абсолютным.

Это значит, что  как бы мы не спускались и тянули уголки, для второго слагаемого будет использоваться именно значение из неизменной ячейки B3. То есть она буд-то бы фиксируется и относительность остается, только у А3.  Такая функция очень пригодится для вычисления процентов, о которых я рассказывал выше.

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

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

Часто у вас бывает такое, что вы пишите какой-либо текст, а когда смотрите на монитор, то выясняется, что он был написан с зажатым CAPS LOCK? Так вот в Экселе есть такая формула, которая убирает прописные буквы, заменяя их строчными.

Для того, чтобы создать такую формулу, войдите в «Текстовые» и выберите функцию «СТРОЧН». Ну а дальше выделите ту ячейку, где у вас написан ваш текст заглавными буквами. Дальше жмем «ОК» и все готово! Текст в норме. То же самое можно делать и наоборот, но за это отвечает функция «ПРОПИСН».

Вот еще некоторые текстовые формулы, которые вам могут пригодится:

• РУБЛЬ — присваивает числу денежный формат, подставляя в конце фирменный знак российского рубля.
• СЖПРОБЕЛ — Удаляет лишние пробелы из текста, если вдруг случайно было поставлено два и более.
• ЮНИСИМВ — очень интересная функция, которая преобразует число в соответствующий ему символ Юникода.

Дата и время

Если честно, то я не знаю, где бы им найти толковое применение. Просто, если взять Функцию «День», то она будет отображать день относительно месяца. Например, если брать число 31, то и отобразится число 31, так как это максимальное число дней в месяце. Но если поставить число 32, то в итоге отобразится единица. Функция как бы переносит число на следующий месяц, типа не 32 января, а первое февраля.

Пример таблицы с применением формулы

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

Доходы:

• Adsense
• РСЯ
• Тизерная сеть
• Партнеки
• Итого за день
• Итого за месяц
• Всего по каждому типу рекламы

Расходы:

• Услуги копирайтера
• Покупка ссылок
• Домен+хостинг
• Разовые услуги фрилансеров

Общая прибыль

Средний ежесуточный доход

Что мы делаем дальше? Теперь мы начинаем вставлять нужные формулы в различные ячейки. выделяем ячейку следующую за «Итого за день» и нажимаем на значок Fx, где нам нужно будет выбрать СУММ, после чего выделяем всю строчку, начиная от Adsenese, и кончая Партнеркой. В итоге у нас будет автоматически высчитываться сумма за один день.

Но неужели теперь придется так проделывать еще 30 раз для каждой строки? Конечно нет. Мы задали форулу и теперь мы можем ее просто дублировать на остальные строки. Для этого выделите ячейку, к которой мы применили формулу, а затем тянете ее вниз за правый нижний уголок, пока не выделятся все 30 строк. В результате, теперь сумма за каждый день будет высчитываться отдельно относительно каждой строчке.

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

Нам нужно посчитать, сколько мы зарабатываем за месяц по каждому виду рекламы. Для этого выделяем весь столбец Adsense с 1 по 31 число, а затем идем во вкладку «Формулы» и нажимаем на значок «Автосумма». Ниже мы тут же увидим сумму по данному виду. Теперь, чтобы также рассчитать сумму за месяц по остальным видам рекламы, нам нужно просто точно также, как и в предыдущем случае, зажать мышкой на кончике ячейки, и протянуть ее по остальным значениям. Таким образом формула автоматически подставится и для других видов доходов.

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

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

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

Ну и ради интереса в отдельной ячейке будем высчитывать автоматически средний ежесуточный доход. Для этого мы с вами поставим курсор в любую ячейку и выберем оператор СРЗНАЧ, после чего выделим диапазон наших доходов с 1 по 31 число. Очень удобно.

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

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

С уважением, Дмитрий Костин.

Как удалить в ячейке формулу, оставив значения?

Хитрости »

12 Май 2011       Дмитрий       364257 просмотров

Иногда формула в ячейке нужна лишь на короткое время — только для получения результата. А затем уже не нужна ни сама формула, ни ячейки, на которые она ссылается. Но вот беда — если удалить ячейки, то формула «ломается», как это говорится. Т.е. в ячейке получается либо #ЗНАЧ! либо #ССЫЛКА!. Или Вам надо просто зафиксировать значение, полученное в результате работы формулы. Т.е. чтобы формулы там не было, а было только значение. Как ни странно, но делается это проще простого:

Выделяете ячейку/несколько ячеек с формулами — Копируете — Правая кнопка мыши — Специальная вставка — Значения

Вот и все. Больше формул нет.
Так же подобное можно сделать при помощи кода VBA:

Sub Formulas_To_Values()
    Selection.Value = Selection.Value
End Sub

Для использования кода переходим в редактор VBA(Alt+F11) —Insert —Module и вставляем приведенный код. Чтобы выполнить код надо на листе нажать сочетание клавиш Alt+F8, выбрать нужный код и нажать Выполнить
Данный код заменяет формулы в выделенном диапазоне на значения. Только необходимо учитывать, что выделенный диапазон должен быть неразрывным. Иначе код выдаст ошибку. А такой код

Sub All_Formulas_To_Values()
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

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

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

Sub All_Formulas_To_Values_In_All_Sheets()
    Dim wsSh As Worksheet
    For Each wsSh In Sheets
        wsSh.UsedRange.Value = wsSh.UsedRange.Value
    Next wsSh
End Sub

А этот код поможет заменить формулы на значения только в видимых ячейках(просматриваются выделенные ячейки):

Sub All_Formulas_To_Values_OnlyVisible()
    Dim rRng As Range, rArea As Range
    If Selection. Count = 1 Then
        Set rRng = ActiveCell
    Else
        Set rRng = Selection.SpecialCells(12)
    End If
    For Each rArea In rRng.Areas
        rArea.Value = rArea.Value
    Next rArea
End Sub

КАК БЫСТРО ВЫЗЫВАТЬ:
Вообще данную команду можно вывести на Быструю панель и использовать в одно нажатие тогда, когда нужно.

• 2010 Excel Файл— Параметры— Настройка. Выбрать команды из: Все команды. Находите команду Вставить значения и добавляете;
• 2007 Excel Меню— Параметры Excel— Настройка. Выбрать команды из: Все команды. Находите команду Вставить значения и добавляете;
• 2003 Excel: Сервис— Настройка— вкладка Команды— категория Правка— Вставить значения.

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

ДОБАВИТЬ В КОНТЕКСТНОЕ МЕНЮ ЯЧЕЕК:
Если один раз выполнить код:

Sub Add_PasteSpecials()
    Dim cbb
    Set cbb = Application.CommandBars("Cell").FindControl(ID:=370)
    If Not cbb Is Nothing Then cbb.Delete 'удаляем пункт, если он был уже добавлен ранее
    Application.CommandBars("Cell").Controls.Add ID:=370, before:=4
End Sub

то в контекстном меню мыши появится команда — «Вставить значения». Она полностью дублирует стандартную, только вызвать её быстрее — одно нажатие и все.

Также см.:
Вставить значения
Формулы перестали считать — как исправить?

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам

Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика

Сохранение общих формул (Microsoft Excel)

Обратите внимание:
Эта статья написана для пользователей следующих версий Microsoft Excel: 2007, 2010, 2013, 2016, 2019 и Excel в Microsoft 365. Если вы используете более раннюю версию (Excel 2003 или более раннюю), этот совет может не работать для ты . Чтобы просмотреть версию этого совета, написанную специально для более ранних версий Excel, щелкните здесь: Сохранение общих формул.

Автор: Allen Wyatt (последнее обновление: 5 октября 2019 г.)
Этот совет относится к Excel 2007, 2010, 2013, 2016, 2019 и Excel в Microsoft 365

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

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

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

1. Введите формулу, как обычно.
2. Выберите ячейку, содержащую формулу, и нажмите F2 . Это переводит Excel в режим редактирования.
3. Удерживая нажатой клавишу Shift , используйте клавиши управления курсором для выбора всей формулы, включая знак равенства в самом начале.
4. Нажмите Ctrl+C . Теперь формула находится в буфере обмена.
5. Нажмите Esc . Теперь вы должны выйти из режима редактирования, а ячейка с формулой все еще выделена.
6. Убедитесь, что на ленте выбрана вкладка «Формулы».
7. В области «Определенные имена» выберите параметр «Определить имя». Excel отображает диалоговое окно «Новое имя». (См. рис. 1.)

Рисунок 1. Диалоговое окно «Новое имя».

8. В поле Имя введите имя, которое вы хотите присвоить этой формуле.
9. Выберите все, что находится в поле Относится к нижней части диалогового окна, и нажмите Ctrl+V . Ссылка на ячейку заменяется формулой, скопированной в буфер обмена на шаге 4.
10. Убедитесь, что в формуле нет знаков доллара. Если они есть, выберите их и удалите. (Этот метод использования формул плохо работает с абсолютными ссылками.)
11. Нажмите кнопку ОК.

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

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

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

1. Запустите Excel с новой книгой.
2. Отображение вкладки «Вид» на ленте.
3. Щелкните стрелку вниз под инструментом «Макросы» и выберите «Запись макроса». Excel отображает диалоговое окно «Запись макроса». (См. рис. 2.)

Рис. 2. Диалоговое окно «Запись макроса».

4. Использование макроса магазина В раскрывающемся списке выберите Личная книга макросов.
5. Нажмите кнопку ОК. Программа записи макросов запущена.
6. Выберите любую ячейку на листе. (Неважно, какой из них вы выберете.)
7. Нажмите «Остановить запись» на вкладке «Разработчик» на ленте.

Записанный макрос теперь хранится во вновь созданной личной книге макросов. Чтобы просмотреть созданный код, откройте редактор VB ( Alt+F11 ). В верхнем левом углу редактора находится Project Explorer; в нем перечислены все различные фрагменты и части, доступные через редактор. Один из элементов в Project Explorer должен быть PERSONAL.XLSB. Если вы развернете этот объект (щелкните маленький плюсик слева от имени проекта), вы должны увидеть папку «Модули». Разверните папку «Модули», и она содержит Module1. Если вы дважды щелкните этот модуль, вы увидите только что записанный макрос; выглядит примерно так:

Подмакрос1()

' Макрос1 Макрос
'

'
    Диапазон("A4").Выбрать
Конец сабвуфера
 

Теперь вы можете выбрать этот код и удалить его, так как он вам больше не нужен. Затем вы можете поместить в модуль другие макросы или пользовательские функции, чтобы они были доступны.

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

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

1. Введите формулу, как обычно.
2. Выберите ячейку, содержащую формулу, и нажмите F2 . Это переводит Excel в режим редактирования.
3. Удерживая нажатой клавишу Shift , используйте клавиши управления курсором для выбора всей формулы, включая знак равенства в самом начале.
4. Нажмите Ctrl+C . Теперь формула находится в буфере обмена.
5. Нажмите Esc . Теперь вы должны выйти из режима редактирования, а ячейка с формулой все еще выделена.
6. Отображение диалогового окна «Параметры Excel». (В Excel 2007 нажмите кнопку «Офис», а затем выберите «Параметры Excel». В Excel 2010 или более поздней версии откройте вкладку «Файл» на ленте и нажмите «Параметры».)
7. В левой части экрана нажмите Проверка.
8. Нажмите кнопку «Параметры автозамены». Excel отображает диалоговое окно автозамены. (См. рис. 3.)

Рисунок 3. Диалоговое окно автозамены.

9. В поле «Заменить» введите имя, которое вы хотите связать со своей формулой.
10. Поместите точку вставки в поле С.
11. Нажмите Ctrl+V , чтобы вставить формулу, скопированную на шаге 4.
12. Нажмите Добавить. Теперь формула надежно хранится в записи автозамены.
13. Закройте все диалоговые окна на экране.

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

Примечание:

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

Советы по Excel — ваш источник экономичного обучения работе с Microsoft Excel.
Этот совет (6135) относится к Microsoft Excel 2007, 2010, 2013, 2016, 2019.и Excel в Microsoft 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь: Сохранение общих формул .

Биография автора

Allen Wyatt

На его счету более 50 научно-популярных книг и множество журнальных статей, Аллен Вятт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене…

Программа успешно работает в Excel! Имя Джона Уокенбаха является синонимом мастерства в расшифровке сложных технических вопросов. В этом всеобъемлющем руководстве «Г-н Электронная таблица» показывает, как максимально использовать возможности Excel, используя профессиональные советы по разработке приложений для работы с электронными таблицами из его личной книжной полки. Ознакомьтесь с Excel 2013 Power Programming with VBA уже сегодня!

Подписаться

БЕСПЛАТНАЯ УСЛУГА: Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по повышению производительности. Введите свой адрес и нажмите «Подписаться».

Просмотреть последний информационный бюллетень.

(Ваш адрес электронной почты никому и никогда не передается.)

Комментарии

Этот сайт

Есть версия Excel, которая использует
ленточный интерфейс (Excel 2007 или новее)?
Этот сайт для вас! Если вы
использовать более раннюю версию Excel, посетите
наш сайт ExcelTips посвящен интерфейсу меню.

Новейшие наконечники

Подписаться

БЕСПЛАТНАЯ УСЛУГА: Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по повышению производительности. Введите свой адрес и нажмите «Подписаться».

(Ваш адрес электронной почты никому и никогда не передается.)

Просмотреть самый последний информационный бюллетень.

Ссылки и обмен

• Советы по Excel: часто задаваемые вопросы
• Вопросы или комментарии
• Отличные идеи брендинга
• Бесплатные календари

Copyright © 2023 Sharon Parq Associates, Inc.

Как создать отчет о старении и формулу в Excel для дебиторской задолженности

перейти к содержанию

Предыдущий Следующий

Памятка по отчету о старении

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

Хотите что-то лучше, чем Excel? Попробуйте Lockstep Inbox, наше бесплатное приложение дополненной реальности, которое покажет вам старение в реальном времени и другие ключевые показатели эффективности дополненной реальности за считанные минуты (да, это бесплатно).

ПОКАЖИТЕ МНЕ СТАРЕНИЕ

Шаг 1:

Пометьте следующие ячейки:
A1 : Клиент
B1 : Номер заказа
090 90 C4 : 900 D1:  Сумма к оплате.
Введите соответствующую информацию для ваших клиентов и их заказов под заголовками.

Шаг 2:

Добавьте дополнительные заголовки для каждого столбца следующим образом:
E1:  Просроченные дни
F1:  Не срок
G1:  0–30 дней 90 0004 90 009 90 60 дней
I1 :  61-90 дней
J1:  >90 дней

Шаг 3:

Затем мы введем формулу для столбца «Просроченные дни», которая позволит нам узнать, сколько дней этот счет остается неоплаченным с момента срок оплаты.
В ячейку E2 введите следующую формулу:  =IF(TODAY()>C2,TODAY()-C2,0)

Шаг 4:

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

Шаг 5:

Теперь мы хотим придать нашему отчету о старении немного цвета, чтобы мы могли легко увидеть, кто является самым просроченным, а кто все еще в чистоте. Выделите все строки в столбце E, затем нажмите «Условное форматирование» на вкладке «Главная» и выберите «Новое правило».

Шаг 6:

Откроется отдельное окно с названием «Новое правило форматирования».
Щелкните раскрывающийся список «Формат стиля» и выберите 3-цветную шкалу.
Щелкните раскрывающийся список «Тип» и выберите «Число»
. В разделе «Значения» введите 0 для минимума, 60 для средней точки и 90 для максимума.
Наконец, выберите наиболее подходящие для вас цвета, обычно это три цвета, которые очень далеки друг от друга на цветовой шкале.

Шаг 7:

В ячейке F2 мы узнаем, кому еще не причитаются счета. Формула проверит, есть ли в столбце «Незавершенные дни» все, что равно нулю.
В ячейку F2 введите следующую формулу:  =IF(E2=0,D2,0)
Перетащите обработчик заполнения вниз по столбцу для заполнения.

Шаг 8:

Формула для 0-30 дней в основном говорит: «Проверьте, не меньше ли разница между сегодняшней датой и датой C2 30. Если это так, введите данные из D2. Если это не так, оставьте значение 0».
Введите в ячейку G2 следующую формулу:  =ЕСЛИ(C2<СЕГОДНЯ(),(ЕСЛИ(СЕГОДНЯ()-C2<=30,D2,0)),0)
Перетащите обработчик заполнения вниз по столбцу, чтобы заполнить .

Шаг 9:

В следующей формуле будет использоваться оператор AND, который в основном говорит, что если разница между сегодняшней датой и этой датой в C2 меньше или равна 60 дням И больше 30 дней, то введите данные от Д2. В противном случае введите 0.
В ячейку h3 введите следующую формулу:  =ЕСЛИ (И(СЕГОДНЯ()-$C2<=60,СЕГОДНЯ()-$C2>30),$D2,0)
Перетащите обработчик заполнения вниз по столбцу для заполнения.

Шаг 10:

В столбце 61-90 дней формула будет аналогична формуле, введенной на шаге 9..
В ячейку I2 введите следующую формулу:  =ЕСЛИ(И(СЕГОДНЯ()-$C2<=90,СЕГОДНЯ()-$C2>60),$D2,0)
Перетащите маркер заполнения вниз столбец для заполнения.

Шаг 11:

Чтобы найти неоплаченные счета более 90 дней, формула довольно проста. Это просто означает, что если разница между сегодняшней датой и датой выполнения превышает 90, необходимо ввести данные из ячейки D2. В противном случае введите 0.