Сортировка защищенных ячеек excel
В EXCEL (неважно в каком).
Есть некая ТАБЛИЦА, в которой часть данных в некоторые СТОЛБЦЫ вводятся в ручную, а часть обрабатывает введенные данные и выдает результат. (следовательно, те ЯЧЕЙКИ где есть формулы необходимо защитить), затем защищаем ЛИСТ.
ТЕПЕРЬ ПРОБЛЕМА:
Необходимо сортировать данные ТАБЛИЦЫ (с учетом заблокированных ячеек), но это НЕВОЗМОЖНО (Даже если при защите ЛИСТА установить ВСЕ ГАЛОЧКИ,т.е. дать полное разрешение)
Люди есть мысли, как заставить работать сортировку?
В EXCEL (неважно в каком).
Есть некая ТАБЛИЦА, в которой часть данных в некоторые СТОЛБЦЫ вводятся в ручную, а часть обрабатывает введенные данные и выдает результат. (следовательно, те ЯЧЕЙКИ где есть формулы необходимо защитить), затем защищаем ЛИСТ.
ТЕПЕРЬ ПРОБЛЕМА:
Необходимо сортировать данные ТАБЛИЦЫ (с учетом заблокированных ячеек), но это НЕВОЗМОЖНО (Даже если при защите ЛИСТА установить ВСЕ ГАЛОЧКИ,т.е. дать полное разрешение)
Люди есть мысли, как заставить работать сортировку? Catand76
Век живи - век учись
Люди есть мысли, как заставить работать сортировку? Автор - Catand76
Дата добавления - 04.07.2018 в 07:37
Все прекрасно работает. Помимо необходимых для остальной работы нужно поставить только одну дополнительную галку - "Использование автофильтра". На вкладке Данные кнопки автофильтра засерятся (неактивными станут), но сам автофильтр (стрелочки) работать будет и в обычных таблицах и в умных
Все прекрасно работает. Помимо необходимых для остальной работы нужно поставить только одну дополнительную галку - "Использование автофильтра". На вкладке Данные кнопки автофильтра засерятся (неактивными станут), но сам автофильтр (стрелочки) работать будет и в обычных таблицах и в умных _Boroda_
boa, Это был бы выход если 1 человек пользуется (тогда вообще нет смысла блокировать), но проблема в том, что файлом пользуется N кол-во человек и данные влияют на другие файлы и графики. Файл уже слишком "большой" и тормозит на слабых машинах, а если еще раз ввести дубль всех таблиц (их не 1 и не 2. ) будет вообще не камильфо. Сейчас защита листа снята и мне приходится по 1-2 разу в неделю восстанавливать формулы и УФ. P.S. "Безбожники" лезут туда, куда Макар телят не гонял. иногда мне прямо интересно. что творится в их головах, чтоб так извращенно ломать связи в формулах.
boa, Это был бы выход если 1 человек пользуется (тогда вообще нет смысла блокировать), но проблема в том, что файлом пользуется N кол-во человек и данные влияют на другие файлы и графики. Файл уже слишком "большой" и тормозит на слабых машинах, а если еще раз ввести дубль всех таблиц (их не 1 и не 2. ) будет вообще не камильфо. Сейчас защита листа снята и мне приходится по 1-2 разу в неделю восстанавливать формулы и УФ. P.S. "Безбожники" лезут туда, куда Макар телят не гонял. иногда мне прямо интересно. что творится в их головах, чтоб так извращенно ломать связи в формулах. Catand76
Catand76, формулы можно перенести в менеджер имен и скрыть их там, а в ячейках использовать лишь имена Формула1, Формула2 и т.д.
Пускай потом голову ломают, что и как считается
а файл пересохраните в бинарном формате и он значительно "подусохнет"
Catand76, формулы можно перенести в менеджер имен и скрыть их там, а в ячейках использовать лишь имена Формула1, Формула2 и т.д.
Пускай потом голову ломают, что и как считается
а файл пересохраните в бинарном формате и он значительно "подусохнет" boa
если остальные юзеры не вносят данные, то еще вариант - создать пользовательского клиента,
т.е. основной файл у вас в пользовании, а у остальных книга с коннектами к этому файлу.
Либо установить пароль на редактирование книги.
если остальные юзеры не вносят данные, то еще вариант - создать пользовательского клиента,
т.е. основной файл у вас в пользовании, а у остальных книга с коннектами к этому файлу.
Либо установить пароль на редактирование книги. boa
boa, Книга и так двоичная, юзеров МНОГО, все вносят данные.
Да и предложенные решения КРИВЫЕ. Может макросом кто поможет?
boa, Книга и так двоичная, юзеров МНОГО, все вносят данные.
Да и предложенные решения КРИВЫЕ. Может макросом кто поможет? Catand76
Не, вот ну ничего себе? Вы переписали свой первый пост, поменяли название темы (было "Фильтр заблокированного листа"), везде фильтр заменили на сотрировку. И как теперь выглядит мой ответ? Первая мысль - что за дебил, его спрашивают про сотрировку, а он отвечает про фильтр. Нужно было или новую тему создавать, или в следующем посте написать, что перепутали.
Макросом вот так можно
Оранжевая кнопка паролит лист для пользователя, но не для макроса. При необходимости снимаем пароль вручную. Пароль q1. Можно поменять - в макросе предпоследняя строка. По выбору пользователя или плавающий делать не стал - забудете, потом придется ломать. А так всегда в коде макроса посмотреть можно, туда ж никто больше не полезет, правильно?
Заленая кнопка сортирует данные по столбцу, в котором находится левая верхняя ячейка выделенного диапазона. Предполагается, что в таблице есть шапка в одну строку. Ескейп, крестик или "Отмена" - выход из макроса
[vba]
Sub Sortir()
Dim d As Range, d0 As Range
Set d0 = Selection(1)
сb_ = Replace(Cells(1, d0.Column).Address(0, 0), 1, "")
s_ = MsgBox("Столбец '" & сb_ & "' сортируем по возрастанию?", vbYesNoCancel, "Сортировка по столбцу " & сb_)
If s_ < 6 Then Exit Sub
or_ = s_ - 5
On Error Resume Next
Set d = d0.CurrentRegion
Set d = d.Offset(1).Resize(d.Rows.Count - 1)
With ActiveSheet.Sort.SortFields
.Clear
.Add Key:=d0, Order:=or_
With .Parent
.SetRange d
.Apply
End With
End With
d0.Select
End Sub
Sub Parol()
ActiveSheet.Protect userInterfaceOnly:=True, Password:="q1"
End Sub
Не, вот ну ничего себе? Вы переписали свой первый пост, поменяли название темы (было "Фильтр заблокированного листа"), везде фильтр заменили на сотрировку. И как теперь выглядит мой ответ? Первая мысль - что за дебил, его спрашивают про сотрировку, а он отвечает про фильтр. Нужно было или новую тему создавать, или в следующем посте написать, что перепутали.
Макросом вот так можно
Оранжевая кнопка паролит лист для пользователя, но не для макроса. При необходимости снимаем пароль вручную. Пароль q1. Можно поменять - в макросе предпоследняя строка. По выбору пользователя или плавающий делать не стал - забудете, потом придется ломать. А так всегда в коде макроса посмотреть можно, туда ж никто больше не полезет, правильно?
Заленая кнопка сортирует данные по столбцу, в котором находится левая верхняя ячейка выделенного диапазона. Предполагается, что в таблице есть шапка в одну строку. Ескейп, крестик или "Отмена" - выход из макроса
[vba]
Sub Sortir()
Dim d As Range, d0 As Range
Set d0 = Selection(1)
сb_ = Replace(Cells(1, d0.Column).Address(0, 0), 1, "")
s_ = MsgBox("Столбец '" & сb_ & "' сортируем по возрастанию?", vbYesNoCancel, "Сортировка по столбцу " & сb_)
If s_ < 6 Then Exit Sub
or_ = s_ - 5
On Error Resume Next
Set d = d0.CurrentRegion
Set d = d.Offset(1).Resize(d.Rows.Count - 1)
With ActiveSheet.Sort.SortFields
.Clear
.Add Key:=d0, Order:=or_
With .Parent
.SetRange d
.Apply
End With
End With
d0.Select
End Sub
Sub Parol()
ActiveSheet.Protect userInterfaceOnly:=True, Password:="q1"
End Sub
Макросом вот так можно
Оранжевая кнопка паролит лист для пользователя, но не для макроса. При необходимости снимаем пароль вручную. Пароль q1. Можно поменять - в макросе предпоследняя строка. По выбору пользователя или плавающий делать не стал - забудете, потом придется ломать. А так всегда в коде макроса посмотреть можно, туда ж никто больше не полезет, правильно?
Заленая кнопка сортирует данные по столбцу, в котором находится левая верхняя ячейка выделенного диапазона. Предполагается, что в таблице есть шапка в одну строку. Ескейп, крестик или "Отмена" - выход из макроса
[vba]
Sub Sortir()
Dim d As Range, d0 As Range
Set d0 = Selection(1)
сb_ = Replace(Cells(1, d0.Column).Address(0, 0), 1, "")
s_ = MsgBox("Столбец '" & сb_ & "' сортируем по возрастанию?", vbYesNoCancel, "Сортировка по столбцу " & сb_)
If s_ < 6 Then Exit Sub
or_ = s_ - 5
On Error Resume Next
Set d = d0.CurrentRegion
Set d = d.Offset(1).Resize(d.Rows.Count - 1)
With ActiveSheet.Sort.SortFields
.Clear
.Add Key:=d0, Order:=or_
With .Parent
.SetRange d
.Apply
End With
End With
d0.Select
End Sub
Sub Parol()
ActiveSheet.Protect userInterfaceOnly:=True, Password:="q1"
End Sub
Как использовать раскрывающийся список на защищенном листе в Excel?
Обычно при использовании Excel нельзя вносить изменения в защищенный лист. Если вы по-прежнему хотите использовать раскрывающийся список на защищенном листе, вам необходимо разблокировать эти ячейки, прежде чем защищать рабочий лист. В этой статье мы покажем вам, как выбрать все ячейки в раскрывающемся списке и сразу разблокировать их, чтобы сделать это невозможно после защиты рабочего листа.
Использовать раскрывающийся список на защищенном листе в Excel
1. Во-первых, вам нужно выбрать все ячейки с раскрывающимся списком на листе, который нужно защитить. Пожалуйста, нажмите Найти и выбрать > проверка достоверности данных под Главная меню.
2. Затем все ячейки с выпадающим списком сразу выбираются на текущем листе. Щелкните их правой кнопкой мыши и выберите Формат ячеек в контекстном меню. Смотрите скриншот:
3. в Формат ячеек диалогового окна, перейдите к Protection вкладка, снимите флажок Заблокированный поле, а затем щелкните OK кнопку.
Теперь все ячейки с выпадающим списком на текущем листе разблокированы. И все они могут использоваться после защиты рабочего листа.
Легко создать раскрывающийся список с флажками в Excel:
Раскрывающийся список с флажками полезности Kutools for Excel может помочь вам легко создать раскрывающийся список с флажками в указанном диапазоне, текущем листе, текущей книге или всех открытых книгах в зависимости от ваших потребностей.
Загрузите полнофункциональную 30-дневную бесплатную версию Kutools for Excel прямо сейчас!
Статьи по теме:
Автозаполнение при вводе текста в раскрывающемся списке Excel
Если у вас есть раскрывающийся список проверки данных с большими значениями, вам нужно прокрутить список вниз только для того, чтобы найти нужное, или введите все слово напрямую в поле списка. Если есть способ разрешить автозаполнение при вводе первой буквы в выпадающем списке, все станет проще. В этом руководстве представлен метод решения проблемы.
Создать раскрывающийся список из другой книги в Excel
Создать раскрывающийся список проверки данных среди листов в книге довольно просто. Но если данные списка, необходимые для проверки данных, находятся в другой книге, что вы будете делать? В этом руководстве вы узнаете, как подробно создать раскрывающийся список из другой книги в Excel.
Создайте раскрывающийся список с возможностью поиска в Excel
Для раскрывающегося списка с многочисленными значениями найти подходящий - непростая задача. Ранее мы ввели метод автоматического заполнения раскрывающегося списка при вводе первой буквы в раскрывающемся списке. Помимо функции автозаполнения, вы также можете сделать раскрывающийся список доступным для поиска для повышения эффективности работы при поиске правильных значений в раскрывающемся списке. Чтобы сделать раскрывающийся список доступным для поиска, попробуйте метод, описанный в этом руководстве.
Автоматическое заполнение других ячеек при выборе значений в раскрывающемся списке Excel
Допустим, вы создали раскрывающийся список на основе значений в диапазоне ячеек B8: B14. При выборе любого значения в раскрывающемся списке необходимо, чтобы соответствующие значения в диапазоне ячеек C8: C14 автоматически заполнялись в выбранной ячейке. Для решения проблемы методы, описанные в этом руководстве, окажут вам услугу.
Разрешить сортировку и фильтрацию на защищенном листе
Чтобы разрешить сортировку и фильтрацию на защищенном листе, вам потребуются следующие шаги:
1. Выберите диапазон, в котором пользователям будет разрешена сортировка и фильтрация, нажмите Данные > Фильтр добавить Значки фильтрации к заголовкам ассортимента. Смотрите скриншот :
2. Затем, не снимая выделения с диапазона, нажмите Обзор > Разрешить пользователям редактировать диапазоны. Смотрите скриншот:
3. в Разрешить пользователям редактировать диапазоны диалоговое окно, нажмите Новое для отображения Новый диапазон диалоговое окно, дайте диапазону имя. Смотрите скриншот:
4. Нажмите OK , чтобы вернуться к Разрешить пользователям редактировать диапазоны диалога, а затем нажмите Защитить лист. Смотрите скриншот:
5. в Защитить лист диалоговом окне введите пароль в Пароль , чтобы снять защиту с текстового поля листа, и в Разрешить всем пользователям этого листа список для проверки Сортировать и Использовать автофильтр параметры. Смотрите скриншот:
6. Нажмите OK, и повторно введите пароль в текстовое поле в Подтвердите пароль диалог. Нажмите OK закончить защиту листа. Смотрите скриншот:
Затем пользователи могут сортировать и фильтровать на этом защищенном листе.
Наконечник. Если есть несколько листов, необходимых для защиты и позволяющих пользователям сортировать и фильтровать, вы можете применить Защитить лист полезности Kutools for Excel для защиты нескольких листов одновременно.пожалуйста, перейдите на бесплатную пробную версию Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с приведенными ниже шагами.
1. После завершения первых трех шагов, позволяющих пользователям редактировать диапазоны на указанных листах, щелкните Кутулс Плюс > Защитить лист. Смотрите скриншот:
2. в Защитить лист диалоговом окне выберите листы, которые вы хотите защитить, щелкните Ok для отображения следующего диалогового окна введите и повторно введите пароль и установите флажок Сортировать и Использовать автофильтр in Разрешить всем пользователям этой рабочей таблицы .
3. Нажмите Ok. Теперь появляется диалоговое окно, в котором показано, сколько листов было защищено, как показано на скриншоте ниже. Пожалуйста, нажмите на OK чтобы закрыть его.
Теперь все указанные листы защищены, но их можно сортировать и фильтровать.
Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты - от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно.
Уровень 0. Защита от ввода некорректных данных в ячейку
Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation) . В Excel 2003 и старше это можно было сделать с помощью меню Данные - Проверка (Data - Validation) . На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:
Уровень 1. Защита ячеек листа от изменений
Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:
- Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек(Format Cells) . На вкладке Защита(Protection) снимите флажок Защищаемая ячейка(Locked) . Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие - нет, можно воспользоваться этим макросом.
- Для включения защиты текущего листа в Excel 2003 и старше - выберите в меню Сервис - Защита - Защитить лист(Tools - Protection - Protect worksheet) , а в Excel 2007 и новее - нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw) . В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:
Т.е., если мы хотим оставить пользователю возможность, например, форматировать защищенные и незащищенные ячейки, необходимо установить первых три флажка. Также можно разрешить пользователям использовать сортировку, автофильтр и другие удобные средства работы с таблицами.
Уровень 2. Выборочная защита диапазонов для разных пользователей
Если предполагается, что с файлом будут работать несколько пользователей, причем каждый из них должен иметь доступ в свою область листа, то можно установить защиту листа с разными паролями на разные диапазоны ячеек.
Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges) . В версии Excel 2003 и старше для этого есть команда в меню Сервис - Защита - Разрешить изменение диапазонов (Tools - Protection - Allow users to change ranges) :
В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:
Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.
Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать "в своем огороде".
Уровень 3. Защита листов книги
Если необходимо защититься от:
- удаления, переименования, перемещения листов в книге
- изменения закрепленных областей ("шапки" и т.п.)
- нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки "плюс/минус")
- возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel
то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или - в старых версиях Excel - через меню Сервис - Защита - Защитить книгу (Tools - Protection - Protect workbook) :
Уровень 4. Шифрование файла
При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл - Сохранить как (File - Save As) , а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options) . В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:
Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты - от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно.
Уровень 0. Защита от ввода некорректных данных в ячейку
Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation) . В Excel 2003 и старше это можно было сделать с помощью меню Данные - Проверка (Data - Validation) . На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:
Уровень 1. Защита ячеек листа от изменений
Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:
- Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек(Format Cells) . На вкладке Защита(Protection) снимите флажок Защищаемая ячейка(Locked) . Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие - нет, можно воспользоваться этим макросом.
- Для включения защиты текущего листа в Excel 2003 и старше - выберите в меню Сервис - Защита - Защитить лист(Tools - Protection - Protect worksheet) , а в Excel 2007 и новее - нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw) . В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:
Т.е., если мы хотим оставить пользователю возможность, например, форматировать защищенные и незащищенные ячейки, необходимо установить первых три флажка. Также можно разрешить пользователям использовать сортировку, автофильтр и другие удобные средства работы с таблицами.
Уровень 2. Выборочная защита диапазонов для разных пользователей
Если предполагается, что с файлом будут работать несколько пользователей, причем каждый из них должен иметь доступ в свою область листа, то можно установить защиту листа с разными паролями на разные диапазоны ячеек.
Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges) . В версии Excel 2003 и старше для этого есть команда в меню Сервис - Защита - Разрешить изменение диапазонов (Tools - Protection - Allow users to change ranges) :
В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:
Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.
Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать "в своем огороде".
Уровень 3. Защита листов книги
Если необходимо защититься от:
- удаления, переименования, перемещения листов в книге
- изменения закрепленных областей ("шапки" и т.п.)
- нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки "плюс/минус")
- возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel
то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или - в старых версиях Excel - через меню Сервис - Защита - Защитить книгу (Tools - Protection - Protect workbook) :
Уровень 4. Шифрование файла
При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл - Сохранить как (File - Save As) , а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options) . В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:
Читайте также: