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

Вход

Регистрация

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

 

= Мир MS Excel/Изменение формулы в ячейках по шаблону - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Изменение формулы в ячейках по шаблону (Формулы/Formulas)
Изменение формулы в ячейках по шаблону
AVI Дата: Понедельник, 28.08.2017, 08:00 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
Добрый день!
У меня имеется большие файлы (более 700 тыщстрок).
Для дальнейшей работы вынужден рисовать монстерообразные формулы и потом копировать ее на весь файл.
Возможно ли сделать так, что бы изменяя формулу в одной ячейке изменениям подвергались и в необходимых столбцах.
К примеру: в ячейке C2 находится формула
Код
=ЕСЛИ(ИЛИ(ЕЧИСЛО(--СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;"А";""))));"";A2)

Я в нее добавляю
Код
;ЕЧИСЛО(--СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A3;"-а";"")))
В итоге получается
Код
=ЕСЛИ(ИЛИ(ЕЧИСЛО(--СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;"А";"")));ЕЧИСЛО(--СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;"-а";""))));"";A2)
И нужно , что бы на все формулы в столбце b распространилось это изменение, при этом, в каждой формуле сохранились ссылки на соответствующую строку.
Надеюсь понятно объяснил...
К сообщению приложен файл: Microsoft_Excel.xlsx (9.8 Kb)
 
Ответить
СообщениеДобрый день!
У меня имеется большие файлы (более 700 тыщстрок).
Для дальнейшей работы вынужден рисовать монстерообразные формулы и потом копировать ее на весь файл.
Возможно ли сделать так, что бы изменяя формулу в одной ячейке изменениям подвергались и в необходимых столбцах.
К примеру: в ячейке C2 находится формула
Код
=ЕСЛИ(ИЛИ(ЕЧИСЛО(--СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;"А";""))));"";A2)

Я в нее добавляю
Код
;ЕЧИСЛО(--СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A3;"-а";"")))
В итоге получается
Код
=ЕСЛИ(ИЛИ(ЕЧИСЛО(--СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;"А";"")));ЕЧИСЛО(--СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;"-а";""))));"";A2)
И нужно , что бы на все формулы в столбце b распространилось это изменение, при этом, в каждой формуле сохранились ссылки на соответствующую строку.
Надеюсь понятно объяснил...
Автор - AVI
Дата добавления - 28.08.2017 в 08:00
китин Дата: Понедельник, 28.08.2017, 08:40 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7034
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
а воспользоваться даблкликом ( поместив курсор на черном квадрате справа внизу на выделенной ячейке ) не пробовали?

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеа воспользоваться даблкликом ( поместив курсор на черном квадрате справа внизу на выделенной ячейке ) не пробовали?
Автор - китин
Дата добавления - 28.08.2017 в 08:40
AVI Дата: Понедельник, 28.08.2017, 09:08 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
Цитата китин, 28.08.2017 в 08:40, в сообщении № 2 ()
не пробовали?

Не пробовал, потому что не знал)) Спасибо!! Вопрос снят)
Блин, если бы я раньше знал. Сколько бы времени сэкономил!!

Интересно, почему я не могу плюсик кинуть... Уже давно


Сообщение отредактировал AVI - Понедельник, 28.08.2017, 09:11
 
Ответить
Сообщение
Цитата китин, 28.08.2017 в 08:40, в сообщении № 2 ()
не пробовали?

Не пробовал, потому что не знал)) Спасибо!! Вопрос снят)
Блин, если бы я раньше знал. Сколько бы времени сэкономил!!

Интересно, почему я не могу плюсик кинуть... Уже давно
Автор - AVI
Дата добавления - 28.08.2017 в 09:08
AndreTM Дата: Понедельник, 28.08.2017, 15:04 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
Ещё можно оформлять данные в виде "Умных таблиц", там изменение формул так же приводит к автоматическому реформату формул всего столбца. Но с ними надо научиться и привыкнуть работать.

Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
СообщениеЕщё можно оформлять данные в виде "Умных таблиц", там изменение формул так же приводит к автоматическому реформату формул всего столбца. Но с ними надо научиться и привыкнуть работать.
Автор - AndreTM
Дата добавления - 28.08.2017 в 15:04
_Boroda_ Дата: Понедельник, 28.08.2017, 17:19 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16880
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Умная таблица с формулами на 700000 записей может умереть. У нее ж формат еще... Конечно, можно отключить, но это редко делают

AVI, формула типа ЕСЛИ(ИЛИ(А;В);... всегда считает оба значения внутри ИЛИ, а потом уже начинает работать с ЕСЛИ. Другими словами, Вы всегда делаете 700000 х 2 = 1400000 проверок
А вот если написать что-то типа
Код
=ЕСЛИ(ЕОШ(--ПОДСТАВИТЬ(A2;"А";));ЕСЛИ(ЕОШ(--ПОДСТАВИТЬ(A2;"-а";));A2;"");"")
, то сначала просматривается подстановка "А", если она срабатывает (получается число), то дальше не смотрим и ставим пусто. А вот если первое условие не срабатывает, то тогда проверяем второе - "-а". Проверок получается на столько меньше, сколько получится чисел при замене "А" (срабатываение при первом условии). Поэтому, кстати, нужно в первое условие ставить замену того (из "А" и "-а"), что чаще встречается в базе. А проверить можно так, например:
Код
=СЧЁТЕСЛИ(A:A;"*А")


У Вас же в столбце В уже есть формулы и их просто изменить нужно? Тогда можно еще и так протянуть формулу (если это не формула массива) или значение можно еще так: встаете в В2, жмете Контрл+Шифт+СтрелкаВниз, Меняете что нужно в формуле и жмете Контрл+Ентер
И еще есть кнопочка "Заполнить" на вкладке "Главная" в разделе "Редактирование". Она размножает в любую сторону
К сообщению приложен файл: 148678686_1.xlsx (10.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУмная таблица с формулами на 700000 записей может умереть. У нее ж формат еще... Конечно, можно отключить, но это редко делают

AVI, формула типа ЕСЛИ(ИЛИ(А;В);... всегда считает оба значения внутри ИЛИ, а потом уже начинает работать с ЕСЛИ. Другими словами, Вы всегда делаете 700000 х 2 = 1400000 проверок
А вот если написать что-то типа
Код
=ЕСЛИ(ЕОШ(--ПОДСТАВИТЬ(A2;"А";));ЕСЛИ(ЕОШ(--ПОДСТАВИТЬ(A2;"-а";));A2;"");"")
, то сначала просматривается подстановка "А", если она срабатывает (получается число), то дальше не смотрим и ставим пусто. А вот если первое условие не срабатывает, то тогда проверяем второе - "-а". Проверок получается на столько меньше, сколько получится чисел при замене "А" (срабатываение при первом условии). Поэтому, кстати, нужно в первое условие ставить замену того (из "А" и "-а"), что чаще встречается в базе. А проверить можно так, например:
Код
=СЧЁТЕСЛИ(A:A;"*А")


У Вас же в столбце В уже есть формулы и их просто изменить нужно? Тогда можно еще и так протянуть формулу (если это не формула массива) или значение можно еще так: встаете в В2, жмете Контрл+Шифт+СтрелкаВниз, Меняете что нужно в формуле и жмете Контрл+Ентер
И еще есть кнопочка "Заполнить" на вкладке "Главная" в разделе "Редактирование". Она размножает в любую сторону
Автор - _Boroda_
Дата добавления - 28.08.2017 в 17:19
AndreTM Дата: Понедельник, 28.08.2017, 19:46 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
А вообще, конечно, "на 700 тыщ строк" лучше вообще не иметь ни формул, ни оформления :D

Только чистая обработка макросами и другими средствами (запросы, своды).

Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
СообщениеА вообще, конечно, "на 700 тыщ строк" лучше вообще не иметь ни формул, ни оформления :D

Только чистая обработка макросами и другими средствами (запросы, своды).
Автор - AndreTM
Дата добавления - 28.08.2017 в 19:46
AVI Дата: Вторник, 29.08.2017, 06:56 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
_Boroda_, Несмотря на то, что у меня ай7 пара открытых таких файлов с пересчетом формул гасит комп наглухо.
Цитата _Boroda_, 28.08.2017 в 17:19, в сообщении № 5 ()
Вы всегда делаете 700000 х 2 = 1400000 проверок

Я Вам больше скажу: у меня условий в формуле может более 30-ти, ибо формат адреса, который есть в росреестре, на ходу придумывается "пряморукими" регистраторам. Бывает необходимо работать с 2-мя и или 3-мя дикими файлами одновременно. Потому, что в современном экселе "всего лишь" 1 кк строк.
 
Ответить
Сообщение_Boroda_, Несмотря на то, что у меня ай7 пара открытых таких файлов с пересчетом формул гасит комп наглухо.
Цитата _Boroda_, 28.08.2017 в 17:19, в сообщении № 5 ()
Вы всегда делаете 700000 х 2 = 1400000 проверок

Я Вам больше скажу: у меня условий в формуле может более 30-ти, ибо формат адреса, который есть в росреестре, на ходу придумывается "пряморукими" регистраторам. Бывает необходимо работать с 2-мя и или 3-мя дикими файлами одновременно. Потому, что в современном экселе "всего лишь" 1 кк строк.
Автор - AVI
Дата добавления - 29.08.2017 в 06:56
AVI Дата: Вторник, 29.08.2017, 11:36 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
Цитата _Boroda_, 28.08.2017 в 17:19, в сообщении № 5 ()
У Вас же в столбце В уже есть формулы и их просто изменить нужно? Тогда можно еще и так протянуть формулу (если это не формула массива) или значение можно еще так: встаете в В2, жмете Контрл+Шифт+СтрелкаВниз, Меняете что нужно в формуле и жмете Контрл+Ентер
И еще есть кнопочка "Заполнить" на вкладке "Главная" в разделе "Редактирование". Она размножает в любую сторону

Первый способ через Контрл+шифт... жестко копирует формулу и не меняет ссылку на ячейки на соответствующую строку.
Второй способ это как даблклик по углу выделенной ячейки. Загвоздка в том, что чтобы скопировать даблкликом формулу по всем ячейкам нужно снимать все фильтры. Иначе формула копируется только в не скрытые фильтром ячейки.
 
Ответить
Сообщение
Цитата _Boroda_, 28.08.2017 в 17:19, в сообщении № 5 ()
У Вас же в столбце В уже есть формулы и их просто изменить нужно? Тогда можно еще и так протянуть формулу (если это не формула массива) или значение можно еще так: встаете в В2, жмете Контрл+Шифт+СтрелкаВниз, Меняете что нужно в формуле и жмете Контрл+Ентер
И еще есть кнопочка "Заполнить" на вкладке "Главная" в разделе "Редактирование". Она размножает в любую сторону

Первый способ через Контрл+шифт... жестко копирует формулу и не меняет ссылку на ячейки на соответствующую строку.
Второй способ это как даблклик по углу выделенной ячейки. Загвоздка в том, что чтобы скопировать даблкликом формулу по всем ячейкам нужно снимать все фильтры. Иначе формула копируется только в не скрытые фильтром ячейки.
Автор - AVI
Дата добавления - 29.08.2017 в 11:36
_Boroda_ Дата: Вторник, 29.08.2017, 16:37 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16880
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата AVI, 29.08.2017 в 11:36, в сообщении № 8 ()
через Контрл+шифт... жестко копирует формулу и не меняет ссылку на ячейки на соответствующую строку
Это Вы что-то не так делаете. Может, у Вас там доллары стоят? Покажите, как Вы вводите
Цитата AVI, 29.08.2017 в 11:36, в сообщении № 8 ()
чтобы скопировать даблкликом формулу по всем ячейкам нужно снимать все фильтры

Вы хоть слово в своих постах выше про фильтр написали? Нам догадаться нужно было, да?
И это уже другой вопрос, который, между прочим, так просто не решается. Более того, в общем случае его решение сильно зависит от версии Excel

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата AVI, 29.08.2017 в 11:36, в сообщении № 8 ()
через Контрл+шифт... жестко копирует формулу и не меняет ссылку на ячейки на соответствующую строку
Это Вы что-то не так делаете. Может, у Вас там доллары стоят? Покажите, как Вы вводите
Цитата AVI, 29.08.2017 в 11:36, в сообщении № 8 ()
чтобы скопировать даблкликом формулу по всем ячейкам нужно снимать все фильтры

Вы хоть слово в своих постах выше про фильтр написали? Нам догадаться нужно было, да?
И это уже другой вопрос, который, между прочим, так просто не решается. Более того, в общем случае его решение сильно зависит от версии Excel
Автор - _Boroda_
Дата добавления - 29.08.2017 в 16:37
AVI Дата: Среда, 30.08.2017, 08:01 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
_Boroda_,
Цитата _Boroda_, 29.08.2017 в 16:37, в сообщении № 9 ()
Нам догадаться нужно было, да?

Я про фильтры не писал, да. Я решил этот вопрос^ просто закрепил верхнюю строку и в верхней ячейке меняю формулу.
Поэтому особо проблемы с этим нет.


Сообщение отредактировал AVI - Среда, 30.08.2017, 09:03
 
Ответить
Сообщение_Boroda_,
Цитата _Boroda_, 29.08.2017 в 16:37, в сообщении № 9 ()
Нам догадаться нужно было, да?

Я про фильтры не писал, да. Я решил этот вопрос^ просто закрепил верхнюю строку и в верхней ячейке меняю формулу.
Поэтому особо проблемы с этим нет.
Автор - AVI
Дата добавления - 30.08.2017 в 08:01
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Изменение формулы в ячейках по шаблону (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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