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

Вход

Регистрация

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

 

= Мир MS Excel/Не протягивается формула со сцепкой ячеек - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Не протягивается формула со сцепкой ячеек (Формулы/Formulas)
Не протягивается формула со сцепкой ячеек
Андрей2911 Дата: Суббота, 05.11.2022, 13:11 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Здравствуйте уважаемы форумчане!

Прошу помощи, т.к. не нашёл решения моей проблемы ни на этом сайте, ни в поисковиках.

Суть проблемы:

По работе приходится сдавать отчёты МХ для бухгалтерии. В прилагаемом файле есть 3 компании, на каждую есть страница Реестр и страница Акт. Данные вручную заносятся в страницу Реестр. Нужно сделать так, что бы определённые значения (Сцеплённые наименования и артикулы: например уплотнение, патрубки, воронки... + их артикли автоматом переносились в определённую ячейку страницы Акт. Формулу я уже подобрал, но она не протягивается за правый нижний уголок, когда выбираешь диапазон ячеек. Тоже самое и со столбцом Количество на странице Акт. Пробовал и с формулой Сцеп, и с амперсандом - никак не получается протянуть... Наименования часто меняются и добавляются, поэтому нужна именно протяжка формулы, т.к. менять букву столбца руками очень долго. Пока пытаюсь только для Евразии, на остальные компании всё перенесу потом.

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

Буду очень благодарен за помощь или совет в какую сторону копать дальше.
К сообщению приложен файл: 2022.11.05__148.xlsx (336.6 Kb)
 
Ответить
СообщениеЗдравствуйте уважаемы форумчане!

Прошу помощи, т.к. не нашёл решения моей проблемы ни на этом сайте, ни в поисковиках.

Суть проблемы:

По работе приходится сдавать отчёты МХ для бухгалтерии. В прилагаемом файле есть 3 компании, на каждую есть страница Реестр и страница Акт. Данные вручную заносятся в страницу Реестр. Нужно сделать так, что бы определённые значения (Сцеплённые наименования и артикулы: например уплотнение, патрубки, воронки... + их артикли автоматом переносились в определённую ячейку страницы Акт. Формулу я уже подобрал, но она не протягивается за правый нижний уголок, когда выбираешь диапазон ячеек. Тоже самое и со столбцом Количество на странице Акт. Пробовал и с формулой Сцеп, и с амперсандом - никак не получается протянуть... Наименования часто меняются и добавляются, поэтому нужна именно протяжка формулы, т.к. менять букву столбца руками очень долго. Пока пытаюсь только для Евразии, на остальные компании всё перенесу потом.

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

Буду очень благодарен за помощь или совет в какую сторону копать дальше.

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

Excel 2010
так
Код
=СЖПРОБЕЛЫ(ИНДЕКС(Евразия!$I$5:$CH$5;СТРОКА(A1))&" "&ИНДЕКС(Евразия!$I$6:$CH$6;СТРОКА(A1)))
надо?
К сообщению приложен файл: 17.xlsx (347.4 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщениетак
Код
=СЖПРОБЕЛЫ(ИНДЕКС(Евразия!$I$5:$CH$5;СТРОКА(A1))&" "&ИНДЕКС(Евразия!$I$6:$CH$6;СТРОКА(A1)))
надо?

Автор - Nic70y
Дата добавления - 05.11.2022 в 13:21
Gustav Дата: Суббота, 05.11.2022, 14:59 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Теоретически можно формулу массива применить, развернутую на 90 градусов (по столбцу) с помощью функции ТРАНСП:
Код
=ТРАНСП(Евразия!I$6:Евразия!N$6&" "&Евразия!I$5:Евразия!N$5)

Вводится она так (на примере листа "МХ-3 Евразия" и не совсем того диапазона M28:M33):
1. Выделяем диапазон M28:M33.
2. Очищаем его ячейки клавишей Delete.
3. Не снимая выделения, в верхнюю ячейку M28 вставляем формулу.
4. Завершаем ввод комбинацией Ctrl+Shift+Enter.

Да, я вставил в примере формулу в столбец "Примечание", потому что это нормальная колонка, состоящая из одной колонки (как бы смешно это не звучало). Это всё потому, что у вас колонка "ТМЦ" бланка состоит из "вселенского зла" - объединенных ячеек (B28:C33), в которые, к сожалению, нельзя поместить формулу массива описанным способом. Но ее всегда можно поместить, например, в скрытый столбец за пределами области печати бланка, а затем в диапазоне сослаться на ячейки этого столбца простейшими (протягиваемыми) формулами.
Код
=N28

Я дополнительно поместил формулу с ТРАНСП и в столбец N в ячейки N28:N33 и всему столбцу N задал цвет шрифта "Белый, Фон 1, более темный оттенок 35%". Этот цвет практически скрыл текст ячеек в сером фоне непечатной области. Но при желании можно скрыть столбец N и по-настоящему, установив ему нулевую ширину.

Действий и подробностей многовато, согласен, и, наверное, этим своим советом быстро и напрямую я Вам не помогу, но, возможно, разовью фантазию на будущее.
К сообщению приложен файл: Transp.xlsx (145.5 Kb)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеТеоретически можно формулу массива применить, развернутую на 90 градусов (по столбцу) с помощью функции ТРАНСП:
Код
=ТРАНСП(Евразия!I$6:Евразия!N$6&" "&Евразия!I$5:Евразия!N$5)

Вводится она так (на примере листа "МХ-3 Евразия" и не совсем того диапазона M28:M33):
1. Выделяем диапазон M28:M33.
2. Очищаем его ячейки клавишей Delete.
3. Не снимая выделения, в верхнюю ячейку M28 вставляем формулу.
4. Завершаем ввод комбинацией Ctrl+Shift+Enter.

Да, я вставил в примере формулу в столбец "Примечание", потому что это нормальная колонка, состоящая из одной колонки (как бы смешно это не звучало). Это всё потому, что у вас колонка "ТМЦ" бланка состоит из "вселенского зла" - объединенных ячеек (B28:C33), в которые, к сожалению, нельзя поместить формулу массива описанным способом. Но ее всегда можно поместить, например, в скрытый столбец за пределами области печати бланка, а затем в диапазоне сослаться на ячейки этого столбца простейшими (протягиваемыми) формулами.
Код
=N28

Я дополнительно поместил формулу с ТРАНСП и в столбец N в ячейки N28:N33 и всему столбцу N задал цвет шрифта "Белый, Фон 1, более темный оттенок 35%". Этот цвет практически скрыл текст ячеек в сером фоне непечатной области. Но при желании можно скрыть столбец N и по-настоящему, установив ему нулевую ширину.

Действий и подробностей многовато, согласен, и, наверное, этим своим советом быстро и напрямую я Вам не помогу, но, возможно, разовью фантазию на будущее.

Автор - Gustav
Дата добавления - 05.11.2022 в 14:59
Gustav Дата: Суббота, 05.11.2022, 16:11 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Ну, и, конечно, в дополнение ко всему, сказанному выше, есть же еще функция ДВССЫЛ, с помощью которой можно соорудить легко-протягиваемые формулы, например, для ячейки B28 листа "МХ-3 Евразия" формула может быть такой:
Код
=ДВССЫЛ("Евразия!"&АДРЕС(6;СТРОКА()-19))&" "&ДВССЫЛ("Евразия!"&АДРЕС(5;СТРОКА()-19))

, где в строке 28 индекс столбца, рассчитанный по формуле СТРОКА()-19, будет равен 28-19 = 9, что соответствует столбцу I:I.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Суббота, 05.11.2022, 16:16
 
Ответить
СообщениеНу, и, конечно, в дополнение ко всему, сказанному выше, есть же еще функция ДВССЫЛ, с помощью которой можно соорудить легко-протягиваемые формулы, например, для ячейки B28 листа "МХ-3 Евразия" формула может быть такой:
Код
=ДВССЫЛ("Евразия!"&АДРЕС(6;СТРОКА()-19))&" "&ДВССЫЛ("Евразия!"&АДРЕС(5;СТРОКА()-19))

, где в строке 28 индекс столбца, рассчитанный по формуле СТРОКА()-19, будет равен 28-19 = 9, что соответствует столбцу I:I.

Автор - Gustav
Дата добавления - 05.11.2022 в 16:11
Андрей2911 Дата: Воскресенье, 06.11.2022, 13:26 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Большое спасибо всем откликнувшимся!

Проблема решена!

P.S. Я то думал что разбираюсь в Excel...
 
Ответить
СообщениеБольшое спасибо всем откликнувшимся!

Проблема решена!

P.S. Я то думал что разбираюсь в Excel...

Автор - Андрей2911
Дата добавления - 06.11.2022 в 13:26
Gustav Дата: Воскресенье, 06.11.2022, 15:40 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Андрей2911, 06.11.2022 в 13:26, в сообщении № 5 ()
Проблема решена!

А у нас еще не всё! ("У нас с собой было"). Поэтому, как говорили Шурику в "Кавказской пленнице": "Слушай третий тост!" (в смысле - совет)

Как было показано выше, с помощью использования ДВССЫЛ можно приспособить формулу к протягиванию. Однако сама формула при этом может быть весьма неинформативна. Глядя на предыдущую формулу, трудно сразу сказать, какими ячейками она оперирует. И если ссылки на строку в виде чисел "6" и "5" еще более-менее понятны, то понять какой столбец скрывается за формулой СТРОКА()-19 - это надо напрячься (чего стоит только одна сбивающая с толку функция СТРОКА() при вычислении СТОЛБЦА!)

Выход - в способе построения формул с помощью других формул. Посмотрим, как это можно сделать всё для того же диапазона B28:B33 листа "МХ-3 Евразия". Для этого:

1. Временно добавим в книгу новый пустой рабочий лист.

2. В ячейку B28 нового листа введем формулу (для удобства формирования ссылок будем работать на новом листе с точно таким же "одноименным" диапазоном B28:B33):
Код
="=Евразия!"&АДРЕС(6;СТРОКА()-19)&"&"" ""&"&"Евразия!"&АДРЕС(5;СТРОКА()-19)

Т.е. это практически предыдущая формула, только без ДВССЫЛ.

3. Скопируем формулу во все ячейки диапазона B28:B33 нового листа. Получится такая картина:
[vba]
Код
=Евразия!$I$6&" "&Евразия!$I$5    
=Евразия!$J$6&" "&Евразия!$J$5    
=Евразия!$K$6&" "&Евразия!$K$5    
=Евразия!$L$6&" "&Евразия!$L$5    
=Евразия!$M$6&" "&Евразия!$M$5    
=Евразия!$N$6&" "&Евразия!$N$5    
[/vba]
4. На новом листе выделим диапазон B28:C33 (т.е. уже двухколоночный) и выполним команду "Объединить по строкам". Этот шаг специфичен для данной конкретной задачи из-за того, что на лист "МХ-3 Евразия" формулы придётся вставлять в объединенные ячейки. Если бы вставка делалась в нормальный одноколоночный диапазон, то этого шага 4 в алгоритме не было бы.

5. Копируем объединенные ячейки диапазона B28:B33 нового листа, идём на лист "МХ-3 Евразия" и делаем в аналогичный диапазон B28:B33 Специальную вставку "Вставить значения". После этого в ячейках листа "МХ-3 Евразия" оказываются тексты формул, созданные на новом листе. Остается их оживить, чтобы они заработали как формулы.

6. "Оживление формул". При выделенном диапазоне B28:B33 листа "МХ-3 Евразия" вызываем по Ctrl+H окно "Найти и заменить". В поля "Найти" и "Заменить на" вводим один и тот же символ: = (знак равенства) и жмём кнопку "Заменить все".

7. Всё! В ячейках B28:B33 листа "МХ-3 Евразия" - формулы с простыми нормальными ссылками на ячейки. Временно добавленный в пункте 1 лист можно удалять.

[p.s.]На похожую тему я уже рассуждал несколько месяцев назад:[/p.s.]
http://www.excelworld.ru/forum/2-50229-328557-16-1658155108
Только там вместо окна "Найти и заменить" использовался вояж в редактор VBA c выполнением в Окне отладки одного единственного оператора:
[vba]
Код
Selection.FormulaLocal = Selection.Value
[/vba]
Что ж, тоже вариант! А в сумме прямо лекция по специфическим протягиваниям и формульному оживляжу ;)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 06.11.2022, 15:57
 
Ответить
Сообщение
Цитата Андрей2911, 06.11.2022 в 13:26, в сообщении № 5 ()
Проблема решена!

А у нас еще не всё! ("У нас с собой было"). Поэтому, как говорили Шурику в "Кавказской пленнице": "Слушай третий тост!" (в смысле - совет)

Как было показано выше, с помощью использования ДВССЫЛ можно приспособить формулу к протягиванию. Однако сама формула при этом может быть весьма неинформативна. Глядя на предыдущую формулу, трудно сразу сказать, какими ячейками она оперирует. И если ссылки на строку в виде чисел "6" и "5" еще более-менее понятны, то понять какой столбец скрывается за формулой СТРОКА()-19 - это надо напрячься (чего стоит только одна сбивающая с толку функция СТРОКА() при вычислении СТОЛБЦА!)

Выход - в способе построения формул с помощью других формул. Посмотрим, как это можно сделать всё для того же диапазона B28:B33 листа "МХ-3 Евразия". Для этого:

1. Временно добавим в книгу новый пустой рабочий лист.

2. В ячейку B28 нового листа введем формулу (для удобства формирования ссылок будем работать на новом листе с точно таким же "одноименным" диапазоном B28:B33):
Код
="=Евразия!"&АДРЕС(6;СТРОКА()-19)&"&"" ""&"&"Евразия!"&АДРЕС(5;СТРОКА()-19)

Т.е. это практически предыдущая формула, только без ДВССЫЛ.

3. Скопируем формулу во все ячейки диапазона B28:B33 нового листа. Получится такая картина:
[vba]
Код
=Евразия!$I$6&" "&Евразия!$I$5    
=Евразия!$J$6&" "&Евразия!$J$5    
=Евразия!$K$6&" "&Евразия!$K$5    
=Евразия!$L$6&" "&Евразия!$L$5    
=Евразия!$M$6&" "&Евразия!$M$5    
=Евразия!$N$6&" "&Евразия!$N$5    
[/vba]
4. На новом листе выделим диапазон B28:C33 (т.е. уже двухколоночный) и выполним команду "Объединить по строкам". Этот шаг специфичен для данной конкретной задачи из-за того, что на лист "МХ-3 Евразия" формулы придётся вставлять в объединенные ячейки. Если бы вставка делалась в нормальный одноколоночный диапазон, то этого шага 4 в алгоритме не было бы.

5. Копируем объединенные ячейки диапазона B28:B33 нового листа, идём на лист "МХ-3 Евразия" и делаем в аналогичный диапазон B28:B33 Специальную вставку "Вставить значения". После этого в ячейках листа "МХ-3 Евразия" оказываются тексты формул, созданные на новом листе. Остается их оживить, чтобы они заработали как формулы.

6. "Оживление формул". При выделенном диапазоне B28:B33 листа "МХ-3 Евразия" вызываем по Ctrl+H окно "Найти и заменить". В поля "Найти" и "Заменить на" вводим один и тот же символ: = (знак равенства) и жмём кнопку "Заменить все".

7. Всё! В ячейках B28:B33 листа "МХ-3 Евразия" - формулы с простыми нормальными ссылками на ячейки. Временно добавленный в пункте 1 лист можно удалять.

[p.s.]На похожую тему я уже рассуждал несколько месяцев назад:[/p.s.]
http://www.excelworld.ru/forum/2-50229-328557-16-1658155108
Только там вместо окна "Найти и заменить" использовался вояж в редактор VBA c выполнением в Окне отладки одного единственного оператора:
[vba]
Код
Selection.FormulaLocal = Selection.Value
[/vba]
Что ж, тоже вариант! А в сумме прямо лекция по специфическим протягиваниям и формульному оживляжу ;)

Автор - Gustav
Дата добавления - 06.11.2022 в 15:40
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Не протягивается формула со сцепкой ячеек (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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