Excel, как удалять листы, не нарушая ссылки на формулы
Мне нужно удалить листы из книги без разрыва ссылок на формулы; Есть ли способ сделать это с помощью кода или настроек?
8 ответов 8
Мой бесплатный Mappit! Надстройка, доступная по адресу http://www.experts-exchange.com/A_2613.html, имеет в качестве одного из своих выходов карту, на которой показаны отношения листа
Вы можете использовать это, чтобы определить, где могут возникнуть потенциальные проблемы при удалении листа.
Скопируйте все формулы. Вставьте как формулы в другую область (например, лист, чтобы сохранить их, которые затем можно будет скрыть). На листе с формулой резервных копий найдите «=», замените на «|» (или любой другой редко используемый символ). Всякий раз, когда вам нужны формулы, вам нужно только вставить их туда, где они должны быть, затем найдите «|» и заменить на «=» (не включать ни одного из «кавычек»).
Это позволяет легко исправить формулы, которые вы, возможно, нарушили. Я обычно ломаю свою, удаляя рабочий лист с ячейками, на которые ссылаются формулы. Я использую эту технику, чтобы исправить таблицы, сгенерированные запросом мощности, когда он не заполняет таблицу с тем же порядком столбцов, что и запрос. Процесс состоит в том, чтобы загрузить только в соединение (при этом удаляются данные, на которые ссылались мои формулы), затем загрузить в рабочую таблицу, принудительно перестроив таблицу с тем же порядком столбцов, что и в запросе.
Если вы жестко программируете конкретный лист, это было бы невозможно. Вы подразумеваете, что хотели бы, чтобы зависимость была перенесена на другой лист?
Как указано выше, невозможно удалить лист без разрыва ссылки. Однако вот несколько вариантов в зависимости от ваших обстоятельств:
- Специальное копирование / вставка: значения, чтобы сохранить результат формулы и удалить лист.
- Скройте лист и защитите книгу вместо удаления листа.
- Измените ссылки на лист в другой книге.
это спустя 4 года, но я хотел бы оставить здесь свое решение на случай, если оно поможет кому-то, имеющему ту же проблему
- Вырежьте (CTRL+X) ячейку и ее содержимое, на которое ссылается ваша формула с 1-го листа (лист, который будет удален, например: Лист!$ A $ 1)
- Вставьте вырезанную ячейку на 2-й лист в новом или том же месте (лист, который будет сохранен, напр .: Вставьте его в Лист2!A1)
- Сделайте это для всех ячеек, которые должны быть переданы
- Проверьте формулы, чтобы убедиться, что они ссылаются на 2-й лист (Лист2! вместо Sheet1!)
- Удалить 1-й лист
- При необходимости переименуйте 2-й лист в то же имя, что и 1-й лист, который у вас был
Надеюсь это поможет
Вот как я делал это в прошлом. Я использую его, когда удаляю лист во время выполнения кода. Общий процесс заключается в следующем с конкретной процедурой ниже. Обратите внимание, что вам нужно определить публичную переменную в верхней части модуля.
Я знаю, что опаздываю на вечеринку, но я нашел полезный и простой обходной путь. Допустим, вы пытаетесь VLOOKUP в лист, который постоянно заменяется (Sheet2). Это ваша формула:
Когда вы удалите Sheet2, ссылка будет прервана. Но если вы продолжите заменять Sheet2 на листы, названные так же, сделайте следующее:
На случайной ячейке, скажем, AA1, напишите:
Не используйте знак равенства. Вы хотите это как чистый текст.
Затем измените свой VLOOKUP на:
Чистый текст не подвержен удалению ссылок, поэтому это должно сделать ваш код безопасным.
Работа с листами в excel
В основном вся работа на листе excel происходит в ячейках. Подробнее про работу с ячейками можно почитать в нашей статье «Работа с ячейками в excel».
Помимо ячеек на листе эксель можно добавлять несколько слоев различного рода вставок таких как: диаграммы, рисунки, срезы, формы элементов управления и многое другое.
 |
 |
 |
Как в excel добавить лист
Как переименовать лист в excel
Ещё один способ изменить название листа – это дважды кликнуть на его название, ввести имя и нажать Enter.
Третий наиболее редкий способ переименовать листы в экселе через меню. На вкладке «Главная» в группе «Ячейки» нужно нажать кнопку «Формат» и далее выбрать «Переименовать лист»:
Отобразить листы в excel
Иногда при открытии файла Вы можете обнаружить, что пропали листы в excel. Расстраивается не стоит, скорее всего Вы легко сможете снова отобразить свои листы эксель.
Сначала нужно разобраться пропали вкладки с листами или Вы не можете найти только часть листов.
Пропали некоторые листы в excel
Если в эксель пропали не все листы, то в первую очередь убедитесь, что нужный Вам лист не за зоной видимости вкладок.
Для этого на клавишах переключения между вкладками нажмите правой клавишей мыши и увидите список всех не скрытых листов книги:
Далее просто выбираете какие листы отобразить и нажимаете Ok.
Если в контекстном меню все операции с листами недоступны, то скорее всего используется защита книги, ее нужно отключать. Как пользоваться защитой в эксель мы расскажем в следующих статьях.
В экселе пропали все вкладки с листами
Еще одной причиной когда в экселе нет листов – это получение файла из другой версии или даже другой программы, например 1С. Включить листы в этом случае можно в настройках. На вкладке «Файл» открываем «Параметры».
Затем в пункте «Дополнительно» убедитесь, что в разделе «Показать параметры для следующей книги» установлен флажок «Показывать ярлычки листов»:
Как удалить лист в эксель
Как распечатать в экселе на одном листе
Как правило печать огромной таблицы в несколько тысяч строк на одном листе не требуется хотя и возможна.
Чаще всего в эксель требуется печать сравнительно небольшого участка, например нужна печать на одном листе одной таблицы или графика.
В статье «6 Основных приёмов в работе с таблицами» мы подробно описывали «Как напечатать таблицу в excel». Там Вы сможете найти всю необходимую информацию.
Как копировать листы в excel
Часто бывает удобно сделать копию листа в excel, например получился хороший отчет за текущий месяц, и Вы хотите его сделать шаблоном.
Чтобы дублировать лист в excel нужно удерживая нужный лист левой клавишей мыши зажать Ctrl, перетащить его в желаемое место и продолжая держать Ctrl отпустить мышь.
Как удалить лист в excel?
Если необходимо удалить несколько листов подряд, нажав и удерживая кнопку SHIFT щелкнуть(выделить) следующие листы.
Если нужно удалить несколько листов выборочно: нажимаем и удерживаем кнопку CTRL, выделяем нужные, а вернее ненужные листы.
- Щелкаем правой кнопкой мыши, в открывшемся меню выбираем Удалить.
Для удаления листа(ов) Excel можно сделать так:
- правой кнопкой мыши выделить удаляемый Лист, либо при нажатой клавише «Ctrl» выделить несколько удаляемых Листов в ярлычках с названием Листов (внизу)
- правой кнопкой мыши кликнуть в выделенный(ые) ярлычки (внизу)
- в открывшемся контекстном меню — «Удалить».
При работе с этой популярной программой excel иногда возникает необходимость удалить не актуальные в данный момент листы или листы с устаревшей информацией. Сделать в данной программе это очень легко, рассмотрим инструкцию по удалению листов:
Первый шаг. Начать нужно с открытия необходимой вам книги excel. Сразу выберите лист, который вы желаете удалить.
Второй шаг. Наведите курсор вашей мыши на название листа, который необходимо просто удалить.
Третий шаг. Нажмите правой кнопкой мыши на этом листе, перед вам откроется небольшая таблица, в которой вторая сверху надпись будет выглядит как «удалить»
Нажимайте на эту надпись, после чего удалиться весь лист со всеми данными.
Выделить необходимой диапазон (выделить верхнюю ячейку, затем нажать ctrl+»стрелка вниз»), затем нажать F2 (инициализируется редактирование формулы в верхней ячейке выбранного диапазона), ввести необходимую формулу и нажать комбинацию клавиш ctrl+Enter, что приведет к автозаполнению выбранного диапазона ячеек введенной формулой.
Тут много вопросов. Сделать можно все в екселе формулами, но у вас слишком мало исходных данных. Например, допустим, число 54 встречается в январе, феврале, марте, апреле, а потом в июне, июле и августе. То есть сначала 4 раза подряд, потом месяц пропуск и еще три раза. Как считать тут? 3+2 или 3 или 2?
Далее. Какие именно числа считать надо? Все или только определенные каким то правилом? Или вы вручную будете указывать числа, для которых надо проверить повторяемость? Если вы вручную вводите — это проще, если надо найти ВСЕ числа, которые встречаются в таблице два раза и более — это сложнее.
Еще момент. Ваша таблица конечная или данные будут еще добавляться в нее? Заголовков у столбцов нет и не ясно будет ли расти таблица вправо. Вниз таблица может расти на следующий год или это данные за год и анализировать надо только 1 год?
После ответов на эти вопросы составьте алгоритм, затем простые формулы, затем объединяйте их для получения общей формулы.
- определить количество вхождений числа в таблице для каждого числа (использовать СЧЕТЕСЛИ и получим вспомогательную таблицу).
- для всех чисел, число вхождений которых более 2 проверяем их количество по строкам — еще одна вспомогательная таблица
Вот как-то так. Без дополнительных данных сложно предложить какое-то решение.
функция СТАНДОТКЛОН определяет значения стандартного отклонения. Оно используется для определения однородности данных. Чем меньше, тем данные более однородны. Мне стандартное отклонение больше нравиться рассчитывать для данных в статике (например, анализ ассортимента или клиентов). Если данные в динамике (за период), то лучше рассчитывать коэффициент вариации для определения однородности.
Например, есть ряд данных 12 14 8 10 11. Для начала нужно рассчитать среднее значение: (12+14+8+10+11)/5=11. Далее рассчитываем Дисперсию = ((12-11)^2+(14-11)^2+(8-11)^2+(10-11)^2+(11-11)^2)/5=4. Стандартное отклонение — это корень из дисперсии, т.е. в нашем случае корень из 4 = 2.
В Excel есть СТАНДОТКЛОН.Г — стандартное отклонение для генеральной совокупности (весь диапазон данных). Тогда в формуле дисперсии числитель делим на количество данных. В нашем случае = 5.
Есть и СТАНДОТКЛОН.В -стандартное отклонение для выборочной совокупности (например, результаты маркетинговых исследований, часть клиентов и т.п.). Тогда в формуле дисперсии числитель делим на количество данных — 1. В нашем случае = 5-1=4.
Время, в Excel является числом, точнее, десятичной дробью меньше единицы. (Соответственно дата — число, больше единицы. А дата с временем — это сумма этих чисел.).
Что бы данное число смотрелось в клетке как Дата, или Время, или Дата с Временем — для этого необходимо задать определенный формат с свойствах ячейки (Втор. кл. мыши по ячейке — пункт «Формат Ячейки»). Следственно, над любой датой и над любым временем можно производить операции сложения и вычитания. Так же надо помнить, что в Excel есть функция (ВРЕМЯ()), которая преобразовывает три значения (часы,минуты,секунды) в специальную десятичную дробь, которая, по сути, является временем «чч:мм:сс», если изменить формат ячейки на «Время-13:30:55».
- Пусть в ячейке A1 у нас есть время «23:23:59«, тогда
- Пусть, клетках B1,C1,D1 у нас будет количество часов,минут,секунд, (целые числа) которое мы хотим добавить к нашему времени.
- Для получения результата, запишем в клетке E1 формулу: =A1+ВРЕМЯ(B1;C1;D1)
В Экселе есть особенность — если в числе написан неверный знак то значение ячейки считается текстовым и значение ячейки при расчете суммы даёт 0.
Нужно либо найти ошибку либо в функцию СУММ подставить формулу для преобразования текста в число с исправлением ошибок ввода, только в поле исходная ячейка надо ввести свой диапазон ячеек(BS93:BZ109), а после ввода в EXCEL завершить ввод формулы нажатием клавиш CTRL+SHIFT+ENTER (ввести как формулу массива)
Получится примерно так :
фигурные скобки Excel ставит сам после ввода CTRL+SHIFT+ENTER