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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация формулы расчёта по двум критериям (Формулы/Formulas)
Оптимизация формулы расчёта по двум критериям
AlexM Дата: Пятница, 09.01.2015, 15:19 | Сообщение № 21
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Кстати, если без пробела найдет 10, а с пробелом будет ошибка, так как в столбце А с "1 " нет данных. :(
Позицию надо по другому искать.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеКстати, если без пробела найдет 10, а с пробелом будет ошибка, так как в столбце А с "1 " нет данных. :(
Позицию надо по другому искать.
Автор - AlexM
Дата добавления - 09.01.2015 в 15:19
gling Дата: Пятница, 09.01.2015, 15:36 | Сообщение № 22
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

2010
Исключить, проверкой данных, возможность ввода в I1 значений <3 и всё тут!

ЯД-41001506838083
 
Ответить
СообщениеИсключить, проверкой данных, возможность ввода в I1 значений <3 и всё тут!
Автор - gling
Дата добавления - 09.01.2015 в 15:36
AlexM Дата: Пятница, 09.01.2015, 15:38 | Сообщение № 23
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Изменил формулу поиска позиции с
Код
ПОИСКПОЗ(I1&"*";A2:A12;)
на
Код
=ПОИСКПОЗ(I1;(--ЛЕВБ(A2:A12;2)>3)*ЛЕВБ(A2:A12;2))
и немного поменял таблицу, для однообразия восприятия.
К сообщению приложен файл: Vopros3_2.xls (31.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеИзменил формулу поиска позиции с
Код
ПОИСКПОЗ(I1&"*";A2:A12;)
на
Код
=ПОИСКПОЗ(I1;(--ЛЕВБ(A2:A12;2)>3)*ЛЕВБ(A2:A12;2))
и немного поменял таблицу, для однообразия восприятия.
Автор - AlexM
Дата добавления - 09.01.2015 в 15:38
AlexM Дата: Пятница, 09.01.2015, 15:40 | Сообщение № 24
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Цитата gling, 09.01.2015 в 15:36, в сообщении № 22
ввода в I1 значений <3
тогда надо запрещать и >13.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата gling, 09.01.2015 в 15:36, в сообщении № 22
ввода в I1 значений <3
тогда надо запрещать и >13.
Автор - AlexM
Дата добавления - 09.01.2015 в 15:40
Baton Дата: Пятница, 09.01.2015, 20:20 | Сообщение № 25
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
AlexM,

Я не могу менять ни форму, ни формат таблицы. Поэтому, несмотря на элегантную лаконичность вашего второго решения, меня оно не устраивает. Зато первое... :)

У меня к вам несколько вопросов:
Я работаю в Еxcel на французском языке. Копируя формулы или открывая файлы с готовыми формулами, они переводятся автоматически на французский;

Код
=SI(EQUIV(I2%%;{99;2;0,5};-1)=2;I2*STXT(Коэфф;6;5)+DROITEB(Коэфф;4);I2*Коэфф)


1. Где именно и как вы определяете параметр Коэфф?
2. Почему вы предпочитаете использовать формулу
Код
DROITEB(Коэфф;4)
, а не
Код
DROITE(Коэфф;4)
3. Как "прочесть" выражение
Код
EQUIV(I2%%;{99;2;0,5};-1)
, особенно, что это за параметр 99?
Спасибо заранее...

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

Я не могу менять ни форму, ни формат таблицы. Поэтому, несмотря на элегантную лаконичность вашего второго решения, меня оно не устраивает. Зато первое... :)

У меня к вам несколько вопросов:
Я работаю в Еxcel на французском языке. Копируя формулы или открывая файлы с готовыми формулами, они переводятся автоматически на французский;

Код
=SI(EQUIV(I2%%;{99;2;0,5};-1)=2;I2*STXT(Коэфф;6;5)+DROITEB(Коэфф;4);I2*Коэфф)


1. Где именно и как вы определяете параметр Коэфф?
2. Почему вы предпочитаете использовать формулу
Код
DROITEB(Коэфф;4)
, а не
Код
DROITE(Коэфф;4)
3. Как "прочесть" выражение
Код
EQUIV(I2%%;{99;2;0,5};-1)
, особенно, что это за параметр 99?
Спасибо заранее...
Автор - Baton
Дата добавления - 09.01.2015 в 20:20
AlexM Дата: Пятница, 09.01.2015, 21:15 | Сообщение № 26
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
см вариант на 3 листе
1. Коэфф и Коэф - именованные формулы. Надо смотреть в диспетчере имен
2. DROITEB=ПРАВБ, DROITE=ПРАВСИМВ. Использую первую, потому что в русском Excel так короче. Можете использовать любую.
3. формула
Код
ПОИСКПОЗ(I2%%;{99:2:0,5};-1)
это компактный вид формулы
Код
ПОИСКПОЗ(I2;{990000:20000:5000};-1)
тут видно 5000 км 20000 км и свыше 20000 км (990000)
К сообщению приложен файл: Vopros3_3.xls (35.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Пятница, 09.01.2015, 23:58
 
Ответить
Сообщениесм вариант на 3 листе
1. Коэфф и Коэф - именованные формулы. Надо смотреть в диспетчере имен
2. DROITEB=ПРАВБ, DROITE=ПРАВСИМВ. Использую первую, потому что в русском Excel так короче. Можете использовать любую.
3. формула
Код
ПОИСКПОЗ(I2%%;{99:2:0,5};-1)
это компактный вид формулы
Код
ПОИСКПОЗ(I2;{990000:20000:5000};-1)
тут видно 5000 км 20000 км и свыше 20000 км (990000)
Автор - AlexM
Дата добавления - 09.01.2015 в 21:15
jakim Дата: Пятница, 09.01.2015, 23:36 | Сообщение № 27
Группа: Друзья
Ранг: Старожил
Сообщений: 1218
Репутация: 316 ±
Замечаний: 0% ±

Excel 2010
Второй вариант с небольшой переделкой таблицы и именованным диапазоном.
К сообщению приложен файл: 9875305.xls (29.5 Kb)
 
Ответить
Сообщение
Второй вариант с небольшой переделкой таблицы и именованным диапазоном.
Автор - jakim
Дата добавления - 09.01.2015 в 23:36
AlexM Дата: Пятница, 09.01.2015, 23:48 | Сообщение № 28
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Цитата Baton, 09.01.2015 в 20:20, в сообщении № 25
Я не могу менять ни форму, ни формат таблицы
В третьем варианте забыл про скобки. В 4-ом таблица как исходная.
К сообщению приложен файл: Vopros3_4.xls (39.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата Baton, 09.01.2015 в 20:20, в сообщении № 25
Я не могу менять ни форму, ни формат таблицы
В третьем варианте забыл про скобки. В 4-ом таблица как исходная.
Автор - AlexM
Дата добавления - 09.01.2015 в 23:48
Baton Дата: Вторник, 13.01.2015, 00:13 | Сообщение № 29
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
AlexM,
Добрый вечер,
Я попробовал открыть последнюю вашу версию дома - у меня английский интерфейс - плохо дело. Я никак не могу найти где вы определяете функцию "коэфф". Я вижу выделяемый участок в колонке А, но в регистре ячеек (если я правильно понимаю, это место слева от линии формул) нет никакого "коэфф" равно как и никакого "коэф". А расчётная ячейка стала мне выдавать ошибки :-(
Не затруднит ли вас моя просьба переименовать эти две функции, дав им какие-либо названия в латинице? Надеюсь, что таким образом я смогу прочитать и полностью понять идею вашего решения.Мне бы хотелось его применить, но не могу, не поняв как оно работает.
В таблице, которую я выложил на сайт, я написал всё на русском. Оно должно всё быть на французском. Ну и верну французскую палабру.
Спасибо и доброй ночи!

Хрен не есть редька.
 
Ответить
СообщениеAlexM,
Добрый вечер,
Я попробовал открыть последнюю вашу версию дома - у меня английский интерфейс - плохо дело. Я никак не могу найти где вы определяете функцию "коэфф". Я вижу выделяемый участок в колонке А, но в регистре ячеек (если я правильно понимаю, это место слева от линии формул) нет никакого "коэфф" равно как и никакого "коэф". А расчётная ячейка стала мне выдавать ошибки :-(
Не затруднит ли вас моя просьба переименовать эти две функции, дав им какие-либо названия в латинице? Надеюсь, что таким образом я смогу прочитать и полностью понять идею вашего решения.Мне бы хотелось его применить, но не могу, не поняв как оно работает.
В таблице, которую я выложил на сайт, я написал всё на русском. Оно должно всё быть на французском. Ну и верну французскую палабру.
Спасибо и доброй ночи!
Автор - Baton
Дата добавления - 13.01.2015 в 00:13
AlexM Дата: Вторник, 13.01.2015, 01:54 | Сообщение № 30
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Рассматриваем Лист4
Формула в I3
Код
=I2*ЛЕВБ(ПОДСТАВИТЬ(Коэф;"(d x ";"");5)+ЕСЛИ(ПОИСКПОЗ(I2%%;{99:2:0,5};-1)=2;ПРАВБ(Коэф;4);)
Коэф - именованная формула
Код
=ИНДЕКС(Sheet4!B2:D12;ПОИСКПОЗ(Sheet4!I1;(--ЛЕВБ(Sheet4!A2:A12;2)>3)*ЛЕВБ(Sheet4!A2:A12;2));4-ПОИСКПОЗ(Sheet4!I2%%;{99:2:0,5};-1))
Формулу найдете в диспетчере имен.
Для перевода формул на EN нажмите пиктограмму левее формулы.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Вторник, 13.01.2015, 01:55
 
Ответить
СообщениеРассматриваем Лист4
Формула в I3
Код
=I2*ЛЕВБ(ПОДСТАВИТЬ(Коэф;"(d x ";"");5)+ЕСЛИ(ПОИСКПОЗ(I2%%;{99:2:0,5};-1)=2;ПРАВБ(Коэф;4);)
Коэф - именованная формула
Код
=ИНДЕКС(Sheet4!B2:D12;ПОИСКПОЗ(Sheet4!I1;(--ЛЕВБ(Sheet4!A2:A12;2)>3)*ЛЕВБ(Sheet4!A2:A12;2));4-ПОИСКПОЗ(Sheet4!I2%%;{99:2:0,5};-1))
Формулу найдете в диспетчере имен.
Для перевода формул на EN нажмите пиктограмму левее формулы.
Автор - AlexM
Дата добавления - 13.01.2015 в 01:54
Baton Дата: Вторник, 13.01.2015, 18:03 | Сообщение № 31
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
AlexM,
Спасибо!
Две волшебные кнопки Ctrl + F3 открыли доступ к списку формул, где я и набрёл на "коэф".
Мой последний вопрос по этой теме:
вы написали в формуле
Код
(--LEFTB(Sheet4!A2:A12,2)>3)*LEFTB(Sheet4!A2:A12,2))
Я могу понять, что "фраза"
Код
LEFTB(A2:A12,2))
обозначает "как взять два первых знака слева в тексте в одной из ячеек диапазона А2:А12".
А как прочесть/ понять смысл выражения
Код
(--LEFTB(A2:A12,2)>3)
? В особенности два минуса вгоняют меня в ступор :-(

Хрен не есть редька.
 
Ответить
СообщениеAlexM,
Спасибо!
Две волшебные кнопки Ctrl + F3 открыли доступ к списку формул, где я и набрёл на "коэф".
Мой последний вопрос по этой теме:
вы написали в формуле
Код
(--LEFTB(Sheet4!A2:A12,2)>3)*LEFTB(Sheet4!A2:A12,2))
Я могу понять, что "фраза"
Код
LEFTB(A2:A12,2))
обозначает "как взять два первых знака слева в тексте в одной из ячеек диапазона А2:А12".
А как прочесть/ понять смысл выражения
Код
(--LEFTB(A2:A12,2)>3)
? В особенности два минуса вгоняют меня в ступор :-(
Автор - Baton
Дата добавления - 13.01.2015 в 18:03
vikttur Дата: Вторник, 13.01.2015, 18:16 | Сообщение № 32
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

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

Нет, в каждой из ячеек (создается массив текстовых строк).
ЛЕВБ - функция текстовая. поэтому, например, "58" не число, текст. Чтобы получить число, нужно с текстовой записью произвести математическое вычисление: "58"*1, "58"+0.
Двойное отрицание (бинарное отрицание) выполняет ту же функцию.


Сообщение отредактировал vikttur - Вторник, 13.01.2015, 18:17
 
Ответить
Сообщение
Цитата
как взять два первых знака слева в тексте в одной из ячеек...

Нет, в каждой из ячеек (создается массив текстовых строк).
ЛЕВБ - функция текстовая. поэтому, например, "58" не число, текст. Чтобы получить число, нужно с текстовой записью произвести математическое вычисление: "58"*1, "58"+0.
Двойное отрицание (бинарное отрицание) выполняет ту же функцию.
Автор - vikttur
Дата добавления - 13.01.2015 в 18:16
gling Дата: Вторник, 13.01.2015, 18:22 | Сообщение № 33
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

2010
Пока AlexM отсутствует, можете прочтите про двойное отрицание здесь.

ЯД-41001506838083

Сообщение отредактировал gling - Вторник, 13.01.2015, 18:23
 
Ответить
СообщениеПока AlexM отсутствует, можете прочтите про двойное отрицание здесь.
Автор - gling
Дата добавления - 13.01.2015 в 18:22
Baton Дата: Вторник, 13.01.2015, 18:27 | Сообщение № 34
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
vikttur,
если у меня в строке имеется текст " 13 НР и больше". я применяю функцию
Код
Left(а15;2)
, то ответ будет "15". Не число 15, но текст. С этим всё ясно.
НО "бинарное отрицание выполняет ту же функцию". Это что значит? Кто что отрицает? Какую "ту же функцию"?
Результат
Код
--Left(а15;2)
будет тогда не "15"? Тогда что?
Я окончательно запутался %) :(

Хрен не есть редька.
 
Ответить
Сообщениеvikttur,
если у меня в строке имеется текст " 13 НР и больше". я применяю функцию
Код
Left(а15;2)
, то ответ будет "15". Не число 15, но текст. С этим всё ясно.
НО "бинарное отрицание выполняет ту же функцию". Это что значит? Кто что отрицает? Какую "ту же функцию"?
Результат
Код
--Left(а15;2)
будет тогда не "15"? Тогда что?
Я окончательно запутался %) :(
Автор - Baton
Дата добавления - 13.01.2015 в 18:27
vikttur Дата: Вторник, 13.01.2015, 18:38 | Сообщение № 35
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Left(а15;2) - два левых символа из текста в ячейке А15. И если там текст " 13 НР и больше", то функция покажет " 1", а не 13, и тем более не 15!
Двойное отрицание в данном случае аналогично математическому действию (выполняет ту же функцию) - преобразует " 1" в число 1.

Попутно, для информации: --ЛОЖЬ=0, --ИСТИНА=1 - преобразует логическое значение в число.
Если в преобразуемом тексте находится буква - получим ошибку. Проверьте: --"л52"


Сообщение отредактировал vikttur - Вторник, 13.01.2015, 18:42
 
Ответить
СообщениеLeft(а15;2) - два левых символа из текста в ячейке А15. И если там текст " 13 НР и больше", то функция покажет " 1", а не 13, и тем более не 15!
Двойное отрицание в данном случае аналогично математическому действию (выполняет ту же функцию) - преобразует " 1" в число 1.

Попутно, для информации: --ЛОЖЬ=0, --ИСТИНА=1 - преобразует логическое значение в число.
Если в преобразуемом тексте находится буква - получим ошибку. Проверьте: --"л52"
Автор - vikttur
Дата добавления - 13.01.2015 в 18:38
JayBhagavan Дата: Вторник, 13.01.2015, 18:40 | Сообщение № 36
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 188
Репутация: 27 ±
Замечаний: 0% ±

Excel 2010
--1 = (-1) * (-1)

Языком ты или построишь жизнь,или разрушишь ее до основания.Думайте что говорите.(с)А.Хакимов
 
Ответить
Сообщение--1 = (-1) * (-1)
Автор - JayBhagavan
Дата добавления - 13.01.2015 в 18:40
Pelena Дата: Вторник, 13.01.2015, 18:44 | Сообщение № 37
Группа: Админы
Ранг: Местный житель
Сообщений: 19521
Репутация: 4633 ±
Замечаний: ±

Excel 365 & Mac Excel
До кучи :)
Что такое -- или как превратить ИСТИНА в 1, а ЛОЖЬ в 0?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеДо кучи :)
Что такое -- или как превратить ИСТИНА в 1, а ЛОЖЬ в 0?
Автор - Pelena
Дата добавления - 13.01.2015 в 18:44
Baton Дата: Вторник, 13.01.2015, 22:41 | Сообщение № 38
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 1 ±
Замечаний: 40% ±

Excel 2007
Pelena, JayBhagavan, vikttur, gling,

Merci beaucoup за ваши объяснения!
Без стакангенса не разобраться, но буду стараться.

Ещё раз спасибо всем за помощь!

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

Merci beaucoup за ваши объяснения!
Без стакангенса не разобраться, но буду стараться.

Ещё раз спасибо всем за помощь!
Автор - Baton
Дата добавления - 13.01.2015 в 22:41
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация формулы расчёта по двум критериям (Формулы/Formulas)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

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