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

Вход

Регистрация

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

 

= Мир MS Excel/Оптимизация формулы расчёта по двум критериям - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация формулы расчёта по двум критериям (Формулы/Formulas)
Оптимизация формулы расчёта по двум критериям
Baton Дата: Четверг, 08.01.2015, 11:51 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
Доброе утро всем

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

Спасибо за любые предложения.
К сообщению приложен файл: Vopros3.xls (26.0 Kb)

Хрен не есть редька.
 
Ответить
СообщениеДоброе утро всем

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

Спасибо за любые предложения.
Автор - Baton
Дата добавления - 08.01.2015 в 11:51
DJ_Marker_MC Дата: Четверг, 08.01.2015, 14:04 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Baton, Добрый день.
Как насчет такого варианта решения?
К сообщению приложен файл: 6437370.xlsx (11.7 Kb)
 
Ответить
СообщениеBaton, Добрый день.
Как насчет такого варианта решения?
Автор - DJ_Marker_MC
Дата добавления - 08.01.2015 в 14:04
Baton Дата: Четверг, 08.01.2015, 14:14 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
DJ_Marker_MC,

Идея отличная и мне очень понравилось решение. Спасибо! К сожалению, мне нужно решение, которое не предусматривает использование дополнительных ячеек, строк или колонок :-(((

Хрен не есть редька.
 
Ответить
СообщениеDJ_Marker_MC,

Идея отличная и мне очень понравилось решение. Спасибо! К сожалению, мне нужно решение, которое не предусматривает использование дополнительных ячеек, строк или колонок :-(((
Автор - Baton
Дата добавления - 08.01.2015 в 14:14
Richman Дата: Четверг, 08.01.2015, 14:44 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 346
Репутация: 54 ±
Замечаний: 0% ±

Excel 2007
Baton, Если Вам нравится решение DJ_Marker_MC я все его формулы из доп ячеек скопировал в одну.
К сообщению приложен файл: 6437370-1-.xlsx (11.2 Kb)

С Уважением, Richman

 
Ответить
СообщениеBaton, Если Вам нравится решение DJ_Marker_MC я все его формулы из доп ячеек скопировал в одну.
Автор - Richman
Дата добавления - 08.01.2015 в 14:44
gling Дата: Четверг, 08.01.2015, 15:07 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

2010
Получилась такая формула
Код
=ЕСЛИ($I$2>20000;$I$2*ВПР($I$1;$A$2:$D$12;4;1);ЕСЛИ($I$2<=5000;$I$2*ВПР($I$1;$A$2:$D$12;2;1);$I$2*ПРАВСИМВ(ЛЕВСИМВ(ВПР($I$1;$A$2:$D$12;3;1);10);5)+ПРАВСИМВ(ВПР($I$1;$A$2:$D$12;3;1);4)))
В файле в солбце А прописал пользовательский формат и в вашей формуле убрал &" HP"&. Тестируйте.
Нашел ошибку перевложил файл
К сообщению приложен файл: 1560080.xls (29.5 Kb)

ЯД-41001506838083

Сообщение отредактировал gling - Четверг, 08.01.2015, 15:19
 
Ответить
СообщениеПолучилась такая формула
Код
=ЕСЛИ($I$2>20000;$I$2*ВПР($I$1;$A$2:$D$12;4;1);ЕСЛИ($I$2<=5000;$I$2*ВПР($I$1;$A$2:$D$12;2;1);$I$2*ПРАВСИМВ(ЛЕВСИМВ(ВПР($I$1;$A$2:$D$12;3;1);10);5)+ПРАВСИМВ(ВПР($I$1;$A$2:$D$12;3;1);4)))
В файле в солбце А прописал пользовательский формат и в вашей формуле убрал &" HP"&. Тестируйте.
Нашел ошибку перевложил файл
Автор - gling
Дата добавления - 08.01.2015 в 15:07
Baton Дата: Четверг, 08.01.2015, 15:40 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
Richman,

Огромное спасибо! beer

Хрен не есть редька.
 
Ответить
СообщениеRichman,

Огромное спасибо! beer
Автор - Baton
Дата добавления - 08.01.2015 в 15:40
DJ_Marker_MC Дата: Четверг, 08.01.2015, 15:44 | Сообщение № 7
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Baton, Richman, ну вообще то столбцы А и два срытых E,F по прежнему продолжает использоваться.
 
Ответить
СообщениеBaton, Richman, ну вообще то столбцы А и два срытых E,F по прежнему продолжает использоваться.
Автор - DJ_Marker_MC
Дата добавления - 08.01.2015 в 15:44
Baton Дата: Четверг, 08.01.2015, 15:44 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
gling,

Большое спасибо!
посижу и внимательно поразбираю все предложенные варианты. есть чему поучиться.
Ещё раз спассибо!

Хрен не есть редька.
 
Ответить
Сообщениеgling,

Большое спасибо!
посижу и внимательно поразбираю все предложенные варианты. есть чему поучиться.
Ещё раз спассибо!
Автор - Baton
Дата добавления - 08.01.2015 в 15:44
Baton Дата: Четверг, 08.01.2015, 16:38 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
gling,
сел читать более внимательно ваше решение. возник вопрос:
В ячейках А2:А2 написано одно, но когда я кликаю на одну из них, то в линии формул я вижу лишь цифры. Я думаю, что это именно то, что мешает мне использовать формулу. Можете ли вы объяснить что и как нужно сделать и что это даёт? Я никогда не пользовался персональным форматом.

Спасибо за новые знания!

Хрен не есть редька.
 
Ответить
Сообщениеgling,
сел читать более внимательно ваше решение. возник вопрос:
В ячейках А2:А2 написано одно, но когда я кликаю на одну из них, то в линии формул я вижу лишь цифры. Я думаю, что это именно то, что мешает мне использовать формулу. Можете ли вы объяснить что и как нужно сделать и что это даёт? Я никогда не пользовался персональным форматом.

Спасибо за новые знания!
Автор - Baton
Дата добавления - 08.01.2015 в 16:38
gling Дата: Четверг, 08.01.2015, 16:59 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

2010
Цитата
что это даёт?
Это исключает использование дополнительно столбца. В принципе наверно можно этот массив забить сразу в формулу, чтобы не путаться с пользоват. форматом. Сейчас попробую.

ЯД-41001506838083
 
Ответить
Сообщение
Цитата
что это даёт?
Это исключает использование дополнительно столбца. В принципе наверно можно этот массив забить сразу в формулу, чтобы не путаться с пользоват. форматом. Сейчас попробую.
Автор - gling
Дата добавления - 08.01.2015 в 16:59
Baton Дата: Четверг, 08.01.2015, 17:09 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
gling,

я предпочёл бы узнать как сделать персональный формат - уж больно здорово оно получается. ваша формула в два раза короче моей. :D

Хрен не есть редька.
 
Ответить
Сообщениеgling,

я предпочёл бы узнать как сделать персональный формат - уж больно здорово оно получается. ваша формула в два раза короче моей. :D
Автор - Baton
Дата добавления - 08.01.2015 в 17:09
gling Дата: Четверг, 08.01.2015, 17:20 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

2010
Формат ячеек--(все форматы), увидите какой там прописан. В окошке под Тип: пишите свой, пробуйте, почитайте про п. формат. В окошке "Образец" видно как будет выглядеть значение в ячейке.
С ВПР() не получилось, можно с ПРОСМОТР(), но длиннее
Код
=ЕСЛИ($I$2>20000;$I$2*ПРОСМОТР($I$1;{0:4:5:6:7:8:9:10:11:12:13};$D$2:$D$12);ЕСЛИ($I$2<=5000;$I$2*ПРОСМОТР($I$1;{0:4:5:6:7:8:9:10:11:12:13};$B$2:$B$12);$I$2*ПРАВСИМВ(ЛЕВСИМВ(ПРОСМОТР($I$1;{0:4:5:6:7:8:9:10:11:12:13};$C$2:$C$12);10);5)+ПРАВСИМВ(ПРОСМОТР($I$1;{0:4:5:6:7:8:9:10:11:12:13};$C$2:$C$12);4)))

ЯД-41001506838083

Сообщение отредактировал gling - Четверг, 08.01.2015, 17:23
 
Ответить
СообщениеФормат ячеек--(все форматы), увидите какой там прописан. В окошке под Тип: пишите свой, пробуйте, почитайте про п. формат. В окошке "Образец" видно как будет выглядеть значение в ячейке.
С ВПР() не получилось, можно с ПРОСМОТР(), но длиннее
Код
=ЕСЛИ($I$2>20000;$I$2*ПРОСМОТР($I$1;{0:4:5:6:7:8:9:10:11:12:13};$D$2:$D$12);ЕСЛИ($I$2<=5000;$I$2*ПРОСМОТР($I$1;{0:4:5:6:7:8:9:10:11:12:13};$B$2:$B$12);$I$2*ПРАВСИМВ(ЛЕВСИМВ(ПРОСМОТР($I$1;{0:4:5:6:7:8:9:10:11:12:13};$C$2:$C$12);10);5)+ПРАВСИМВ(ПРОСМОТР($I$1;{0:4:5:6:7:8:9:10:11:12:13};$C$2:$C$12);4)))
Автор - gling
Дата добавления - 08.01.2015 в 17:20
Baton Дата: Четверг, 08.01.2015, 18:17 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
gling,
Я залез в рубрику персональных форматов и по аналогии с вашим синтаксисом написал свой текст. Не работает. У меня в моёй первоначальной таблице в одном из мест используется знак & вместо "и". Это может иметь какое-то значение?

Хрен не есть редька.

Сообщение отредактировал Baton - Четверг, 08.01.2015, 18:17
 
Ответить
Сообщениеgling,
Я залез в рубрику персональных форматов и по аналогии с вашим синтаксисом написал свой текст. Не работает. У меня в моёй первоначальной таблице в одном из мест используется знак & вместо "и". Это может иметь какое-то значение?
Автор - Baton
Дата добавления - 08.01.2015 в 18:17
jakim Дата: Четверг, 08.01.2015, 19:44 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1218
Репутация: 316 ±
Замечаний: 0% ±

Excel 2010
Вариант.
К сообщению приложен файл: Vopros3-1.xls (27.5 Kb)
 
Ответить
Сообщение
Вариант.
Автор - jakim
Дата добавления - 08.01.2015 в 19:44
gling Дата: Четверг, 08.01.2015, 21:19 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

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

ЯД-41001506838083

Сообщение отредактировал gling - Четверг, 08.01.2015, 21:23
 
Ответить
СообщениеОбсуждение здесь про форматы - это не по теме вопроса. Что бы говорить о значении символа, надо знать как он написан, в кавычках или без, рядом с какими знаками и т.д. К тому же я тоже не знаю всех нюансов, многое узнаю здесь на форуме и своими попытками.
Автор - gling
Дата добавления - 08.01.2015 в 21:19
Baton Дата: Пятница, 09.01.2015, 11:06 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

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

Хрен не есть редька.
 
Ответить
Сообщениеjakim,
доброе утро
идея отличная, на все случаи жизни. спасибо!
к моей истории она, к сожалению, не подходит, так как я не могу добавлять колонки.
Автор - Baton
Дата добавления - 09.01.2015 в 11:06
AlexM Дата: Пятница, 09.01.2015, 13:42 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Еще вариант. В I4 полная формула, а в I3 та же формула с именованными фрагментами.
Полная формула
Код
=ЕСЛИ(ПОИСКПОЗ(I2%%;{99:2:0,5};-1)=2;I2*ПСТР(ИНДЕКС(B2:D12;ПОИСКПОЗ(I1&"*";A2:A12;);4-ПОИСКПОЗ(I2%%;{99:2:0,5};-1));6;5)+ПРАВБ(ИНДЕКС(B2:D12;ПОИСКПОЗ(I1&"*";A2:A12;);4-ПОИСКПОЗ(I2%%;{99:2:0,5};-1));4);I2*ИНДЕКС(B2:D12;ПОИСКПОЗ(I1&"*";A2:A12;);4-ПОИСКПОЗ(I2%%;{99:2:0,5};-1)))
К сообщению приложен файл: Vopros3_1.xls (27.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЕще вариант. В I4 полная формула, а в I3 та же формула с именованными фрагментами.
Полная формула
Код
=ЕСЛИ(ПОИСКПОЗ(I2%%;{99:2:0,5};-1)=2;I2*ПСТР(ИНДЕКС(B2:D12;ПОИСКПОЗ(I1&"*";A2:A12;);4-ПОИСКПОЗ(I2%%;{99:2:0,5};-1));6;5)+ПРАВБ(ИНДЕКС(B2:D12;ПОИСКПОЗ(I1&"*";A2:A12;);4-ПОИСКПОЗ(I2%%;{99:2:0,5};-1));4);I2*ИНДЕКС(B2:D12;ПОИСКПОЗ(I1&"*";A2:A12;);4-ПОИСКПОЗ(I2%%;{99:2:0,5};-1)))
Автор - AlexM
Дата добавления - 09.01.2015 в 13:42
gling Дата: Пятница, 09.01.2015, 14:18 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

2010
Здравствуйте все. Здесь I1&"*" перед * лучше поставить пробел (I1&" *") иначе при 1 найдет 10. А может 1 и не бывает.

ЯД-41001506838083
 
Ответить
СообщениеЗдравствуйте все. Здесь I1&"*" перед * лучше поставить пробел (I1&" *") иначе при 1 найдет 10. А может 1 и не бывает.
Автор - gling
Дата добавления - 09.01.2015 в 14:18
AlexM Дата: Пятница, 09.01.2015, 14:59 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Цитата gling, 09.01.2015 в 14:18, в сообщении № 18
А может 1 и не бывает
В таблице задано 3 Рн и меньше, так что 1 и 2 быть не должно.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата gling, 09.01.2015 в 14:18, в сообщении № 18
А может 1 и не бывает
В таблице задано 3 Рн и меньше, так что 1 и 2 быть не должно.
Автор - AlexM
Дата добавления - 09.01.2015 в 14:59
gling Дата: Пятница, 09.01.2015, 15:09 | Сообщение № 20
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

2010
Так I1 заполняется в ручную не из списка.

ЯД-41001506838083
 
Ответить
СообщениеТак I1 заполняется в ручную не из списка.
Автор - gling
Дата добавления - 09.01.2015 в 15:09
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация формулы расчёта по двум критериям (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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