Как отключить автоматическое заполнение столбцов таблицы?

[Олег*]

Словами трудно сформулировать, поэтому приложил файл.

Итак, имеется таблица, в которую вставляем формулы программно. Вставляем формулу в верхнюю ячейку столбца, а Эксель  сразу же автоматически заполняет весь этот столбец этой формулой. Подскажите, пожалуйста, как бы мне эту автоматику отключить где-нибудь в настройках, а то она меня уже достала

[ZORRO2005]

Вариант1:
После того, как вставите формулу и она распростанится на весь столбец, нажмите CTRL+Z.
Больше хулиганить не будет.
Вариант2:
Правой кнопкой мыши по таблице-Таблица-Преобразовать в диапазон.

[Олег*]

Вариант1:
После того, как вставите формулу и она распростанится на весь столбец, нажмите CTRL+Z.
Больше хулиганить не будет.
Вариант2:
Правой кнопкой мыши по таблице-Таблица-Преобразовать в диапазон.

Спасибо, но мне это не подходит

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

Вариант2: Но мне таблица-то все-таки нужна, для последующей сортировки данных. Не хотелось бы ее удалять. Как вариант, можно, наверно, преобразовать в диапазон, потом вставить данные, а потом снова преобразовать в таблицу, но я думал, что есть какое-нибудь более изящное решение где-нибудь в настройках Экселя.

[Serge 007]

Как эту автоматику отключить где-нибудь в настройках?

1. Вручную:
Создаём и заполняем любую таблицу, жмём кнопку (см. картинку), удаляем таблицу
Далее можно пользоваться макросом

2. Раз уж всё-равно заполнение идёт макросом, то в коде, до того как прописываются формулы пишем так:

Код Выделить Развернуть

Application.AutoCorrect.AutoFillFormulasInLists = False

[Олег*]

Serge 007
Огромное спасибо!!!
Вставил в самом начале макроса:
 

Код Выделить Развернуть

Application.AutoCorrect.AutoFillFormulasInLists = False

и в самом конце:

Код Выделить Развернуть

Application.AutoCorrect.AutoFillFormulasInLists = True

Все работает превосходно! В смысле, Эксель заметно поумнел

[Олег*]

Как эту автоматику отключить где-нибудь в настройках?

1. Вручную:
Создаём и заполняем любую таблицу, жмём кнопку (см. картинку), удаляем таблицу
Далее можно пользоваться макросом

А кстати, это тоже классный вариант!
После этого Эксель уже не самоуправничает, а лишь любезно предлагает "Заполнить все ячейки данного столбца указанной формулой".

[_Boroda_]

Олег, дело в том, что таблицы в Excel для того, в частности, и созданы, чтобы в столбцах была однотипная информация (некий аналог базы в Access). Вы, конечно, можете заполнить одно поле разными формулами, но это, во-первых, "дурной тон" и, во-вторых, довольно опасно тем, что может быть такая ситуация - кто-то "провалится" в одну из ячеек , потом для выхода нажмет Ентер и формула из этой ячейки размножится по всему полю, а он и не заметит, что другие значения поменялись (даже если убрать автозаполнение, то где гарантия, что завтра Вы(или еще кто) не поставите его для другой таблицы). Так что, дело Ваше, но я бы так делать не стал.
А по поводу "Но мне таблица-то все-таки нужна, для последующей сортировки данных" - а почему не сортируете просто автофильтром?

[Олег*]

Олег, дело в том, что таблицы в Excel для того, в частности, и созданы, чтобы в столбцах была однотипная информация (некий аналог базы в Access). Вы, конечно, можете заполнить одно поле разными формулами...

Ну не совсем они "разные"... Формулы, в общем-то, одинаковые, но они "подхватывают" однотипные данные из разных ячеек, разбросанных по другому листу с определенным шагом (Данные > Из Интернета > Импорт) и вставляют их в одну таблицу. Поэтому "автоматические" формулы получаются совсем не такими, какие нужны.

но это, во-первых, "дурной тон"

Ну мне-то простительно  Я же еще только учусь

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

Никто кроме меня не "провалится" Я единственный пользователь своих же собственных Эксельных программ. А за предупреждение спасибо! Впредь буду учитывать такую потенциальную опасность. Сейчас попробовал, два раза щелкнул по одной из ячеек, появилась формула, нажал на "Enter", ничего не изменилось. Наверно, правда, из-за того, что у меня автозаполнение сейчас убрано.
А кстати, на какие кнопки надо нажать, чтобы его назад-то вернуть? Я пытался найти, но не смог. Пока возвращать не собираюсь, но вдруг понадобится.

А по поводу "Но мне таблица-то все-таки нужна, для последующей сортировки данных" - а почему не сортируете просто _автофильтр_ом?

На самом деле, уже думал об этом. Сегодня весь день ковырялся, и с таблицей пробовал и с _автофильтр_ом. Честно говоря, большой разницы между ними пока не вижу, кроме той, что таблица выглядит как-то более солидно. А этот самый _автофильтр_ самоуправничать не будет?

И если позволите, еще один вопрос, раз уж речь зашла о сортировке в таблицах и _автофильтр_ах.
Нажимаю на фильтр, выбираю "Сортировка от минимального к максимальному", а вот отменить эту установку не получается. Что надо сделать, чтобы отменить? Там есть опция "Удалить фильтр с поля...", но она почему-то не активирована.

[_Boroda_]

А кстати, на какие кнопки надо нажать, чтобы его назад-то вернуть?

Поскольку я, по причинам, указанным выше, его никогда не убираю, то могу посоветовать просто отдельно запустить строку

Код Выделить Развернуть

Application.AutoCorrect.AutoFillFormulasInLists = True

Ну не совсем они "разные"... Формулы, в общем-то, одинаковые, но они "подхватывают" однотипные данные из разных ячеек, разбросанных по другому листу с определенным шагом (Данные > Из Интернета > Импорт) и вставляют их в одну таблицу. Поэтому "автоматические" формулы получаются совсем не такими, какие нужны.

Все равно, если формулы не делаются "протягиванием", то это разные формулы
Кстати, киньте кусок файла с объяснялкой что-куда-почему, может, можно написать одну формулу на весь столбец.

Ну мне-то простительно Улыбка  Я же еще только учусь

А вот как раз в этом случае крайне желательно делать все четко и по правилам. До тех пор, по крайней мере, пока Вы не скажете сами себе что-то типа "Я уже настолько хорошо знаю правила, что могу себе позволить их нарушать". Хотя, даже и в этом случае ...

Честно говоря, большой разницы между ними пока не вижу, кроме той, что таблица выглядит как-то более солидно

автозаполнение, автоформатирование, формулы в стиле ДжетПивот, авторасширение диапазона, автоименование диапазонов (столбцов, строк, всей таблицы), наверняка еще много чего забыл написать

А этот самый _автофильтр_ самоуправничать не будет?

Не должен

Нажимаю на фильтр, выбираю "Сортировка от минимального к максимальному", а вот отменить эту установку не получается. Что надо сделать, чтобы отменить? Там есть опция "Удалить фильтр с поля...", но она почему-то не активирована.

А вот это можно понять по-разному. Уточните.

[Serge 007]

На какую кнопку надо нажать, чтобы его назад вернуть?

Ещё раз на ту же кнопку ("Не создавать вычисляемые столбцы автоматически"), что я показал на скрине Table_AutoFill.gif в третьем посте

...большой разницы между ними пока не вижу...

Для меня основным отличием является то, что таблица - это динамически именованый диапазон (с возможностью самостоятельной фильтрации и иными прибамбасами), который создаёт сам Excel, а _автофильтр_ - это инструмент, который можно применять к любому диапазону

Нажимаю на фильтр, выбираю "Сортировка от минимального к максимальному", а вот отменить эту установку не получается. Что надо сделать, чтобы отменить? Там есть опция "Удалить фильтр с поля...", но она почему-то не активирована.

Отменить сортировку можно так:
1. Выделяете любую ячейку таблицы
2. Вкладка Данные - группа Сортировка и фильтр - меню Сортировка
3. В появившемся окне выбираем Удалить уровень

Можно так же нажать на значек фильтра в заголовке любого поля таблицы выбрать из контекстного меню пункт Сортировка по цвету - Пользовательская сортировка - Удалить уровень

Третий способ: в макрос добавьте такую строку:

Код Выделить Развернуть

Worksheets("Лист1").ListObjects("Таблица1").Sort.SortFields.Clear

Опция "Удалить фильтр с поля..." неактивна, потому что Вы не установили никаких ФИЛЬТРОВ, Вы воспользовались СОРТИРОВКОЙ, поэтому удалять попросту нечего

[Олег*]

Ещё раз на ту же кнопку ("Не создавать вычисляемые столбцы автоматически"), что я показал на скрине Table_AutoFill.gif в третьем посте

А как же на нее нажать-то, если она уже больше не появляется никогда? Вместо нее появляется кнопка совсем другого вида и с единственной опцией-предложением ""Заполнить все ячейки данного столбца указанной формулой".

Отменить сортировку можно так:
1. Выделяете любую ячейку таблицы
2. Вкладка Данные - группа Сортировка и фильтр - меню Сортировка
3. В появившемся окне выбираем Удалить уровень

Можно так же нажать на значек фильтра в заголовке любого поля таблицы выбрать из контекстного меню пункт Сортировка по цвету - Пользовательская сортировка - Удалить уровень

Сам бы я никогда не сообразил, честное слово! Вот уж запрятали, так запрятали!

[Serge 007]

Ещё раз на ту же кнопку ("Не создавать вычисляемые столбцы автоматически"), что я показал на скрине в третьем посте

А как же на нее нажать-то, если она уже больше не появляется никогда?

Если кнопка пропала из контекстного меню, то вернуть её можно так:
Excel 2010: Файл - Параметры - Правописание - Параметры автозамены - Автоформат при вводе - Создать вычисляемые столбцы, заполнив таблицы формулами
Excel 2007: Кнопка Офис - Параметры Excel - Правописание - Параметры автозамены - Автоформат при вводе - Создать вычисляемые столбцы, заполнив таблицы формулами

[Олег*]

Если кнопка пропала из контекстного меню, то вернуть её можно так:
Excel 2010: Файл - Параметры - Правописание - Параметры автозамены - Автоформат при вводе - Создать вычисляемые столбцы, заполнив таблицы формулами

Спасибо! Постараюсь не забыть