Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Последние два заданных значения - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Последние два заданных значения (Формулы/Formulas)
Последние два заданных значения
Николай67 Дата: Понедельник, 09.03.2020, 04:50 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте уважаемые форумчане.
Представительниц прекрасного пола поздравляю с прошедшим праздником.
Я столкнулся с проблемой. Существует строка с формулами ссылающимися на заданную величину, в которую раз в несколько дней вручную вводятся значения. как сделать, что бы формула учитывала при расчете следующей ячейки не постоянную величину либо среднюю величину между первым и предыдущим (на что у меня хватило ума) , а среднее двух последних введенных вручную значений. Пример во вложении.
Заранее спасибо.
К сообщению приложен файл: 2494629.xlsx(9.7 Kb)
 
Ответить
СообщениеЗдравствуйте уважаемые форумчане.
Представительниц прекрасного пола поздравляю с прошедшим праздником.
Я столкнулся с проблемой. Существует строка с формулами ссылающимися на заданную величину, в которую раз в несколько дней вручную вводятся значения. как сделать, что бы формула учитывала при расчете следующей ячейки не постоянную величину либо среднюю величину между первым и предыдущим (на что у меня хватило ума) , а среднее двух последних введенных вручную значений. Пример во вложении.
Заранее спасибо.

Автор - Николай67
Дата добавления - 09.03.2020 в 04:50
bmv98rus Дата: Понедельник, 09.03.2020, 08:23 | Сообщение № 2
Группа: Проверенные
Ранг: Участник клуба
Сообщений: 2986
Репутация: 524 ±
Замечаний: 0% ±

Excel 2013/2016
Код
=IFERROR(AVERAGE(IFERROR(INDEX(7:7;N(INDEX(LARGE(IF(NOT(ISFORMULA($B$7:C7));COLUMN($B$7:C7));{1;2});)));1=0));"")

Но для 2010 нужна UDF заменяющая отсутствующую функцию

[vba]
Код
Function ISFORMULA(ByRef Range As Range) As Variant
Dim i As Long, j As Long, Arr
On Error Resume Next
If Range.Count > 1 Then
    Arr = Range
    For i = 1 To Range.Rows.Count
    For j = 1 To Range.Columns.Count
        Arr(i, j) = Range.Cells(i, j).HasFormula
    Next
    Next
    IS_FORMULA = Arr
Else
    IS_FORMULA = Range.HasFormula
End If
End Function

Function ЕФОРМУЛА(ByRef Range As Range) As Variant
ЕФОРМУЛА = ISFORMULA(Range)
End Function
[/vba]
К сообщению приложен файл: example1581.xlsm(16.3 Kb)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Код
=IFERROR(AVERAGE(IFERROR(INDEX(7:7;N(INDEX(LARGE(IF(NOT(ISFORMULA($B$7:C7));COLUMN($B$7:C7));{1;2});)));1=0));"")

Но для 2010 нужна UDF заменяющая отсутствующую функцию

[vba]
Код
Function ISFORMULA(ByRef Range As Range) As Variant
Dim i As Long, j As Long, Arr
On Error Resume Next
If Range.Count > 1 Then
    Arr = Range
    For i = 1 To Range.Rows.Count
    For j = 1 To Range.Columns.Count
        Arr(i, j) = Range.Cells(i, j).HasFormula
    Next
    Next
    IS_FORMULA = Arr
Else
    IS_FORMULA = Range.HasFormula
End If
End Function

Function ЕФОРМУЛА(ByRef Range As Range) As Variant
ЕФОРМУЛА = ISFORMULA(Range)
End Function
[/vba]

Автор - bmv98rus
Дата добавления - 09.03.2020 в 08:23
Светлый Дата: Понедельник, 09.03.2020, 16:12 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1441
Репутация: 380 ±
Замечаний: 0% ±

Excel 2010, 2013
Добрый день!
И мой вариант:
Код
=ЕСЛИОШИБКА(СРЗНАЧ(ИНДЕКС(6:6;НАИБОЛЬШИЙ(ЕСЛИ(ЕФОРМУЛА($B6:C6);;СТОЛБЕЦ($B:C));Ч(ИНДЕКС({1:2};)))));)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
И мой вариант:
Код
=ЕСЛИОШИБКА(СРЗНАЧ(ИНДЕКС(6:6;НАИБОЛЬШИЙ(ЕСЛИ(ЕФОРМУЛА($B6:C6);;СТОЛБЕЦ($B:C));Ч(ИНДЕКС({1:2};)))));)

Автор - Светлый
Дата добавления - 09.03.2020 в 16:12
Николай67 Дата: Среда, 11.03.2020, 09:43 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте форумчане.
Каюсь, не корректно объяснил стоящую передо мной задачу. Пробовал использовать ваши решения для создания своего решения, но к сожалению не мой уровень. К стати формула от bmv98rus работает, но слетает формула ЕФОРМУЛА если "наступить" на строку формул, формулу от Светлого так и не смог заставить работать. Буду вам очень признателен если вы мне поможете решить несколько вопросов. Дальше я сам.
1. Найти максимальное введенное вручную значение в формулированной строке.
2. Вычислить номер столбца для этого значения.
3. Найти предыдущее максимальному введенное вручную значение в формулированной строке.
4. Вычислить номер столбца для этого значения.
Заранее спасибо.
 
Ответить
СообщениеЗдравствуйте форумчане.
Каюсь, не корректно объяснил стоящую передо мной задачу. Пробовал использовать ваши решения для создания своего решения, но к сожалению не мой уровень. К стати формула от bmv98rus работает, но слетает формула ЕФОРМУЛА если "наступить" на строку формул, формулу от Светлого так и не смог заставить работать. Буду вам очень признателен если вы мне поможете решить несколько вопросов. Дальше я сам.
1. Найти максимальное введенное вручную значение в формулированной строке.
2. Вычислить номер столбца для этого значения.
3. Найти предыдущее максимальному введенное вручную значение в формулированной строке.
4. Вычислить номер столбца для этого значения.
Заранее спасибо.

Автор - Николай67
Дата добавления - 11.03.2020 в 09:43
Светлый Дата: Среда, 11.03.2020, 18:50 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1441
Репутация: 380 ±
Замечаний: 0% ±

Excel 2010, 2013
Цитата Николай67, 11.03.2020 в 09:43, в сообщении № 4 ()
Вычислить номер столбца
и куда их поместить?
А если они равны, что делать?
*Вариант:
К сообщению приложен файл: 2494629-1.xlsx(11.5 Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 11.03.2020, 19:59
 
Ответить
Сообщение
Цитата Николай67, 11.03.2020 в 09:43, в сообщении № 4 ()
Вычислить номер столбца
и куда их поместить?
А если они равны, что делать?
*Вариант:

Автор - Светлый
Дата добавления - 11.03.2020 в 18:50
Николай67 Дата: Четверг, 12.03.2020, 04:54 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте.
То что Вы сделали это то что нужно, касательно номеров столбцов. Но почему то снова слетает формула если я щелкну по строке формул. У меня офис 2016. Я вижу, что это формула массива, может из за этого?

Я могу вычислить наибольшее значение в строке, но не могу вычислить наибольшее вручную введенное в строке с формулами. Мое предположение, что не работает формула ЕФОРМУЛА.

Цитата Светлый, 11.03.2020 в 18:50, в сообщении № 5 ()
куда их поместить?


Абсолютно не важно. Я сам скомпоную нужную мне формулу из четырех, которые я просил помочь мне создать.
 
Ответить
СообщениеЗдравствуйте.
То что Вы сделали это то что нужно, касательно номеров столбцов. Но почему то снова слетает формула если я щелкну по строке формул. У меня офис 2016. Я вижу, что это формула массива, может из за этого?

Я могу вычислить наибольшее значение в строке, но не могу вычислить наибольшее вручную введенное в строке с формулами. Мое предположение, что не работает формула ЕФОРМУЛА.

Цитата Светлый, 11.03.2020 в 18:50, в сообщении № 5 ()
куда их поместить?


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

Автор - Николай67
Дата добавления - 12.03.2020 в 04:54
Светлый Дата: Четверг, 12.03.2020, 05:55 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1441
Репутация: 380 ±
Замечаний: 0% ±

Excel 2010, 2013
Цитата Николай67, 12.03.2020 в 04:54, в сообщении № 6 ()
что это формула массива, может из за этого?
Совершенно верно. Формула массива вводится одновременным нажатием Ctrl+Shift+Enter.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата Николай67, 12.03.2020 в 04:54, в сообщении № 6 ()
что это формула массива, может из за этого?
Совершенно верно. Формула массива вводится одновременным нажатием Ctrl+Shift+Enter.

Автор - Светлый
Дата добавления - 12.03.2020 в 05:55
Николай67 Дата: Четверг, 12.03.2020, 07:47 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Подскажите пожалуйста, как сделать что бы она не слетала.
 
Ответить
СообщениеПодскажите пожалуйста, как сделать что бы она не слетала.

Автор - Николай67
Дата добавления - 12.03.2020 в 07:47
Pelena Дата: Четверг, 12.03.2020, 08:47 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 15466
Репутация: 3383 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Цитата Николай67, 12.03.2020 в 07:47, в сообщении № 8 ()
что бы она не слетала
если кликаете по строке формул, потом нажмите сочетание клавиш Ctrl+Shift+Enter


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
Цитата Николай67, 12.03.2020 в 07:47, в сообщении № 8 ()
что бы она не слетала
если кликаете по строке формул, потом нажмите сочетание клавиш Ctrl+Shift+Enter

Автор - Pelena
Дата добавления - 12.03.2020 в 08:47
Николай67 Дата: Четверг, 12.03.2020, 10:17 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо за совет

Цитата Николай67, 12.03.2020 в 04:54, в сообщении № 6 ()
Я могу вычислить наибольшее значение в строке, но не могу вычислить наибольшее вручную введенное в строке с формулами.


Помогите пожалуйста с этим.
К сообщению приложен файл: 7783008.xlsx(11.5 Kb)
 
Ответить
СообщениеСпасибо за совет

Цитата Николай67, 12.03.2020 в 04:54, в сообщении № 6 ()
Я могу вычислить наибольшее значение в строке, но не могу вычислить наибольшее вручную введенное в строке с формулами.


Помогите пожалуйста с этим.

Автор - Николай67
Дата добавления - 12.03.2020 в 10:17
Pelena Дата: Четверг, 12.03.2020, 10:53 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 15466
Репутация: 3383 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Не совсем поняла, 6-я строка - это то, к чему надо стремиться? Каков конечный результат?
См. вариант в файле (строка 5)
К сообщению приложен файл: 2605438.xlsx(12.1 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеНе совсем поняла, 6-я строка - это то, к чему надо стремиться? Каков конечный результат?
См. вариант в файле (строка 5)

Автор - Pelena
Дата добавления - 12.03.2020 в 10:53
Николай67 Дата: Четверг, 12.03.2020, 11:37 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - Николай67
Дата добавления - 12.03.2020 в 11:37
Pelena Дата: Четверг, 12.03.2020, 12:01 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 15466
Репутация: 3383 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Вы мой файл смотрели? Не так?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеВы мой файл смотрели? Не так?

Автор - Pelena
Дата добавления - 12.03.2020 в 12:01
Николай67 Дата: Четверг, 12.03.2020, 12:20 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
На сколько я понял в Вашем файле так же идет привязка к фиксированным величинам. В моем случае введенные величины вручную не пропорциональны ни чему (взяты со счетчиков). И при расчете нужна ссылка именно на них, так как они являются фактическими. Расчет будет производиться как в строке 6. Но в расчете должны участвовать фактические величины. Я знаю, что должны участвовать ЕФОРМУЛА и НАИБОЛЬШИЙ, но к сожалению у меня не хватает ума их связать. В примере введенные вручную величины выделены желтой заливкой, остальное формулы.
 
Ответить
СообщениеНа сколько я понял в Вашем файле так же идет привязка к фиксированным величинам. В моем случае введенные величины вручную не пропорциональны ни чему (взяты со счетчиков). И при расчете нужна ссылка именно на них, так как они являются фактическими. Расчет будет производиться как в строке 6. Но в расчете должны участвовать фактические величины. Я знаю, что должны участвовать ЕФОРМУЛА и НАИБОЛЬШИЙ, но к сожалению у меня не хватает ума их связать. В примере введенные вручную величины выделены желтой заливкой, остальное формулы.

Автор - Николай67
Дата добавления - 12.03.2020 в 12:20
Pelena Дата: Четверг, 12.03.2020, 12:31 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 15466
Репутация: 3383 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Цитата Николай67, 12.03.2020 в 12:20, в сообщении № 14 ()
идет привязка к фиксированным величинам

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


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
Цитата Николай67, 12.03.2020 в 12:20, в сообщении № 14 ()
идет привязка к фиксированным величинам

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

Автор - Pelena
Дата добавления - 12.03.2020 в 12:31
Michael_S Дата: Четверг, 12.03.2020, 12:41 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1942
Репутация: 361 ±
Замечаний: 0% ±

Excel2016
Николай67, я так думаю, что если бы вы описали саму задачу, а не метод, которым вы её решаете, то решение давно уже б было.
Лично я только после последнего сообщения начиню понимать, что вам нужно, да и то, неуверенно.


ЯД: 41001136675053
WM: R389613894253
 
Ответить
СообщениеНиколай67, я так думаю, что если бы вы описали саму задачу, а не метод, которым вы её решаете, то решение давно уже б было.
Лично я только после последнего сообщения начиню понимать, что вам нужно, да и то, неуверенно.

Автор - Michael_S
Дата добавления - 12.03.2020 в 12:41
Николай67 Дата: Четверг, 12.03.2020, 12:49 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Разница в наших файлах заключается в том, что у Вас формуле нет необходимости различать в ячейках на которые она ссылается это формула или значение, она просто анализирует введенные значения, которые являются некоторыми константами. А у меня необходимость именно в этом, отличить значение от формулы и применить это значение в расчете. Посмотрите пожалуйста строки 5 и 6.
 
Ответить
СообщениеРазница в наших файлах заключается в том, что у Вас формуле нет необходимости различать в ячейках на которые она ссылается это формула или значение, она просто анализирует введенные значения, которые являются некоторыми константами. А у меня необходимость именно в этом, отличить значение от формулы и применить это значение в расчете. Посмотрите пожалуйста строки 5 и 6.

Автор - Николай67
Дата добавления - 12.03.2020 в 12:49
Pelena Дата: Четверг, 12.03.2020, 13:28 | Сообщение № 18
Группа: Админы
Ранг: Местный житель
Сообщений: 15466
Репутация: 3383 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Что-то странное с файлами происходит.
Не тот файл вложился. См. ещё раз
К сообщению приложен файл: 7515338.xlsx(12.1 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЧто-то странное с файлами происходит.
Не тот файл вложился. См. ещё раз

Автор - Pelena
Дата добавления - 12.03.2020 в 13:28
Николай67 Дата: Пятница, 13.03.2020, 07:34 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте уважаемые форумчане. Огромное спасибо за помощь. Из Тех примеров, которые вы мне выслали я все таки скомпоновал нужную мне формулу. Может она выглядит топорно, но она работает именно так как мне надо. Конечный результат во вложении. Спасибо, что вы есть.
К сообщению приложен файл: 7515338_2.xlsx(10.5 Kb)
 
Ответить
СообщениеЗдравствуйте уважаемые форумчане. Огромное спасибо за помощь. Из Тех примеров, которые вы мне выслали я все таки скомпоновал нужную мне формулу. Может она выглядит топорно, но она работает именно так как мне надо. Конечный результат во вложении. Спасибо, что вы есть.

Автор - Николай67
Дата добавления - 13.03.2020 в 07:34
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Последние два заданных значения (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2020 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!