Прошу помощи, т.к. не нашёл решения моей проблемы ни на этом сайте, ни в поисковиках.
Суть проблемы:
По работе приходится сдавать отчёты МХ для бухгалтерии. В прилагаемом файле есть 3 компании, на каждую есть страница Реестр и страница Акт. Данные вручную заносятся в страницу Реестр. Нужно сделать так, что бы определённые значения (Сцеплённые наименования и артикулы: например уплотнение, патрубки, воронки... + их артикли автоматом переносились в определённую ячейку страницы Акт. Формулу я уже подобрал, но она не протягивается за правый нижний уголок, когда выбираешь диапазон ячеек. Тоже самое и со столбцом Количество на странице Акт. Пробовал и с формулой Сцеп, и с амперсандом - никак не получается протянуть... Наименования часто меняются и добавляются, поэтому нужна именно протяжка формулы, т.к. менять букву столбца руками очень долго. Пока пытаюсь только для Евразии, на остальные компании всё перенесу потом.
Так же есть ограничения: нельзя изменять форму листов, ячеек и т.д., и применять макросы, т.к. форма утверждена начальством, а макросы блокируются политикой безопасности компании.
Буду очень благодарен за помощь или совет в какую сторону копать дальше.
Здравствуйте уважаемы форумчане!
Прошу помощи, т.к. не нашёл решения моей проблемы ни на этом сайте, ни в поисковиках.
Суть проблемы:
По работе приходится сдавать отчёты МХ для бухгалтерии. В прилагаемом файле есть 3 компании, на каждую есть страница Реестр и страница Акт. Данные вручную заносятся в страницу Реестр. Нужно сделать так, что бы определённые значения (Сцеплённые наименования и артикулы: например уплотнение, патрубки, воронки... + их артикли автоматом переносились в определённую ячейку страницы Акт. Формулу я уже подобрал, но она не протягивается за правый нижний уголок, когда выбираешь диапазон ячеек. Тоже самое и со столбцом Количество на странице Акт. Пробовал и с формулой Сцеп, и с амперсандом - никак не получается протянуть... Наименования часто меняются и добавляются, поэтому нужна именно протяжка формулы, т.к. менять букву столбца руками очень долго. Пока пытаюсь только для Евразии, на остальные компании всё перенесу потом.
Так же есть ограничения: нельзя изменять форму листов, ячеек и т.д., и применять макросы, т.к. форма утверждена начальством, а макросы блокируются политикой безопасности компании.
Буду очень благодарен за помощь или совет в какую сторону копать дальше.Андрей2911
Вводится она так (на примере листа "МХ-3 Евразия" и не совсем того диапазона M28:M33): 1. Выделяем диапазон M28:M33. 2. Очищаем его ячейки клавишей Delete. 3. Не снимая выделения, в верхнюю ячейку M28 вставляем формулу. 4. Завершаем ввод комбинацией Ctrl+Shift+Enter.
Да, я вставил в примере формулу в столбец "Примечание", потому что это нормальная колонка, состоящая из одной колонки (как бы смешно это не звучало). Это всё потому, что у вас колонка "ТМЦ" бланка состоит из "вселенского зла" - объединенных ячеек (B28:C33), в которые, к сожалению, нельзя поместить формулу массива описанным способом. Но ее всегда можно поместить, например, в скрытый столбец за пределами области печати бланка, а затем в диапазоне сослаться на ячейки этого столбца простейшими (протягиваемыми) формулами.
Код
=N28
Я дополнительно поместил формулу с ТРАНСП и в столбец N в ячейки N28:N33 и всему столбцу N задал цвет шрифта "Белый, Фон 1, более темный оттенок 35%". Этот цвет практически скрыл текст ячеек в сером фоне непечатной области. Но при желании можно скрыть столбец N и по-настоящему, установив ему нулевую ширину.
Действий и подробностей многовато, согласен, и, наверное, этим своим советом быстро и напрямую я Вам не помогу, но, возможно, разовью фантазию на будущее.
Теоретически можно формулу массива применить, развернутую на 90 градусов (по столбцу) с помощью функции ТРАНСП:
Вводится она так (на примере листа "МХ-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
Ну, и, конечно, в дополнение ко всему, сказанному выше, есть же еще функция ДВССЫЛ, с помощью которой можно соорудить легко-протягиваемые формулы, например, для ячейки B28 листа "МХ-3 Евразия" формула может быть такой:
, где в строке 28 индекс столбца, рассчитанный по формуле СТРОКА()-19, будет равен 28-19 = 9, что соответствует столбцу I:I.
Ну, и, конечно, в дополнение ко всему, сказанному выше, есть же еще функция ДВССЫЛ, с помощью которой можно соорудить легко-протягиваемые формулы, например, для ячейки B28 листа "МХ-3 Евразия" формула может быть такой:
А у нас еще не всё! ("У нас с собой было"). Поэтому, как говорили Шурику в "Кавказской пленнице": "Слушай третий тост!" (в смысле - совет)
Как было показано выше, с помощью использования ДВССЫЛ можно приспособить формулу к протягиванию. Однако сама формула при этом может быть весьма неинформативна. Глядя на предыдущую формулу, трудно сразу сказать, какими ячейками она оперирует. И если ссылки на строку в виде чисел "6" и "5" еще более-менее понятны, то понять какой столбец скрывается за формулой СТРОКА()-19 - это надо напрячься (чего стоит только одна сбивающая с толку функция СТРОКА() при вычислении СТОЛБЦА!)
Выход - в способе построения формул с помощью других формул. Посмотрим, как это можно сделать всё для того же диапазона B28:B33 листа "МХ-3 Евразия". Для этого:
1. Временно добавим в книгу новый пустой рабочий лист.
2. В ячейку B28 нового листа введем формулу (для удобства формирования ссылок будем работать на новом листе с точно таким же "одноименным" диапазоном B28:B33):
[/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] Что ж, тоже вариант! А в сумме прямо лекция по специфическим протягиваниям и формульному оживляжу
А у нас еще не всё! ("У нас с собой было"). Поэтому, как говорили Шурику в "Кавказской пленнице": "Слушай третий тост!" (в смысле - совет)
Как было показано выше, с помощью использования ДВССЫЛ можно приспособить формулу к протягиванию. Однако сама формула при этом может быть весьма неинформативна. Глядя на предыдущую формулу, трудно сразу сказать, какими ячейками она оперирует. И если ссылки на строку в виде чисел "6" и "5" еще более-менее понятны, то понять какой столбец скрывается за формулой СТРОКА()-19 - это надо напрячься (чего стоит только одна сбивающая с толку функция СТРОКА() при вычислении СТОЛБЦА!)
Выход - в способе построения формул с помощью других формул. Посмотрим, как это можно сделать всё для того же диапазона B28:B33 листа "МХ-3 Евразия". Для этого:
1. Временно добавим в книгу новый пустой рабочий лист.
2. В ячейку B28 нового листа введем формулу (для удобства формирования ссылок будем работать на новом листе с точно таким же "одноименным" диапазоном B28:B33):
[/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
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
