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

Вход

Регистрация

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

 

= Мир MS Excel/Выбор, поиск значений в диапазоне ЕСЛИ НЕ ПУСТАЯ ячейка - Страница 2 - Мир MS Excel

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор, поиск значений в диапазоне ЕСЛИ НЕ ПУСТАЯ ячейка (Формулы/Formulas)
Выбор, поиск значений в диапазоне ЕСЛИ НЕ ПУСТАЯ ячейка
Nic70y Дата: Пятница, 04.01.2019, 13:38 | Сообщение № 21
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Длина записи формулы, знаков, не более 8 192
My WebPage
Цитата Никанор, 04.01.2019 в 13:34, в сообщении № 20 ()
Почему в 1-ом примере ячейки выделены цветным шрифтом
цвета закончились, не обращайте внимание на это.


ЮMoney 41001841029809
 
Ответить
СообщениеДлина записи формулы, знаков, не более 8 192
My WebPage
Цитата Никанор, 04.01.2019 в 13:34, в сообщении № 20 ()
Почему в 1-ом примере ячейки выделены цветным шрифтом
цвета закончились, не обращайте внимание на это.

Автор - Nic70y
Дата добавления - 04.01.2019 в 13:38
Никанор Дата: Пятница, 04.01.2019, 15:14 | Сообщение № 22
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Nic70y – Николай!
Спасибо за ответ!
1 вопрос: Скажите, пожалуйста, значит, я написал правильную формулу, не смотря на то, что она очень и очень длинная и её сократить – усовершенствовать нельзя?
И Вы пишите что:
Длина записи формулы, знаков, не более 8 192

2 вопрос: А у меня число знаков с пробелами 3 533 но ведь это же меньше чем 8 192, тогда почему нет разных цветов у шрифта имени ячеек?
3 вопрос: Я понял что не нужно на это обращать внимания и формула будет правильно «работать». Верно?


Сообщение отредактировал Никанор - Пятница, 04.01.2019, 15:18
 
Ответить
СообщениеNic70y – Николай!
Спасибо за ответ!
1 вопрос: Скажите, пожалуйста, значит, я написал правильную формулу, не смотря на то, что она очень и очень длинная и её сократить – усовершенствовать нельзя?
И Вы пишите что:
Длина записи формулы, знаков, не более 8 192

2 вопрос: А у меня число знаков с пробелами 3 533 но ведь это же меньше чем 8 192, тогда почему нет разных цветов у шрифта имени ячеек?
3 вопрос: Я понял что не нужно на это обращать внимания и формула будет правильно «работать». Верно?

Автор - Никанор
Дата добавления - 04.01.2019 в 15:14
Nic70y Дата: Пятница, 04.01.2019, 16:44 | Сообщение № 23
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
будет работать
Цитата Никанор, 04.01.2019 в 15:14, в сообщении № 22 ()
тогда почему нет разных цветов
видать есть какие-то ограничения, не задумывался об этом, сталкивался и знаю что такое существует (давно было)
Цитата Никанор, 04.01.2019 в 15:14, в сообщении № 22 ()
усовершенствовать нельзя?
да вроде норм формула.
Цитата Никанор, 04.01.2019 в 15:14, в сообщении № 22 ()
сократить
я уже писал, что можно.
если формула ЕСЛИ ссылается на числа больше нуля, это =ИСТИНА, 0 (пусто) =ЛОЖЬ
т.е
Код
=ЕСЛИ(C14<>"";B14;"")&
можно записать
Код
=ЕСЛИ(C14;B14;"")&


ЮMoney 41001841029809
 
Ответить
Сообщениебудет работать
Цитата Никанор, 04.01.2019 в 15:14, в сообщении № 22 ()
тогда почему нет разных цветов
видать есть какие-то ограничения, не задумывался об этом, сталкивался и знаю что такое существует (давно было)
Цитата Никанор, 04.01.2019 в 15:14, в сообщении № 22 ()
усовершенствовать нельзя?
да вроде норм формула.
Цитата Никанор, 04.01.2019 в 15:14, в сообщении № 22 ()
сократить
я уже писал, что можно.
если формула ЕСЛИ ссылается на числа больше нуля, это =ИСТИНА, 0 (пусто) =ЛОЖЬ
т.е
Код
=ЕСЛИ(C14<>"";B14;"")&
можно записать
Код
=ЕСЛИ(C14;B14;"")&

Автор - Nic70y
Дата добавления - 04.01.2019 в 16:44
Никанор Дата: Пятница, 04.01.2019, 18:07 | Сообщение № 24
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Nic70y – Николай спасибо за ответ на все мои вопросы!
Я только не понял следующее:
Скажите пожалуйста в какие ячейки надо писать эти формулы?
Или это части формул?
Я знаю что знак & сцепляет – соединяет текст.
Наверное вместо В14 надо вставить
Код
ЛЕВСИМВ(B14;ПОИСК(" *";B14)-1)&" "&ПСТР(B14;ПОИСК(" *";B14)+1;1)&". "&ПСТР(B14;ПОИСК(" *";B14;ПОИСК(" *";B14)+1)+1;1)&". ";"")


Или с помощью этих формул можно сократить мои длинные формулы?
Если да, я только не знаю как формулы можно сократить.
Можете ли Вы подсказать, как сократить формулы?
С уважением.


Сообщение отредактировал Никанор - Суббота, 05.01.2019, 11:04
 
Ответить
СообщениеNic70y – Николай спасибо за ответ на все мои вопросы!
Я только не понял следующее:
Скажите пожалуйста в какие ячейки надо писать эти формулы?
Или это части формул?
Я знаю что знак & сцепляет – соединяет текст.
Наверное вместо В14 надо вставить
Код
ЛЕВСИМВ(B14;ПОИСК(" *";B14)-1)&" "&ПСТР(B14;ПОИСК(" *";B14)+1;1)&". "&ПСТР(B14;ПОИСК(" *";B14;ПОИСК(" *";B14)+1)+1;1)&". ";"")


Или с помощью этих формул можно сократить мои длинные формулы?
Если да, я только не знаю как формулы можно сократить.
Можете ли Вы подсказать, как сократить формулы?
С уважением.

Автор - Никанор
Дата добавления - 04.01.2019 в 18:07
Nic70y Дата: Пятница, 04.01.2019, 20:53 | Сообщение № 25
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
например в ячейках N117, N120
К сообщению приложен файл: 4159420-1-.xlsx (91.1 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщениенапример в ячейках N117, N120

Автор - Nic70y
Дата добавления - 04.01.2019 в 20:53
Pelena Дата: Пятница, 04.01.2019, 21:27 | Сообщение № 26
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Никанор, оформите формулу тегами


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеНиканор, оформите формулу тегами

Автор - Pelena
Дата добавления - 04.01.2019 в 21:27
Никанор Дата: Суббота, 05.01.2019, 11:07 | Сообщение № 27
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Pelena – Елена Здравствуйте!
оформите формулу тегами
Я подумал, что это не формула, а только часть формулы, которую надо вставить в другую формулу.
И там нет знака = (равно) поэтому не оформил её тегами.

Скажите, пожалуйста, и такую часть формулы тоже надо оформлять тегами, ЕСЛИ нет знака = (равно)?

Только что выполнил Ваше замечание – оформил тегами.


Сообщение отредактировал Никанор - Воскресенье, 06.01.2019, 10:50
 
Ответить
СообщениеPelena – Елена Здравствуйте!
оформите формулу тегами
Я подумал, что это не формула, а только часть формулы, которую надо вставить в другую формулу.
И там нет знака = (равно) поэтому не оформил её тегами.

Скажите, пожалуйста, и такую часть формулы тоже надо оформлять тегами, ЕСЛИ нет знака = (равно)?

Только что выполнил Ваше замечание – оформил тегами.

Автор - Никанор
Дата добавления - 05.01.2019 в 11:07
китин Дата: Суббота, 05.01.2019, 12:09 | Сообщение № 28
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Никанор, я хоть и не Елена отвечу. в движке форума заложена функция перевода формул, но она работает только если формула "обтэжена". и знак = тут роли не играет, главное оформить формулу тэгами. очень много пользователей форума пользуются Excel в англицкой локали. да и читать формулу, которая нормально оформлена гораздо удобнее


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеНиканор, я хоть и не Елена отвечу. в движке форума заложена функция перевода формул, но она работает только если формула "обтэжена". и знак = тут роли не играет, главное оформить формулу тэгами. очень много пользователей форума пользуются Excel в англицкой локали. да и читать формулу, которая нормально оформлена гораздо удобнее

Автор - китин
Дата добавления - 05.01.2019 в 12:09
Никанор Дата: Суббота, 05.01.2019, 13:42 | Сообщение № 29
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
китин – Игорь Здравствуйте!
Большое спасибо за ответ и объяснение на мой вопрос.
Да согласен, что
читать формулу, которая нормально оформлена гораздо удобнее


Сообщение отредактировал Никанор - Суббота, 05.01.2019, 13:43
 
Ответить
Сообщениекитин – Игорь Здравствуйте!
Большое спасибо за ответ и объяснение на мой вопрос.
Да согласен, что
читать формулу, которая нормально оформлена гораздо удобнее

Автор - Никанор
Дата добавления - 05.01.2019 в 13:42
Никанор Дата: Вторник, 08.01.2019, 11:11 | Сообщение № 30
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте Николай!

Извините, пожалуйста, что на Ваше Сообщение № 25 от Дата: Пятница, 04.01.2019, 20:53 отвечаю только сегодня. Раньше не было возможности посмотреть Ваш файл.
Большое спасибо за формулы и за ответы на мои многочисленные вопросы.
Ваши формулы даю правильный результат.
Уже есть все варианты формул.

Мне только интересно можно ли написать формулы, что бы искала, делала ВЫБОР из диапазона ячеек B14:B48 что бы был такой результат как в прикреплённом файле в ячейке:
D95 – в одну ячейку в строчку полностью фамилия имя отчество;
D103 – в одну ячейку в строчку фамилия и инициалы;
K104 – в столбец фамилия и инициалы.

Все эти формулы есть только очень - очень длинные.
Повторюсь мне интересно можно ли сократить, что бы эти формулы были короче и искали из ячеек B14:B48 что-то наподобие Вашей формулы массива
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)))&" ";" ";ПОВТОР(" ";136);3);131));"")


Прикрепляю Ваш файл:
Это Ваш файл -1-1-.xlsx(90.2 Kb) из сообщения Дата: Пятница, 04.01.2019, 11:45 | Сообщение № 15.

С уважением и наилучшими пожеланиями.
К сообщению приложен файл: 0276296.xlsx (90.1 Kb)


Сообщение отредактировал Никанор - Вторник, 08.01.2019, 11:19
 
Ответить
СообщениеЗдравствуйте Николай!

Извините, пожалуйста, что на Ваше Сообщение № 25 от Дата: Пятница, 04.01.2019, 20:53 отвечаю только сегодня. Раньше не было возможности посмотреть Ваш файл.
Большое спасибо за формулы и за ответы на мои многочисленные вопросы.
Ваши формулы даю правильный результат.
Уже есть все варианты формул.

Мне только интересно можно ли написать формулы, что бы искала, делала ВЫБОР из диапазона ячеек B14:B48 что бы был такой результат как в прикреплённом файле в ячейке:
D95 – в одну ячейку в строчку полностью фамилия имя отчество;
D103 – в одну ячейку в строчку фамилия и инициалы;
K104 – в столбец фамилия и инициалы.

Все эти формулы есть только очень - очень длинные.
Повторюсь мне интересно можно ли сократить, что бы эти формулы были короче и искали из ячеек B14:B48 что-то наподобие Вашей формулы массива
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)))&" ";" ";ПОВТОР(" ";136);3);131));"")


Прикрепляю Ваш файл:
Это Ваш файл -1-1-.xlsx(90.2 Kb) из сообщения Дата: Пятница, 04.01.2019, 11:45 | Сообщение № 15.

С уважением и наилучшими пожеланиями.

Автор - Никанор
Дата добавления - 08.01.2019 в 11:11
Nic70y Дата: Вторник, 08.01.2019, 11:57 | Сообщение № 31
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
k104
Код
=ЛЕВБ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));ПОИСК(" ";ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1))))+1)&"."&ПСТР(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));ПОИСК("\";ПОДСТАВИТЬ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));" ";"\";2))+1;1)&"."
остальное - нет.
(это не значит, что это не возможно, а всего лишь, что нельзя решить средствами, которыми пытаемся решить)


ЮMoney 41001841029809
 
Ответить
Сообщениеk104
Код
=ЛЕВБ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));ПОИСК(" ";ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1))))+1)&"."&ПСТР(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));ПОИСК("\";ПОДСТАВИТЬ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));" ";"\";2))+1;1)&"."
остальное - нет.
(это не значит, что это не возможно, а всего лишь, что нельзя решить средствами, которыми пытаемся решить)

Автор - Nic70y
Дата добавления - 08.01.2019 в 11:57
Никанор Дата: Вторник, 08.01.2019, 13:28 | Сообщение № 32
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Nic70y, Николай!
Спасибо!
В столбец получилось. Я понял это формула массива – сочетание клавиш Ctrl+Shift+Enter.
И я «протянул» эту формулу вниз.
Но только в этом случае ЕСЛИ нет данных то результат пишет #ЧИСЛО!
Только мне кажется, что бы не писало число, ЕСЛИ нет значений надо немного изменить формулу:
в начало формулы написать функцию ЕСЛИОШИБКА и поставить скобки.
Но у меня не получается её изменить.
Пробую ставить скобки вначале перед ЛЕВБ и в самом конце формулы после кавычек – не получается.


Сообщение отредактировал Никанор - Вторник, 08.01.2019, 13:52
 
Ответить
СообщениеNic70y, Николай!
Спасибо!
В столбец получилось. Я понял это формула массива – сочетание клавиш Ctrl+Shift+Enter.
И я «протянул» эту формулу вниз.
Но только в этом случае ЕСЛИ нет данных то результат пишет #ЧИСЛО!
Только мне кажется, что бы не писало число, ЕСЛИ нет значений надо немного изменить формулу:
в начало формулы написать функцию ЕСЛИОШИБКА и поставить скобки.
Но у меня не получается её изменить.
Пробую ставить скобки вначале перед ЛЕВБ и в самом конце формулы после кавычек – не получается.

Автор - Никанор
Дата добавления - 08.01.2019 в 13:28
Nic70y Дата: Вторник, 08.01.2019, 14:41 | Сообщение № 33
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Код
=ЕСЛИОШИБКА(ЛЕВБ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));ПОИСК(" ";ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1))))+1)&"."&ПСТР(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));ПОИСК("\";ПОДСТАВИТЬ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));" ";"\";2))+1;1)&".";"")


ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=ЕСЛИОШИБКА(ЛЕВБ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));ПОИСК(" ";ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1))))+1)&"."&ПСТР(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));ПОИСК("\";ПОДСТАВИТЬ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)));" ";"\";2))+1;1)&".";"")

Автор - Nic70y
Дата добавления - 08.01.2019 в 14:41
Никанор Дата: Вторник, 08.01.2019, 16:10 | Сообщение № 34
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Николай!
Спасибо большое!
Я посмотрел, что делал неправильно:
Вначале перед ЛЕВБ я скобку поставил правильно:
Код
=ЕСЛИОШИБКА(ЛЕВБ

а в конце я ставил скобку после:
&".")

А надо в конце после кавычек поставить точку с запятой, ещё кавычки и закрывающие скобки. Вот так:
&".";"")
Я бы так не придумал.
Спасибо Вам большое за ответы на все мои вопросы.
С уважением.


Сообщение отредактировал Никанор - Вторник, 08.01.2019, 16:15
 
Ответить
СообщениеНиколай!
Спасибо большое!
Я посмотрел, что делал неправильно:
Вначале перед ЛЕВБ я скобку поставил правильно:
Код
=ЕСЛИОШИБКА(ЛЕВБ

а в конце я ставил скобку после:
&".")

А надо в конце после кавычек поставить точку с запятой, ещё кавычки и закрывающие скобки. Вот так:
&".";"")
Я бы так не придумал.
Спасибо Вам большое за ответы на все мои вопросы.
С уважением.

Автор - Никанор
Дата добавления - 08.01.2019 в 16:10
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор, поиск значений в диапазоне ЕСЛИ НЕ ПУСТАЯ ячейка (Формулы/Formulas)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

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