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

Вход

Регистрация

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

 

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

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

Excel 2007
Здравствуйте уважаемые товарищи помогающие!
Поздравляю всех с Новым годом!
Желаю здоровья, счастья исполнение всех желаний и всего самого наилучшего!
Большое спасибо всем за то, что Вы тратите своё время и помогаете другим решать вопросы на форуме.


Полное название темы, которое не поместилось в окно написания темы:
«Выбор, поиск значений в диапазоне с условием, ЕСЛИ не пустая ячейка в диапазоне и перенос этих значений в одну ячейку и в столбец»


У меня вопрос.
Пример в прикреплённом файле.


Дано:в диапазоне ячеек B14:B47 – «Фамилия имя отчество» сотрудников и другие данные. Фамилия имя отчество полностью без сокращений. В этих ячейках от 3-ёх до 5-ти слов. Больше 3-ёх слов
В диапазоне ячеек C14:C48 – «Дата приёма» сотрудника на работу в текущем месяце. Диапазон этих ячеек может быть заполнен, а может быть и пустой.
В диапазоне ячеек D14:D48 – «Дата увольнения» сотрудника в текущем месяце. Диапазон этих ячеек может быть заполнен, а может быть и пустой.


Необходимо – Как должно быть:
1-ый вариант написать в строчку полностью фамилия имя, отчество:
Написать формулу в ячейку D95 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона C14:C48 – «Дата приёма» то в ячейке D95 должны быть Фамилия имя отчество полностью принятых сотрудников. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка С19, то в ячейке D95 должно быть значение ячейки В19: Фамилия имя отчество полностью, через запятую.
А если диапазон ячеек C14:C48 пустой – не заполнен, то ячейка D95 должна быть ПУСТОЙ.


Написать формулу в ячейку D96 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона D14:D48 – «Дата увольнения» то в ячейке D96 должны быть Фамилия имя отчество полностью принятых сотрудников. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка D25, то в ячейке D96 должно быть значение ячейки В25:
Фамилия имя отчество полностью, через запятую.
А если диапазон ячеек D14:D48 пустой – не заполнен, то ячейка D96 должна быть ПУСТОЙ.


Формулу в ячейку D96 я, наверное, смогу сам написать на основе формулы из ячейки D95. Там просто надо будет выбрать другой диапазон.

2-ой вариант написать в строчку фамилия инициалы:
Написать формулу в ячейку D103 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона C14:C48 – «Дата приёма» то в ячейке D103 должны быть Фамилия полностью и инициалы сокращённо. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка С19, то в ячейке D103 должно быть значение ячейки В19:
Фамилия инициалы, через запятую.
А если диапазон ячеек C14:C48 пустой – не заполнен, то ячейка D103 должна быть ПУСТОЙ.


Написать формулу в ячейку D104 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона D14:D48 – «Дата увольнения» то в ячейке D104 должны быть Фамилия полностью и инициалы сокращённо. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка D25, то в ячейке D104 должно быть значение ячейки В25: Фамилия инициалы, через запятую.
А если диапазон ячеек D14:D48 пустой – не заполнен, то ячейка D96 должна быть ПУСТОЙ.

Формулу в ячейку D104 я, наверное, смогу сам написать на основе формулы из ячейки D95. Там просто надо будет выбрать другой диапазон.

3-ий вариант написать в столбец полностью фамилия имя, отчество:
Написать формулы в ячейки K95:K99 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона C14:C48 – «Дата приёма» то в ячейках K95:K99 должны быть Фамилия имя отчество полностью принятых сотрудников. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка С19, то в ячейке K 95 должно быть значение ячейки В19: Фамилия имя отчество полностью.
А если диапазон ячеек C14:C48 пустой – не заполнен, то ячейки K95:K99 должна быть ПУСТЫМИ.


Формулу в диапазон ячеек X95:X99 я, наверное, смогу сам написать на основе формулы из диапазона ячеек K95:K99. Там просто надо будет выбрать другой диапазон.

4-ый вариант написать в столбец фамилия, инициалы:
Написать формулы в ячейки K104:K108 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона C14:C48 – «Дата приёма» то в ячейках K104:K108 должны быть Фамилия, инициалы принятых сотрудников. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка С19, то в ячейке K104 должно быть значение ячейки В19: Фамилия, инициалы.

А если диапазон ячеек C14:C48 пустой – не заполнен, то ячейки K95:K99 должна быть ПУСТЫМИ.


Формулу в диапазон ячеек X104:X109я, наверное, смогу сам написать на основе формулы из диапазона ячеек K104:K108. Там просто надо будет выбрать другой диапазон.

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

P. S. Пробовал самостоятельно написать формулы, но у меня не получилось. Вот я нашёл похожую тему. Там тема называется «Выбор непустых (заполненных) ячеек из массива».
Вот ссылка. https://www.planetaexcel.ru/forum....D=66508
• Там файл называется Книга1 (35).xlsx (12.58 КБ)
К сообщению приложен файл: ___________.xlsx (92.9 Kb)


Сообщение отредактировал Никанор - Среда, 02.01.2019, 17:19
 
Ответить
СообщениеЗдравствуйте уважаемые товарищи помогающие!
Поздравляю всех с Новым годом!
Желаю здоровья, счастья исполнение всех желаний и всего самого наилучшего!
Большое спасибо всем за то, что Вы тратите своё время и помогаете другим решать вопросы на форуме.


Полное название темы, которое не поместилось в окно написания темы:
«Выбор, поиск значений в диапазоне с условием, ЕСЛИ не пустая ячейка в диапазоне и перенос этих значений в одну ячейку и в столбец»


У меня вопрос.
Пример в прикреплённом файле.


Дано:в диапазоне ячеек B14:B47 – «Фамилия имя отчество» сотрудников и другие данные. Фамилия имя отчество полностью без сокращений. В этих ячейках от 3-ёх до 5-ти слов. Больше 3-ёх слов
В диапазоне ячеек C14:C48 – «Дата приёма» сотрудника на работу в текущем месяце. Диапазон этих ячеек может быть заполнен, а может быть и пустой.
В диапазоне ячеек D14:D48 – «Дата увольнения» сотрудника в текущем месяце. Диапазон этих ячеек может быть заполнен, а может быть и пустой.


Необходимо – Как должно быть:
1-ый вариант написать в строчку полностью фамилия имя, отчество:
Написать формулу в ячейку D95 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона C14:C48 – «Дата приёма» то в ячейке D95 должны быть Фамилия имя отчество полностью принятых сотрудников. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка С19, то в ячейке D95 должно быть значение ячейки В19: Фамилия имя отчество полностью, через запятую.
А если диапазон ячеек C14:C48 пустой – не заполнен, то ячейка D95 должна быть ПУСТОЙ.


Написать формулу в ячейку D96 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона D14:D48 – «Дата увольнения» то в ячейке D96 должны быть Фамилия имя отчество полностью принятых сотрудников. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка D25, то в ячейке D96 должно быть значение ячейки В25:
Фамилия имя отчество полностью, через запятую.
А если диапазон ячеек D14:D48 пустой – не заполнен, то ячейка D96 должна быть ПУСТОЙ.


Формулу в ячейку D96 я, наверное, смогу сам написать на основе формулы из ячейки D95. Там просто надо будет выбрать другой диапазон.

2-ой вариант написать в строчку фамилия инициалы:
Написать формулу в ячейку D103 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона C14:C48 – «Дата приёма» то в ячейке D103 должны быть Фамилия полностью и инициалы сокращённо. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка С19, то в ячейке D103 должно быть значение ячейки В19:
Фамилия инициалы, через запятую.
А если диапазон ячеек C14:C48 пустой – не заполнен, то ячейка D103 должна быть ПУСТОЙ.


Написать формулу в ячейку D104 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона D14:D48 – «Дата увольнения» то в ячейке D104 должны быть Фамилия полностью и инициалы сокращённо. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка D25, то в ячейке D104 должно быть значение ячейки В25: Фамилия инициалы, через запятую.
А если диапазон ячеек D14:D48 пустой – не заполнен, то ячейка D96 должна быть ПУСТОЙ.

Формулу в ячейку D104 я, наверное, смогу сам написать на основе формулы из ячейки D95. Там просто надо будет выбрать другой диапазон.

3-ий вариант написать в столбец полностью фамилия имя, отчество:
Написать формулы в ячейки K95:K99 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона C14:C48 – «Дата приёма» то в ячейках K95:K99 должны быть Фамилия имя отчество полностью принятых сотрудников. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка С19, то в ячейке K 95 должно быть значение ячейки В19: Фамилия имя отчество полностью.
А если диапазон ячеек C14:C48 пустой – не заполнен, то ячейки K95:K99 должна быть ПУСТЫМИ.


Формулу в диапазон ячеек X95:X99 я, наверное, смогу сам написать на основе формулы из диапазона ячеек K95:K99. Там просто надо будет выбрать другой диапазон.

4-ый вариант написать в столбец фамилия, инициалы:
Написать формулы в ячейки K104:K108 «Принято за отчётный месяц, лиц» c Условием:
ЕСЛИ НЕ ПУСТАЯ – заполнены ячейки диапазона C14:C48 – «Дата приёма» то в ячейках K104:K108 должны быть Фамилия, инициалы принятых сотрудников. То есть, например ЕСЛИ НЕ ПУСТАЯ – заполнена ячейка С19, то в ячейке K104 должно быть значение ячейки В19: Фамилия, инициалы.

А если диапазон ячеек C14:C48 пустой – не заполнен, то ячейки K95:K99 должна быть ПУСТЫМИ.


Формулу в диапазон ячеек X104:X109я, наверное, смогу сам написать на основе формулы из диапазона ячеек K104:K108. Там просто надо будет выбрать другой диапазон.

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

P. S. Пробовал самостоятельно написать формулы, но у меня не получилось. Вот я нашёл похожую тему. Там тема называется «Выбор непустых (заполненных) ячеек из массива».
Вот ссылка. https://www.planetaexcel.ru/forum....D=66508
• Там файл называется Книга1 (35).xlsx (12.58 КБ)

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

Excel 2010
доп.столбцы
К сообщению приложен файл: 0115951.xlsx (90.9 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщениедоп.столбцы

Автор - Nic70y
Дата добавления - 02.01.2019 в 19:44
Pelena Дата: Среда, 02.01.2019, 20:48 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
[offtop] Никанор, такой был хороший пост поначалу, хотела даже похвалить, что Вы научились писать нормальным черным шрифтом, но не успела... Смотрю, опять эта боевая раскраска :D
Читать сразу расхотелось...[/offtop]


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение[offtop] Никанор, такой был хороший пост поначалу, хотела даже похвалить, что Вы научились писать нормальным черным шрифтом, но не успела... Смотрю, опять эта боевая раскраска :D
Читать сразу расхотелось...[/offtop]

Автор - Pelena
Дата добавления - 02.01.2019 в 20:48
Nic70y Дата: Четверг, 03.01.2019, 09:09 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Читать сразу расхотелось
[offtop]а я и не читал :)
сразу файл скачал.
[/offtop]


ЮMoney 41001841029809
 
Ответить
Сообщение
Читать сразу расхотелось
[offtop]а я и не читал :)
сразу файл скачал.
[/offtop]

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

Excel 2007
Nic70y– Николай Здравствуйте!
Большое спасибо за ответ!
Такая короткая формула.
А я думал что в формуле должны присутствовать функции ИНДЕКС или НАИМЕНЬШИЙ или НАЙТИ.


Скажите, пожалуйста:
1. Первое: как написать формулы во 2-ой вариант в ячейку
в моём исходном файле ___________.xlsx(92.9 Kb) в ячейки D103 и D104; а в файле Nic70y – Николая 0115951.xlsx(90.9 Kb) это ячейки F103 и F104 что бы результат был не фамилия, имя и отчество полностью, а фамилия и инициалы.

2. Второе: как написать: 3-ий вариант в столбец в моём исходном файле ___________.xlsx(92.9 Kb) это ячейки K95:K99 и X95:X99; а в файле Nic70y – Николая 0115951.xlsx(90.9 Kb) это ячейки M95:M99 и Z95:Z99 что бы результат был полностью фамилия имя и отчество.

3. Третье: как написать 4-ый вариант в столбец формулы в моём исходном файле ___________.xlsx(92.9 Kb) в ячейки K104:K108 и X104:X108; а в файле Nic70y – Николая 0115951.xlsx(90.9 Kb) это ячейки M104:N108 и Z104:Z108 что бы результат был не фамилия, имя и отчество полностью, а фамилия и инициалы.

Pelena – Елена здравствуйте!
Никанор, такой был хороший пост поначалу, хотела даже похвалить, что Вы научились писать нормальным черным шрифтом, но не успела... Смотрю, опять эта боевая раскраска
Читать сразу расхотелось...


Раз на форуме присутствуют разные шрифты, то я выделяю, что бы было видно, где информация к теме, а где вопрос.
У меня в файле ___________.xlsx(92.9 Kb) всё кратко написано, как должно быть. Там меньше читать, и всё понятно – кратко написано.


Сообщение отредактировал Никанор - Четверг, 03.01.2019, 11:08
 
Ответить
СообщениеNic70y– Николай Здравствуйте!
Большое спасибо за ответ!
Такая короткая формула.
А я думал что в формуле должны присутствовать функции ИНДЕКС или НАИМЕНЬШИЙ или НАЙТИ.


Скажите, пожалуйста:
1. Первое: как написать формулы во 2-ой вариант в ячейку
в моём исходном файле ___________.xlsx(92.9 Kb) в ячейки D103 и D104; а в файле Nic70y – Николая 0115951.xlsx(90.9 Kb) это ячейки F103 и F104 что бы результат был не фамилия, имя и отчество полностью, а фамилия и инициалы.

2. Второе: как написать: 3-ий вариант в столбец в моём исходном файле ___________.xlsx(92.9 Kb) это ячейки K95:K99 и X95:X99; а в файле Nic70y – Николая 0115951.xlsx(90.9 Kb) это ячейки M95:M99 и Z95:Z99 что бы результат был полностью фамилия имя и отчество.

3. Третье: как написать 4-ый вариант в столбец формулы в моём исходном файле ___________.xlsx(92.9 Kb) в ячейки K104:K108 и X104:X108; а в файле Nic70y – Николая 0115951.xlsx(90.9 Kb) это ячейки M104:N108 и Z104:Z108 что бы результат был не фамилия, имя и отчество полностью, а фамилия и инициалы.

Pelena – Елена здравствуйте!
Никанор, такой был хороший пост поначалу, хотела даже похвалить, что Вы научились писать нормальным черным шрифтом, но не успела... Смотрю, опять эта боевая раскраска
Читать сразу расхотелось...


Раз на форуме присутствуют разные шрифты, то я выделяю, что бы было видно, где информация к теме, а где вопрос.
У меня в файле ___________.xlsx(92.9 Kb) всё кратко написано, как должно быть. Там меньше читать, и всё понятно – кратко написано.

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

Excel 2007
Я только что внимательно посмотрел файл Nic70y – Николая 0115951.xlsx(90.9 Kb) что там смещена исходная таблица и есть дополнительные столбцы.
А как написать все эти формулы в моём исходном файле ___________.xlsx(92.9 Kb) без дополнительных столбцов?
Может надо с использованием функций ИНДЕКС или НАИМЕНЬШИЙ или НАЙТИ?


Сообщение отредактировал Никанор - Четверг, 03.01.2019, 12:05
 
Ответить
СообщениеЯ только что внимательно посмотрел файл Nic70y – Николая 0115951.xlsx(90.9 Kb) что там смещена исходная таблица и есть дополнительные столбцы.
А как написать все эти формулы в моём исходном файле ___________.xlsx(92.9 Kb) без дополнительных столбцов?
Может надо с использованием функций ИНДЕКС или НАИМЕНЬШИЙ или НАЙТИ?

Автор - Никанор
Дата добавления - 03.01.2019 в 11:13
vikttur Дата: Четверг, 03.01.2019, 13:40 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

На Ваши сообщения смотреть ... Жуть.
Посмотрите на чужие сообщения. Сделайте выводы.


Сообщение отредактировал _Boroda_ - Четверг, 18.04.2019, 17:08
 
Ответить
СообщениеНа Ваши сообщения смотреть ... Жуть.
Посмотрите на чужие сообщения. Сделайте выводы.

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

Excel 2007
vikttur – Виктор!
Спасибо за указание на то что что указали что я неправильно оформляю сообщения.
...
А выводы я сделаю.


Сообщение отредактировал _Boroda_ - Четверг, 18.04.2019, 17:09
 
Ответить
Сообщениеvikttur – Виктор!
Спасибо за указание на то что что указали что я неправильно оформляю сообщения.
...
А выводы я сделаю.

Автор - Никанор
Дата добавления - 03.01.2019 в 14:19
Никанор Дата: Четверг, 03.01.2019, 14:45 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Я самостоятельно попробовал написать формулы.
Прикрепляю файл.
В диапазоне ячеек B117:B150 у меня формулы, протянутые вниз для столбца:
Код
=ЕСЛИ(C14<>"";B14;"")
результат этих формул Фамилия имя отчество полностью все слова со столбца В.

В диапазоне ячеек D117:D149 у меня формулы, протянутые вниз для столбца:
Код
=ЕСЛИ(C14<>"";ЕСЛИОШИБКА(ЕСЛИ(B14="";"";ЛЕВБ(B14;ПОИСК("|";ПОДСТАВИТЬ(B14;" ";"|";3))-1));B14);"")
результат этих формул Фамилия имя отчество полностью без лишних слов со столбца В

В диапазоне ячеек H117:H149 у меня формулы, протянутые вниз для столбца:
Код
=ЕСЛИ(C14<>"";ЛЕВСИМВ(B14;ПОИСК(" *";B14)-1)&" "&ПСТР(B14;ПОИСК(" *";B14)+1;1)&"."&ПСТР(B14;ПОИСК(" *";B14;ПОИСК(" *";B14)+1)+1;1)&".";"")

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

1 вопрос: как написать формулы что бы в столбце не было пропусков и все нижние строчки были заполнены?

В ячейках N117 у меня длинная формула для ячейки, что бы результат был в строчку.
Результат формулы: Фамилия имя отчество полностью с лишними словами.

В ячейках N120 у меня длинная формула для ячейки, что бы результат был в строчку.
Результат формулы: Фамилия имя отчество с инициалами без лишних слов.

В ячейках N126 у меня длинная формула для ячейки, что бы результат был в строчку.
Результат формулы: Фамилия с инициалами без лишних слов.

Только в ячейках N117, N120, N126 у меня следующие фамилии идут без пробелов.
2 Вопрос: как изменить формулы, что бы каждая новая фамилия начиналась после пробела?

3 вопрос: в ячейке N126 у меня очень длинная формула, и последние ячейки, которые принимают участие это С45 и В45 46 – 48 строки не принимают участие в этой длинной формуле Как дописать 46 – 48 строки в эту формулу?

4 вопрос: это я написал очень длинные формулы. А как написать правильные формулы?
К сообщению приложен файл: __.xlsx (92.9 Kb)


Сообщение отредактировал Никанор - Четверг, 03.01.2019, 14:47
 
Ответить
СообщениеЯ самостоятельно попробовал написать формулы.
Прикрепляю файл.
В диапазоне ячеек B117:B150 у меня формулы, протянутые вниз для столбца:
Код
=ЕСЛИ(C14<>"";B14;"")
результат этих формул Фамилия имя отчество полностью все слова со столбца В.

В диапазоне ячеек D117:D149 у меня формулы, протянутые вниз для столбца:
Код
=ЕСЛИ(C14<>"";ЕСЛИОШИБКА(ЕСЛИ(B14="";"";ЛЕВБ(B14;ПОИСК("|";ПОДСТАВИТЬ(B14;" ";"|";3))-1));B14);"")
результат этих формул Фамилия имя отчество полностью без лишних слов со столбца В

В диапазоне ячеек H117:H149 у меня формулы, протянутые вниз для столбца:
Код
=ЕСЛИ(C14<>"";ЛЕВСИМВ(B14;ПОИСК(" *";B14)-1)&" "&ПСТР(B14;ПОИСК(" *";B14)+1;1)&"."&ПСТР(B14;ПОИСК(" *";B14;ПОИСК(" *";B14)+1)+1;1)&".";"")

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

1 вопрос: как написать формулы что бы в столбце не было пропусков и все нижние строчки были заполнены?

В ячейках N117 у меня длинная формула для ячейки, что бы результат был в строчку.
Результат формулы: Фамилия имя отчество полностью с лишними словами.

В ячейках N120 у меня длинная формула для ячейки, что бы результат был в строчку.
Результат формулы: Фамилия имя отчество с инициалами без лишних слов.

В ячейках N126 у меня длинная формула для ячейки, что бы результат был в строчку.
Результат формулы: Фамилия с инициалами без лишних слов.

Только в ячейках N117, N120, N126 у меня следующие фамилии идут без пробелов.
2 Вопрос: как изменить формулы, что бы каждая новая фамилия начиналась после пробела?

3 вопрос: в ячейке N126 у меня очень длинная формула, и последние ячейки, которые принимают участие это С45 и В45 46 – 48 строки не принимают участие в этой длинной формуле Как дописать 46 – 48 строки в эту формулу?

4 вопрос: это я написал очень длинные формулы. А как написать правильные формулы?

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

Excel 2010
Цитата Никанор, 03.01.2019 в 11:13, в сообщении № 6 ()
без дополнительных столбцов?
ну так аналогично, только массивно
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)))&" ";" ";ПОВТОР(" ";136);3);131));"")
[p.s.][k95] и т.д[/p.s.]
К сообщению приложен файл: -1-.xlsx (89.7 Kb)


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 03.01.2019, 14:50
 
Ответить
Сообщение
Цитата Никанор, 03.01.2019 в 11:13, в сообщении № 6 ()
без дополнительных столбцов?
ну так аналогично, только массивно
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ИНДЕКС(B$1:B$41;НАИМЕНЬШИЙ(ЕСЛИ(C$14:C$41;СТРОКА(C$14:C$41));СТРОКА(K1)))&" ";" ";ПОВТОР(" ";136);3);131));"")
[p.s.][k95] и т.д[/p.s.]

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

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

2 вопрос: как написать в одну ячейку D95 в строчку полностью фамилия имя отчество
и в ячейку D 103 фамилия с инициалами?


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

2 вопрос: как написать в одну ячейку D95 в строчку полностью фамилия имя отчество
и в ячейку D 103 фамилия с инициалами?

Автор - Никанор
Дата добавления - 03.01.2019 в 15:04
Никанор Дата: Четверг, 03.01.2019, 17:05 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Подскажите, пожалуйста, как написать формулы, эти вопросы в моём предыдущем сообщении.
 
Ответить
СообщениеПодскажите, пожалуйста, как написать формулы, эти вопросы в моём предыдущем сообщении.

Автор - Никанор
Дата добавления - 03.01.2019 в 17:05
Nic70y Дата: Четверг, 03.01.2019, 20:24 | Сообщение № 13
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Никанор, Вы главное не суетитесь! Все будет хорошо!
У Вас один вопрос или все 4 сразу,
если все четыре сразу, то можно один из другого извлечь,
а если каждый отдельно, то это со современными средствами эксель с обязательной подпиской на 365,
либо мы переходим в vba.
А на vikttur не обижайтесь - у них там на своей "Планете" старая закалка -
они удаляют сообщения неугодных, поощряют старых нарушителей правил и тому подобное,
а тут тоже есть правила:
можно просто кратко изложить Ваши желания, не разукрашивая их,
и не писать миллион строк.
Спасибо за внимание и за понимание.
[p.s.]Отредактировал сообщение, орфография, простите человека с абсолютно средним и чуть-чуть профессиональным (но не по эксель и лит-ре) образованием[/p.s.]


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 03.01.2019, 21:05
 
Ответить
СообщениеНиканор, Вы главное не суетитесь! Все будет хорошо!
У Вас один вопрос или все 4 сразу,
если все четыре сразу, то можно один из другого извлечь,
а если каждый отдельно, то это со современными средствами эксель с обязательной подпиской на 365,
либо мы переходим в vba.
А на vikttur не обижайтесь - у них там на своей "Планете" старая закалка -
они удаляют сообщения неугодных, поощряют старых нарушителей правил и тому подобное,
а тут тоже есть правила:
можно просто кратко изложить Ваши желания, не разукрашивая их,
и не писать миллион строк.
Спасибо за внимание и за понимание.
[p.s.]Отредактировал сообщение, орфография, простите человека с абсолютно средним и чуть-чуть профессиональным (но не по эксель и лит-ре) образованием[/p.s.]

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

Excel 2007
Здравствуйте Nic70y – Николай!
Спасибо за сообщение!

Но у меня не получается
У Вас один вопрос или все 4 сразу,
если все четыре сразу, то можно один из другого извлечь,

Но у меня не получается извлечь из Вашей формулы другие формулы, что бы было:
1 вариант: в столбец фамилия и инициалы;
2 вариант: в одну ячейку полностью фамилия имя и отчество;
3 вариант: в одну ячейку в строчку фамилия и инициалы.

Пробовал – не получается.
У меня получилось сделать по другому самостоятельно только очень – очень длинные формулы.

Прикрепляю файл. Там эти формулы в ячейках:
N123 – в ячейке в строчке Фамилия имя отчество полностью без лишних слов и перед следующей фамилией ПРОБЕЛ.
N126 – в ячейке в строчке Фамилия с инициалами без лишних слов Перед следующей фамилией НЕТ ПРОБЕЛА.
N131 – в ячейке в строчке без пробелов Фамилия с инициалами без лишних слов после одной фамилии инициалы
и перед следующей фамилией ПРОБЕЛ, после точки перед инициалом отчества НЕТ ПРОБЕЛА.
N136 – в ячейке в строчке без пробелов Фамилия с инициалами без лишних слов после одной фамилии инициалы
и перед следующей фамилией ПРОБЕЛ, после точки перед инициалом отчеством ЕСТЬ ПРОБЕЛ.

Вопрос: как написать короткие формулы исходя из формул Nic70y – Николая?
1 вариант: в столбец фамилия и инициалы – не полностью имя и отчество;
2 вариант: в одну ячейку полностью фамилия имя и отчество;
3 вариант: в одну ячейку в строчку фамилия и инициалы – не полностью имя и отчество.
К сообщению приложен файл: 4159420.xlsx (94.6 Kb)


Сообщение отредактировал Никанор - Пятница, 04.01.2019, 11:40
 
Ответить
СообщениеЗдравствуйте Nic70y – Николай!
Спасибо за сообщение!

Но у меня не получается
У Вас один вопрос или все 4 сразу,
если все четыре сразу, то можно один из другого извлечь,

Но у меня не получается извлечь из Вашей формулы другие формулы, что бы было:
1 вариант: в столбец фамилия и инициалы;
2 вариант: в одну ячейку полностью фамилия имя и отчество;
3 вариант: в одну ячейку в строчку фамилия и инициалы.

Пробовал – не получается.
У меня получилось сделать по другому самостоятельно только очень – очень длинные формулы.

Прикрепляю файл. Там эти формулы в ячейках:
N123 – в ячейке в строчке Фамилия имя отчество полностью без лишних слов и перед следующей фамилией ПРОБЕЛ.
N126 – в ячейке в строчке Фамилия с инициалами без лишних слов Перед следующей фамилией НЕТ ПРОБЕЛА.
N131 – в ячейке в строчке без пробелов Фамилия с инициалами без лишних слов после одной фамилии инициалы
и перед следующей фамилией ПРОБЕЛ, после точки перед инициалом отчества НЕТ ПРОБЕЛА.
N136 – в ячейке в строчке без пробелов Фамилия с инициалами без лишних слов после одной фамилии инициалы
и перед следующей фамилией ПРОБЕЛ, после точки перед инициалом отчеством ЕСТЬ ПРОБЕЛ.

Вопрос: как написать короткие формулы исходя из формул Nic70y – Николая?
1 вариант: в столбец фамилия и инициалы – не полностью имя и отчество;
2 вариант: в одну ячейку полностью фамилия имя и отчество;
3 вариант: в одну ячейку в строчку фамилия и инициалы – не полностью имя и отчество.

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

Excel 2010
Код
=K104&ЕСЛИ(K105<>"";", "&K105;"")&ЕСЛИ(K106<>"";", "&K106;"")&ЕСЛИ(K107<>"";", "&K107;"")&ЕСЛИ(K108<>"";", "&K108;"")
Код
=ЕСЛИ(K95<>"";СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(K95;" ";ПОВТОР(" ";96));95))&ПСТР(K95;ПОИСК(" ";K95);2)&"."&ЛЕВБ(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(K95;" ";ПОВТОР(" ";96));95)))&".";"")
К сообщению приложен файл: -1-1-.xlsx (90.2 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=K104&ЕСЛИ(K105<>"";", "&K105;"")&ЕСЛИ(K106<>"";", "&K106;"")&ЕСЛИ(K107<>"";", "&K107;"")&ЕСЛИ(K108<>"";", "&K108;"")
Код
=ЕСЛИ(K95<>"";СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(K95;" ";ПОВТОР(" ";96));95))&ПСТР(K95;ПОИСК(" ";K95);2)&"."&ЛЕВБ(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(K95;" ";ПОВТОР(" ";96));95)))&".";"")

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

Excel 2007
Nic70y – Николай!
Большое спасибо за ответ и правильные формулы!
В Вашем файле формулы "подтягиваются" из промежуточных результатов – из других таблиц.


А как сделать такие же результаты были и что бы ИСКАЛИ – выбирали из диапазона ячеек B14:B48?
Что бы в ячейках D95 и D96 результат брался из диапазона ячеек B14:B48.
Что бы в ячейках D103 и D104 результат брался из диапазона ячеек B14:B48.
Что бы в ячейках K104:K108 результат брался из диапазона ячеек B14:B48.
Что бы в ячейках X104:X108 результат брался из диапазона ячеек B14:B48.


Объясню почему.
У меня рабочая таблица, в которой я хочу что бы вёлся ПОИСК из диапазона ячеек B14:B48, а для дополнительных таблиц нет места.


Сообщение отредактировал Никанор - Суббота, 05.01.2019, 11:56
 
Ответить
СообщениеNic70y – Николай!
Большое спасибо за ответ и правильные формулы!
В Вашем файле формулы "подтягиваются" из промежуточных результатов – из других таблиц.


А как сделать такие же результаты были и что бы ИСКАЛИ – выбирали из диапазона ячеек B14:B48?
Что бы в ячейках D95 и D96 результат брался из диапазона ячеек B14:B48.
Что бы в ячейках D103 и D104 результат брался из диапазона ячеек B14:B48.
Что бы в ячейках K104:K108 результат брался из диапазона ячеек B14:B48.
Что бы в ячейках X104:X108 результат брался из диапазона ячеек B14:B48.


Объясню почему.
У меня рабочая таблица, в которой я хочу что бы вёлся ПОИСК из диапазона ячеек B14:B48, а для дополнительных таблиц нет места.

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

Excel 2010
Обычными формулами (без подписки на офис 365 + Excel 2016-19) это не получится (я о сцепке).
поэтому и спрашивал это отдельные вопросы или все в куче.
могу написать макрос (если устроит)
только позже - ухожу в магаз, а там снег и ветер (вдруг унесен, а мелочи в карманах нет)
думаю, сегодня вернусь.


ЮMoney 41001841029809
 
Ответить
СообщениеОбычными формулами (без подписки на офис 365 + Excel 2016-19) это не получится (я о сцепке).
поэтому и спрашивал это отдельные вопросы или все в куче.
могу написать макрос (если устроит)
только позже - ухожу в магаз, а там снег и ветер (вдруг унесен, а мелочи в карманах нет)
думаю, сегодня вернусь.

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

Excel 2007
Nic70y – Николай спасибо за ответ!
это отдельные вопросы или все в куче.

Отвечаю: Это отдельные вопросы.
Спасибо за предложение написать макрос, но мне нужно формулами, так как рабочий файл без макросов.
У меня это получилось методом долгих проб написать очень-очень длинную формулу, что бы результат был в одной ячейке
с разными вариантами.
Это в моём файле
4159420.xlsx(94.6 Kb) в Сообщение № 14 Дата: Пятница, 04.01.2019, 11:32.

Только там очень длинные формулы и мне бы хотелось это написать короче что то наподобие функций ИНДЕКС, ПОИСК
или другой выбор из диапазона ячеек B14:B48.
Я просто хотел посмотреть на результаты и выбрать какую то одну формулу в свой рабочий файл.


То есть осталось написать в столбец что бы было искало из диапазона B14:B48;
1. Фамилия и инициалы – не полностью имя и отчество;
2. В одну ячейку в строчку полностью фамилия имя и отчество;
3. В одну ячейку в строчку фамилия и инициалы – не полностью имя и отчество.


2-ой и 3-ий варианты я самостоятельно как писал Выше, сделал в файле 4159420.xlsx(94.6 Kb) с помощью функции & «сцепить».
Только там очень – очень длинная формула.


Николай!
Если у Вас будет время и желание посмотрите возможно ли выполнить мои условия, а если невозможно это с помощью формул,
то пусть остаётся как есть.
И если можно напишите пожалуйста ответ – возможно ли это сделать с помощью формул.

Вы мне и так очень помогли.
С уважением, и наилучшими пожеланиями.


Сообщение отредактировал Никанор - Суббота, 05.01.2019, 12:01
 
Ответить
СообщениеNic70y – Николай спасибо за ответ!
это отдельные вопросы или все в куче.

Отвечаю: Это отдельные вопросы.
Спасибо за предложение написать макрос, но мне нужно формулами, так как рабочий файл без макросов.
У меня это получилось методом долгих проб написать очень-очень длинную формулу, что бы результат был в одной ячейке
с разными вариантами.
Это в моём файле
4159420.xlsx(94.6 Kb) в Сообщение № 14 Дата: Пятница, 04.01.2019, 11:32.

Только там очень длинные формулы и мне бы хотелось это написать короче что то наподобие функций ИНДЕКС, ПОИСК
или другой выбор из диапазона ячеек B14:B48.
Я просто хотел посмотреть на результаты и выбрать какую то одну формулу в свой рабочий файл.


То есть осталось написать в столбец что бы было искало из диапазона B14:B48;
1. Фамилия и инициалы – не полностью имя и отчество;
2. В одну ячейку в строчку полностью фамилия имя и отчество;
3. В одну ячейку в строчку фамилия и инициалы – не полностью имя и отчество.


2-ой и 3-ий варианты я самостоятельно как писал Выше, сделал в файле 4159420.xlsx(94.6 Kb) с помощью функции & «сцепить».
Только там очень – очень длинная формула.


Николай!
Если у Вас будет время и желание посмотрите возможно ли выполнить мои условия, а если невозможно это с помощью формул,
то пусть остаётся как есть.
И если можно напишите пожалуйста ответ – возможно ли это сделать с помощью формул.

Вы мне и так очень помогли.
С уважением, и наилучшими пожеланиями.

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

Excel 2010
единственное, что могу рекомендовать, это
ctrl+h
найти:<>""
заменить на: (здесь ни чего не писать)

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


ЮMoney 41001841029809
 
Ответить
Сообщениеединственное, что могу рекомендовать, это
ctrl+h
найти:<>""
заменить на: (здесь ни чего не писать)

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

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

Excel 2007
Позвольте задать вопрос, который касается этой темы.
Я написал очень длинную формулу, которая учитывает ячейки с 14-ой по 45.
В этом примере в Строке формул «Знаков с пробелами» 3 230.
В этом примере, если стать в Строку формул – в Строке формул все ячейки выделены разным цветным шрифтом.
Рисунок 1.




А если написать в Строке формулу, которая учитывает ячейки с 14-ой по 48.
В этом примере в Строке формул «Знаков с пробелами» 3 533.
В этом примере, если стать в Строку формул – цветным выделены только скобки. А имена ячеек цвет шрифта Авто.
Рисунок 2.




Вопрос: Почему в 1-ом примере ячейки выделены цветным шрифтом, а во 2-ом примере ячейки цвет шрифта Авто?
Будет ли правильно «работать» такая формула?
Или всегда надо, что бы, когда стать в Строку формул имена ячеек были цветными?
К сообщению приложен файл: 5659792.gif (59.2 Kb) · 6106792.gif (56.1 Kb)


Сообщение отредактировал Никанор - Суббота, 05.01.2019, 11:57
 
Ответить
СообщениеПозвольте задать вопрос, который касается этой темы.
Я написал очень длинную формулу, которая учитывает ячейки с 14-ой по 45.
В этом примере в Строке формул «Знаков с пробелами» 3 230.
В этом примере, если стать в Строку формул – в Строке формул все ячейки выделены разным цветным шрифтом.
Рисунок 1.




А если написать в Строке формулу, которая учитывает ячейки с 14-ой по 48.
В этом примере в Строке формул «Знаков с пробелами» 3 533.
В этом примере, если стать в Строку формул – цветным выделены только скобки. А имена ячеек цвет шрифта Авто.
Рисунок 2.




Вопрос: Почему в 1-ом примере ячейки выделены цветным шрифтом, а во 2-ом примере ячейки цвет шрифта Авто?
Будет ли правильно «работать» такая формула?
Или всегда надо, что бы, когда стать в Строку формул имена ячеек были цветными?

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

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