Excel сортировка при защищенном листе - Word и Excel - помощь в работе с программами

Как разрешить сортировку и фильтрацию заблокированных ячеек в защищенных листах?

Как правило, защищенный лист нельзя редактировать, но в некоторых случаях вы можете разрешить другим пользователям выполнять сортировку или фильтрацию в защищенных листах, как вы можете с этим справиться?

Разрешить сортировку и фильтрацию на защищенном листе

Разрешить сортировку и фильтрацию на защищенном листе

Чтобы разрешить сортировку и фильтрацию на защищенном листе, вам потребуются следующие шаги:

1. Выберите диапазон, в котором пользователям будет разрешена сортировка и фильтрация, нажмите Данные > Фильтр добавить Значки фильтрации к заголовкам ассортимента. Смотрите скриншот ：

2. Затем, не снимая выделения с диапазона, нажмите Обзор > Разрешить пользователям редактировать диапазоны. Смотрите скриншот:

3. в Разрешить пользователям редактировать диапазоны диалоговое окно, нажмите Новинки для отображения Новый диапазон диалоговое окно, дайте диапазону имя. Смотрите скриншот:

4. Нажмите OK , чтобы вернуться к Разрешить пользователям редактировать диапазоны диалога, а затем нажмите Защитить лист. Смотрите скриншот:

5. в Защитить лист диалоговом окне введите пароль в Пароль , чтобы снять защиту с текстового поля листа, и в Разрешить всем пользователям этого листа список для проверки Сортировать и Использовать автофильтр параметры. Смотрите скриншот:

6. Нажмите OK, и повторно введите пароль в текстовое поле в Подтвердите пароль диалог. Нажмите OK закончить защиту листа. Смотрите скриншот:

Затем пользователи могут сортировать и фильтровать на этом защищенном листе.

Наконечник. Если есть несколько листов, необходимых для защиты и позволяющих пользователям сортировать и фильтровать, вы можете применить Защитить лист полезности Kutools for Excel для защиты нескольких листов одновременно.пожалуйста, перейдите на бесплатную пробную версию Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с приведенными ниже шагами.

1. После завершения первых трех шагов, позволяющих пользователям редактировать диапазоны на указанных листах, щелкните Кутулс Плюс > Защитить лист. Смотрите скриншот:

2. в Защитить лист диалоговом окне выберите листы, которые вы хотите защитить, щелкните Ok для отображения следующего диалогового окна введите и повторно введите пароль и установите флажок Сортировать и Использовать автофильтр in Разрешить всем пользователям этой рабочей таблицы .

3. Нажмите Ok. Теперь появляется диалоговое окно, в котором показано, сколько листов было защищено, как показано на скриншоте ниже. Пожалуйста, нажмите на OK чтобы закрыть его.

Теперь все указанные листы защищены, но их можно сортировать и фильтровать.

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (22)

Оценок пока нет. Оцените первым!

Источник

ukor

Пользователь

Сообщений: 8
Регистрация: 01.01.1970

Проблема: Отсутствует возможность сортировки защищенных ячеек в MS Excel.
Дано: Лист с защищенным диапазоном, фильтр на заголовках.
Задача: Необходимо предоставить возможность сортировать значения защищенных диапазонов с помощью опций стандартного фильтра.

Возможные варианты решения:
1. При обработке в фильтре события «сортировка» отключать защиту, сортировать, а затем включать? Если да, то как называется функция?
2. Свои варианты…

Спасибо.

The_Prist

Пользователь

Сообщений: 14181
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

Вариант: т.к. отследить изменение состояния фильтра On-Line нельзя, то лучше всего сделать отдельную кнопку на листе, на которую повесить макрос сортировки. В макросе перед сортировкой снимать защиту листа, а после устанавливать заново.

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

ukor

Пользователь

Сообщений: 8
Регистрация: 01.01.1970

{quote}{login=The_Prist}{date=30.08.2010 04:34}{thema=}{post}Вариант: т.к. отследить изменение состояния фильтра On-Line нельзя, то лучше всего сделать отдельную кнопку на листе, на которую повесить макрос сортировки. В макросе перед сортировкой снимать защиту листа, а после устанавливать заново.{/post}{/quote}

Или, как вариант, сделать выпадающий список с заголовком «Сортировка», выбирать столбцы, выбирать способ сортировки, щелкать по кнопке, а далее по вышеописанному Вами сценарию. Я все же надеялся, что есть возможность как-то фильтр «ловить». Жаль. Спасибо большое за ответ!

	а разве нельзя поставить галку на разрешение автофильтра при защите листа?

ukor Пользователь Сообщений: 8 Регистрация: 01.01.1970	{quote}{login=}{date=30.08.2010 04:44}{thema=}{post}а разве нельзя поставить галку на разрешение автофильтра при защите листа?{/post}{/quote} Галка установлена. Разрешение автофильтра («Использование автофильтра») не дает прав на сортировку.

Поставить-то галку можно. Но будет сортировать только по возрастанию/убыванию, а по условию и все другие варианты — нет.
А вот как при включенной защите сортировать по условиям? Ведь на все варианты кнопок с макросами не напасёшься.

The_Prist

Пользователь

Сообщений: 14181
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

{quote}{login=Vlad}{date=30.08.2010 04:58}{thema=}{post}А вот как при включенной защите сортировать по условиям? Ведь на все варианты кнопок с макросами не напасёшься.{/post}{/quote}

Application.Dialogs(192).Show

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

The_Prist Пользователь Сообщений: 14181 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	Для 2003 можно применить этот: Application.Dialogs(39).Show Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

ukor

Пользователь

Сообщений: 8
Регистрация: 01.01.1970

{quote}{login=Vlad}{date=30.08.2010 04:58}{thema=}{post}Поставить-то галку можно. Но будет сортировать только по возрастанию/убыванию, а по условию и все другие варианты — нет.
А вот как при включенной защите сортировать по условиям? Ведь на все варианты кнопок с макросами не напасёшься.{/post}{/quote}

При включенной защите сортировка невозможна, независимо от условий.
Имеется возможность только отображения значений.

	я чего-то не понимаю? на защищенном листе и сортировка и АФ по всем условиям

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	АФ работает. Сортировка не работает. 2003.

The_Prist Пользователь Сообщений: 14181 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	#12 30.08.2010 22:43:54 {quote}{login=}{date=30.08.2010 10:38}{thema=}{post}я чего-то не понимаю? на защищенном листе и сортировка и АФ по всем условиям{/post}{/quote}У меня сортировка через Фильтр не работает. Да и не работала никогда при защите листа… Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

Источник

Как разрешить сортировку и фильтрацию заблокированных ячеек в защищенных листах?

На чтение 2 мин. Просмотров 393 Опубликовано 23.05.2021

Как правило, защищенный лист нельзя редактировать, но в некоторых случаях вы можете разрешить другим пользователям выполнять сортировку или фильтрацию в защитных листах, как вы можете с этим справиться?

Разрешить сортировку и фильтрацию на защищенном листе

Разрешить сортировку и фильтрацию на защищенном листе

Чтобы разрешить сортировку и фильтрацию на защищенном листе, вам потребуются следующие шаги:

1. Выберите диапазон, в котором пользователи будут разрешать сортировку и фильтрацию, нажмите Данные > Фильтр , чтобы добавить значки фильтрации к заголовки ассортимента. См. Снимок экрана ：

2. Затем, не снимая выделения с диапазона, нажмите Обзор > Разрешить пользователям редактировать диапазоны . См. Снимок экрана:

3. В диалоговом окне Разрешить пользователям редактировать диапазоны нажмите Создать , чтобы отобразить диалоговое окно Новый диапазон , дайте диапазону имя . См. Снимок экрана:

4. Нажмите OK , чтобы вернуться в диалоговое окно Разрешить пользователям редактировать диапазоны , а затем нажмите Защитить лист . См. Снимок экрана:

5. В диалоговом окне Защитить лист введите пароль в текстовое поле Пароль , чтобы снять защиту с листа, и в поле Разрешить всем пользователям этого листа в списке установите флажок Сортировка и Использовать автофильтр . См. Снимок экрана:

6. Нажмите OK и повторно введите пароль в текстовое поле в диалоговом окне Подтверждение пароля . Нажмите OK , чтобы завершить защиту листа. См. Снимок экрана:

Затем пользователи могут сортировать и фильтровать на этом защищенном листе

Совет. Если есть несколько листов, необходимых для защиты и позволяющих пользователям сортировать и фильтровать, вы можете применить утилиту Protect Worksheet . из Kutools for Excel для защиты нескольких листов одновременно. пожалуйста, сначала бесплатно попробуйте Kutools for Excel, а затем примените операцию в соответствии с приведенными ниже шагами.

1. После завершения первых трех шагов, позволяющих пользователям редактировать диапазоны на указанных листах, нажмите Kutools Plus > Защитить рабочий лист . См. Снимок экрана:

2. В диалоговом окне Защитить рабочий лист выберите листы, которые вы хотите защитить, нажмите Ok , чтобы отобразить следующее диалоговое окно, введите и повторно введите пароль и установите флажок Сортировка и Использовать автофильтр в Разрешить всем пользователям этого листа список.

3. Нажмите ОК . Появится диалоговое окно, в котором показано, сколько листов было защищено, как показано на скриншоте ниже. Нажмите кнопку OK , чтобы закрыть его.

Теперь все указанные листы защищены, но их можно сортировать и фильтровать.

Kutools for Excel: более 300 функций, которые должны быть в Excel, 30- дневная бесплатная пробная версия здесь

Источник

Сортировка заблокированных ячеек

Catand76

Дата: Среда, 04.07.2018, 07:37 |
Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

В EXCEL (неважно в каком).
Есть некая ТАБЛИЦА, в которой часть данных в некоторые СТОЛБЦЫ вводятся в ручную, а часть обрабатывает введенные данные и выдает результат. (следовательно, те ЯЧЕЙКИ где есть формулы необходимо защитить), затем защищаем ЛИСТ….
ТЕПЕРЬ ПРОБЛЕМА:
Необходимо сортировать данные ТАБЛИЦЫ (с учетом заблокированных ячеек), но это НЕВОЗМОЖНО (Даже если при защите ЛИСТА установить ВСЕ ГАЛОЧКИ,т.е. дать полное разрешение)

Люди есть мысли, как заставить работать сортировку?

Век живи — век учись

Сообщение отредактировал Catand76 — Среда, 04.07.2018, 12:11

Ответить

_Boroda_

Дата: Среда, 04.07.2018, 09:33 |
Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Все прекрасно работает. Помимо необходимых для остальной работы нужно поставить только одну дополнительную галку — «Использование автофильтра». На вкладке Данные кнопки автофильтра засерятся (неактивными станут), но сам автофильтр (стрелочки) работать будет и в обычных таблицах и в умных

К сообщению приложен файл:

579175.xlsm
(9.4 Kb)

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Catand76

Дата: Среда, 04.07.2018, 12:10 |
Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

_Boroda_, Прошу прощения — РЕЧЬ ИДЕТ О СОРТИРОВКЕ!!!!!

Век живи — век учись

Ответить

boa

Дата: Среда, 04.07.2018, 13:39 |
Сообщение № 4

Группа: Друзья

Ранг: Ветеран

Сообщений: 543

Репутация:
166
±

Замечаний:
0% ±

2013, 365

Catand76, как вариант, построить сводную таблицу и в ней сортировать

К сообщению приложен файл:

6792231.xlsm
(13.4 Kb)

Ответить

Catand76

Дата: Среда, 04.07.2018, 14:00 |
Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

boa, Это был бы выход если 1 человек пользуется (тогда вообще нет смысла блокировать), но проблема в том, что файлом пользуется N кол-во человек и данные влияют на другие файлы и графики…. Файл уже слишком «большой» и тормозит на слабых машинах, а если еще раз ввести дубль всех таблиц (их не 1 и не 2….) будет вообще не камильфо…. Сейчас защита листа снята и мне приходится по 1-2 разу в неделю восстанавливать формулы и УФ…. P.S. «Безбожники» лезут туда, куда Макар телят не гонял… иногда мне прямо интересно. что творится в их головах, чтоб так извращенно ломать связи в формулах???

Век живи — век учись

Ответить

boa

Дата: Среда, 04.07.2018, 14:19 |
Сообщение № 6

Группа: Друзья

Ранг: Ветеран

Сообщений: 543

Репутация:
166
±

Замечаний:
0% ±

2013, 365

Catand76, формулы можно перенести в менеджер имен и скрыть их там, а в ячейках использовать лишь имена Формула1, Формула2 и т.д.
Пускай потом голову ломают, что и как считается
а файл пересохраните в бинарном формате и он значительно «подусохнет»

Сообщение отредактировал boa — Среда, 04.07.2018, 14:20

Ответить

boa

Дата: Среда, 04.07.2018, 14:25 |
Сообщение № 7

Группа: Друзья

Ранг: Ветеран

Сообщений: 543

Репутация:
166
±

Замечаний:
0% ±

2013, 365

если остальные юзеры не вносят данные, то еще вариант — создать пользовательского клиента,
т.е. основной файл у вас в пользовании, а у остальных книга с коннектами к этому файлу.
Либо установить пароль на редактирование книги.

Сообщение отредактировал boa — Среда, 04.07.2018, 14:26

Ответить

Catand76

Дата: Среда, 04.07.2018, 15:15 |
Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

boa, Книга и так двоичная, юзеров МНОГО, все вносят данные….
Да и предложенные решения КРИВЫЕ!!!! Может макросом кто поможет?

Век живи — век учись

Ответить

_Boroda_

Дата: Среда, 04.07.2018, 15:32 |
Сообщение № 9

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Не, вот ну ничего себе? Вы переписали свой первый пост, поменяли название темы (было «Фильтр заблокированного листа»), везде фильтр заменили на сотрировку. И как теперь выглядит мой ответ? Первая мысль — что за дебил, его спрашивают про сотрировку, а он отвечает про фильтр. Нужно было или новую тему создавать, или в следующем посте написать, что перепутали.

Макросом вот так можно
Оранжевая кнопка паролит лист для пользователя, но не для макроса. При необходимости снимаем пароль вручную. Пароль 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

[/vba]

Ответить

pabchek

Дата: Среда, 04.07.2018, 15:57 |
Сообщение № 10

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

Репутация:
218
±

Замечаний:
0% ±

Excel 2007

[offtop]
Любишь ты, Саша, меткие названия ;)))[/offtop]

«Учиться, учиться и еще раз учиться!»
WM: R399923528092

Ответить

boa

Дата: Среда, 04.07.2018, 16:08 |
Сообщение № 11

Группа: Друзья

Ранг: Ветеран

Сообщений: 543

Репутация:
166
±

Замечаний:
0% ±

2013, 365

Catand76, что бы Sortir работал :), не забудьте добавить в модуль книги

[vba]

Код

Private Sub Workbook_Open()
Call Parol
End Sub

[/vba]

Сообщение отредактировал boa — Среда, 04.07.2018, 16:08

Ответить

_Boroda_

Дата: Среда, 04.07.2018, 16:28 |
Сообщение № 12

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

что бы Sortir работал :), не забудьте добавить в модуль книги
[vba]

Код

Private Sub Workbook_Open()
Call Parol
End Sub

[/vba]

Зачем? На всякий случай запаролить лист при открытии книги конечно хорошо (но не поможет — до того, как понадобится сортировка, работника все равно забудут включить макросы), но работа Сортира никак с этим не связана.
Вы случайно не перепутали UserInterfaceOnly с EnableOutlining?

А вот на случай, если админ Catand76 снял пароль, что-то поменял и забыл поставить его обратно, я бы написал принудительную установку на событие закрытия книги (с учетом того, что админ-то уж точно не забудет макросы включить)
[vba]

Код

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Parol
End Sub

[/vba]
Можно, конечно, заставлять пользователя включить макросы, но это уже отдельная тема, которая, кстати, неоднократно уже обсуждалась здесь

Ответить

boa

Дата: Среда, 04.07.2018, 16:51 |
Сообщение № 13

Группа: Друзья

Ранг: Ветеран

Сообщений: 543

Репутация:
166
±

Замечаний:
0% ±

2013, 365

попробуйте закрыть книгу, а потом открыть и не запуская макроса с установкой пароля сделать сортировку.

Ответить

_Boroda_

Дата: Среда, 04.07.2018, 17:11 |
Сообщение № 14

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Дык это первое, что я сделал, когда Ваш пост увидел. Все нормально работало. А сейчас перегрузил Excel и да, без оранжевой кнопки зеленая не срабатывает. Чудеса

Доделал еще всякие мелочи и дописал дополнение от boa. В итоге вот так получилось

Ответить

boa

Дата: Среда, 04.07.2018, 22:36 |
Сообщение № 15

Группа: Друзья

Ранг: Ветеран

Сообщений: 543

Репутация:
166
±

Замечаний:
0% ±

2013, 365

зеленая не срабатывает. Чудеса

_Boroda_, это не чудеса.
userInterfaceOnly действует в пределах одного сеанса,
в параметрах защиты листа данное не сохраняется.
если выполнить код при открытии книги, то все работает,
если после открытия книги данная комманда не отработала, то Оно = False
рад был быть полезным

Сообщение отредактировал boa — Среда, 04.07.2018, 22:39

Ответить

_Boroda_

Дата: Четверг, 05.07.2018, 01:32 |
Сообщение № 16

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

userInterfaceOnly действует в пределах одного сеанса

Не согласен. Она действует в пределах одной книги — пока книга открыта. При закрытии сбрасывается. Сеанс (если под словом «сеанс» Вы подразумеваете сеанс Excel) здесь ни при чем.
А на работе у меня просто Excel загадочный. Дома, кстати, тоже, но уже по-другому.

Ответить

Catand76

Дата: Четверг, 05.07.2018, 02:55 |
Сообщение № 17

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

_Boroda_, Спасибо большое…. Для меня макрос — темный лес…. но буду разбираться и запиливать под свои потребности (вот если еще напротив каждой строки в макросе поставите примечание, будет вообще GOOD)

Век живи — век учись

Сообщение отредактировал Catand76 — Четверг, 05.07.2018, 02:56

Ответить

boa

Дата: Четверг, 05.07.2018, 08:30 |
Сообщение № 18

Группа: Друзья

Ранг: Ветеран

Сообщений: 543

Репутация:
166
±

Замечаний:
0% ±

2013, 365

если под словом «сеанс» Вы подразумеваете сеанс Excel

я имел ввиду сеанс открытой книги.

Ответить

Catand76

Дата: Четверг, 05.07.2018, 09:09 |
Сообщение № 19

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Ребята, а можно макрос упростить?
Вот вводные:
Вешаем на экран кнопку «сортировка»
При нажатии на кнопку.
1. Снимается защита листа.
2. Сортируется «по возрастанию» Столбец «[3]» в умной «Таблице1»
3. Лист снова защищается.

Век живи — век учись

Сообщение отредактировал Catand76 — Четверг, 05.07.2018, 09:10

Ответить

boa

Дата: Четверг, 05.07.2018, 09:17 |
Сообщение № 20

Группа: Друзья

Ранг: Ветеран

Сообщений: 543

Репутация:
166
±

Замечаний:
0% ±

2013, 365

Catand76, для сортировки не надо снимать защиту.
вы можете кнопку установки защиты вообще убрать с листа, т.к. защита будет установлена при закрытии/открытии файла если макросы включены.
можете еще и на событие сохранения устанавливать защиту листа
[vba]

Код

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Parol
End Sub

[/vba]

Сообщение отредактировал boa — Четверг, 05.07.2018, 09:24

Ответить

Источник

Содержание

Как разрешить сортировку и фильтрацию заблокированных ячеек в защищенных листах?
Разрешить сортировку и фильтрацию на защищенном листе
Сортировка данных на защищенных листах
Excel сортировка на защищенном листе

Как разрешить сортировку и фильтрацию заблокированных ячеек в защищенных листах?

Разрешить сортировку и фильтрацию на защищенном листе

Разрешить сортировку и фильтрацию на защищенном листе

Чтобы разрешить сортировку и фильтрацию на защищенном листе, вам потребуются следующие шаги:

Затем пользователи могут сортировать и фильтровать на этом защищенном листе

Теперь все указанные листы защищены, но их можно сортировать и фильтровать.

Источник

Сортировка данных на защищенных листах

Когда вы защищаете рабочий лист, Excel не позволяет пользователям выполнять широкий спектр задач с данными на листе. Одна из вещей, которую пользователь больше не может делать, – это сортировать данные. Что делать, если вы хотите, чтобы пользователь мог сортировать данные, но при этом имел защищенный лист?

Ответ довольно прост: Excel позволяет указать, что пользователи могут и не могут делать с защищенным листом . Когда вы открываете вкладку «Обзор» на ленте и нажимаете кнопку «Защитить лист» в группе «Защита» (группа «Изменения» в более ранних версиях Excel), Excel отображает диалоговое окно «Защита листа». Внизу диалогового окна находится длинный список флажков. Все, что вам нужно сделать, это выбрать, что пользователь должен иметь возможность делать с листом. Один из вариантов (нужно немного прокрутить вниз) – Сортировка. Если вы выберете эту опцию, то пользователи смогут сортировать защищенные данные.

Следует иметь в виду большое предостережение: все ячейки, которые будут участвовать в сортировке (или потенциально участвовать в сортировке) ) должен быть разблокирован. Сюда входят любые заголовки столбцов для данных, которые могут быть отсортированы.

Перед блокировкой рабочего листа выберите все ячейки, которые вы хотите, чтобы люди могли сортировать. (Отличный способ сделать это – выбрать одну из ячеек и нажать Shift + Ctrl + 8 . Excel считает, что выделенная область должна быть отсортирована после завершения сортировки.) все еще выделенные ячейки, откройте вкладку Защита диалогового окна Формат ячеек и снимите флажок Заблокировано. Теперь защитите свой рабочий лист и убедитесь, что вы разрешили сортировку в спецификациях защиты.

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

Источник

Excel сортировка на защищенном листе

Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка заблокированных ячеек (Формулы/Formulas)

Сортировка заблокированных ячеек

Catand76

Дата: Среда, 04.07.2018, 07:37 | Сообщение № 1

В EXCEL (неважно в каком).
Есть некая ТАБЛИЦА, в которой часть данных в некоторые СТОЛБЦЫ вводятся в ручную, а часть обрабатывает введенные данные и выдает результат. (следовательно, те ЯЧЕЙКИ где есть формулы необходимо защитить), затем защищаем ЛИСТ.
ТЕПЕРЬ ПРОБЛЕМА:
Необходимо сортировать данные ТАБЛИЦЫ (с учетом заблокированных ячеек), но это НЕВОЗМОЖНО (Даже если при защите ЛИСТА установить ВСЕ ГАЛОЧКИ,т.е. дать полное разрешение)

Люди есть мысли, как заставить работать сортировку?

В EXCEL (неважно в каком).
Есть некая ТАБЛИЦА, в которой часть данных в некоторые СТОЛБЦЫ вводятся в ручную, а часть обрабатывает введенные данные и выдает результат. (следовательно, те ЯЧЕЙКИ где есть формулы необходимо защитить), затем защищаем ЛИСТ.
ТЕПЕРЬ ПРОБЛЕМА:
Необходимо сортировать данные ТАБЛИЦЫ (с учетом заблокированных ячеек), но это НЕВОЗМОЖНО (Даже если при защите ЛИСТА установить ВСЕ ГАЛОЧКИ,т.е. дать полное разрешение)

Люди есть мысли, как заставить работать сортировку? Catand76

Век живи — век учись

Сообщение В EXCEL (неважно в каком).
Есть некая ТАБЛИЦА, в которой часть данных в некоторые СТОЛБЦЫ вводятся в ручную, а часть обрабатывает введенные данные и выдает результат. (следовательно, те ЯЧЕЙКИ где есть формулы необходимо защитить), затем защищаем ЛИСТ.
ТЕПЕРЬ ПРОБЛЕМА:
Необходимо сортировать данные ТАБЛИЦЫ (с учетом заблокированных ячеек), но это НЕВОЗМОЖНО (Даже если при защите ЛИСТА установить ВСЕ ГАЛОЧКИ,т.е. дать полное разрешение)

Люди есть мысли, как заставить работать сортировку? Автор — Catand76
Дата добавления — 04.07.2018 в 07:37

_Boroda_

Дата: Среда, 04.07.2018, 09:33 | Сообщение № 2

Ответить

Catand76

Дата: Среда, 04.07.2018, 12:10 | Сообщение № 3

boa

Дата: Среда, 04.07.2018, 13:39 | Сообщение № 4

Catand76

Дата: Среда, 04.07.2018, 14:00 | Сообщение № 5

boa

Дата: Среда, 04.07.2018, 14:19 | Сообщение № 6

boa

Дата: Среда, 04.07.2018, 14:25 | Сообщение № 7

Catand76

Дата: Среда, 04.07.2018, 15:15 | Сообщение № 8

_Boroda_

Дата: Среда, 04.07.2018, 15:32 | Сообщение № 9

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

Ответить

Сообщение Не, вот ну ничего себе? Вы переписали свой первый пост, поменяли название темы (было «Фильтр заблокированного листа»), везде фильтр заменили на сотрировку. И как теперь выглядит мой ответ? Первая мысль — что за дебил, его спрашивают про сотрировку, а он отвечает про фильтр. Нужно было или новую тему создавать, или в следующем посте написать, что перепутали.

Источник

Adblock
detector

Источник

I want to let user sort data on the sheet + let user select cells (to copy them to clipboard) + keep them uneditable.

The issue here is that sorting requires adding a range with locked cells (Allow Users to Edit Ranges) and then u just Protect sheet with AllowSorting:=True, AllowFiltering:=True.

Now sorting works, cells un-editable but you can’t select them to copy.
If I make cells selectable than user is able to change values… that I dont want.

Any suggestions?

Thank you!!

asked May 29, 2016 at 21:16

looks like that only wait is to write a macro. excel build-in functionality doesn’t allow both sorting ,selecting and protecting

answered Jun 1, 2016 at 15:49

MaxMax

1462 silver badges9 bronze badges

Источник

Сортировка данных на защищённых листах и в диапазонах с разрывом.

Автор gamaun, 27.06.2012, 12:10

« назад — далее »

Здравствуйте, специалисты! Очень нужна Ваша помощь! Есть файлик, на котором листы защищены паролем от изменения и выделения(за исключением некоторых ячеек). На вкладке «рабочие» нужно отсортировать определённый диапазон без снятия защиты. Я попытался записать макрос, но он выполняет сортировку данных только той области которая была выделена при записи. В VBA я ни в зуб ногой, что бы подправить макрос. Лишний бред который записался рекордером я удалил (надеюсь всё и правильно). Остаётся надеяться на Вашу помощь. Надо что бы макрос выполнял сортировку выделяемой области или привязать параметры сортировки к диапазону указанному в ячейках AY4 и AZ4, BA4 и BВ4 и критерию BС4 (естественно значения в ячейках в ручную будут меняться, в зависимости от количества заполненных строк и критерия). На вкладке «ИТР тариф» такая же задача, но там диапазон с разрывом. Заранее благодарен! Прилагаю файл в Excel 2003.

Макрос для листа «РАБОЧИЕ» я почистил ещё, оставив только самое необходимое:

Sub Сортировка_1() On Error Resume Next ActiveSheet.Unprotect "1234" Посл = Application.WorksheetFunction.Max([A:A]) + 5 Range("B6:AT" & Посл).Sort Range("C6") ActiveSheet.Protect "1234" End Sub

Что касается листа «ИТР ТАРИФ», то, насколько я понимаю, сортировка должна быть одна на оба диапазона? Если такая разбивка предназначена для печати (предполагаю), то может стоит использовать Сквозные строки «$4:$5» (Файл → Параметры страницы → Лист)? Правда придётся что-то придумать с подитогами.

Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

Уважаемый Шпец Докапыч, большое Вам спасибо за то что согласились мне помочь. Данный макрос выполняет сортировку всего заполненного диапазона. Но таблица заполняется из двух разных списков и поэтому сортировать данные приходится дважды. То есть занесён первый список например в диапазоне В6:АТ47, а второй добавлен ниже в диапазон В48:АТ79, поэтому каждый диапазон нужно сортировать отдельно по столбцу «С». Реально ли так сделать?
На счёт листа «ИТР ТАРИФ» Вы правы, сортировка должна быть одна на оба диапазона В6:AZ30 и В43:AZ67. Сквозные строки я никогда не использовал, честно скажу не знаю как. Из листа «ИТР ТАРИФ» в оригинальном файле вытекает ещё 15 листов, поэтому переделывать его мне кажется будет сложно.

ЦитироватьТо есть занесён первый список например в диапазоне В6:АТ47, а второй добавлен ниже в диапазон В48:АТ79, поэтому каждый диапазон нужно сортировать отдельно по столбцу «С». Реально ли так сделать?

Тогда будет две строки сортировки: первая по жёсткому диапазону, вторая — до посл. строки, как в моём примере. Если с этим возникнет проблема, то можно опять пройтись макрорекордером и указать где заминка.

ЦитироватьНа счёт листа «ИТР ТАРИФ» Вы правы, сортировка должна быть одна на оба диапазона В6:AZ30 и В43:AZ67. Сквозные строки я никогда не использовал, честно скажу не знаю как.

Кстати, на листе «РАБОЧИЕ» кто-то всё же использовал сквозные строки. Как-то нелогично получается: раздельная сортировка по цельному диапазону (лист «РАБОЧИЕ») и единая по несмежному (лист «ИТР ТАРИФ»). Второе я реализовал так: скопировал лист, удалил разрыв, отсортировал и скопировал кусочки по местам:

Sub Сортировка_2() Set Tf = Sheets("ИТР ТАРИФ") On Error Resume Next Tf.Unprotect "1234" Application.ScreenUpdating = 0 Tf.Copy Sheets(1)


        Rows("31:42").Delete Shift:=xlUp
    Посл = Application.WorksheetFunction.Max(Tf.[A:A]) + 5
    Range("B6:AZ" & Посл).Sort Range("C6")
        NoP = Array("B:B", "G:AK", "AO:AO", "AX:AZ")
    For Each cn In NoP
      Tf.Range(cn & " 6:30").Value = Range(cn & " 6:30").Value
      Tf.Range(cn & " 43:" & Посл + 12).Value = _
         Range(cn & " 31:" & Посл).Value
    Next
        Application.DisplayAlerts = 0
    ActiveSheet.Delete
    Application.DisplayAlerts = 1
    Tf.Protect "1234"
    Tf.Select
    Application.ScreenUpdating = 1
End Sub

Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

Спасибо! Попробовать смогу только в понедельник.

Большое спасибо! Макрос на листе «ИТР тариф» работает отлично! Но пока вернёмся к листу РАБОЧИЕ. Насколько я понял Вы предполагаете что первый диапазон будет жёстким. А в этом как раз и сложность. Величина диапазонов зависит от размеров исходных списков, у одних граница первого диапазона может быть В6:АТ47, а у других В6:АТ97 или В6:АТ127. Может как то можно диапазон сортировки в макросе привязать к ячейкам AY4:BB4? Т.е. указал там нужный диапазон, а макрос по нему отсортировал, указал следующий диапазон, нажал на кнопку и опять отсортировал следующий указанный диапазон.

Цитата: gamaun от 02.07.2012, 13:48
Может как то можно диапазон сортировки в макросе привязать к ячейкам AY4:BB4?

Если так удобнее, то конечно:

Sub Сортировка_11() On Error Resume Next ActiveSheet.Unprotect "1234" ВД = [AY4] & [AZ4] & ":" & [BA4] & [BB4] СС = [BC4] & [AZ4] Range(ВД).Sort Range(СС) ActiveSheet.Protect "1234" End Sub

Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

Спасибо! Может и не очень удобно, зато практично. Какой диапазон пользователь укажет тот и отсортирует! По крайне мере с начало за паролем для сортировки, а потом за исправлениями после блуда «шаловливых ручек» по файлу прибегать больше не будут . А как к листу ИТР тариф приделать этот макрос?

Цитата: gamaun от 02.07.2012, 16:31
А как к листу ИТР тариф приделать этот макрос?

Аналогичную кнопку прикрутить, которая [ЖМИ СЮДА ДЛЯ СОРТИРОВКИ!!!]. Можно её тупо скопировать, а затем ПКМ → Назначить макрос.

Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

Цитата: Шпец Докапыч от 02.07.2012, 16:46
Аналогичную кнопку прикрутить, которая [ЖМИ СЮДА ДЛЯ СОРТИРОВКИ!!!]. Можно её тупо скопировать, а затем ПКМ → Назначить макрос.

Не в этом загвоздка, с кнопкой проблемы нет. Как быть если сортируемый диапазон получится с разрывом? Я понимаю что из 2-х макросов (Сортировка_11 и Сортировка_2) нужно собрать третий, но как-то надо прописать там, что если указанный диапазон получается с разрывом провести процедуру описанную в макросе «Сортировка_2», а иначе просто идти по пути описанному в макросе «Сортировка_11». Результат совмещения ниже, красным выделил место где по моему мнению надо прописывать условие.
Sub Сортировка_21()
Set Tf = Sheets(«ИТР ТАРИФ»)
On Error Resume Next
Tf.Unprotect «1234»
Application.ScreenUpdating = 0
Tf.Copy Sheets(1)

ВД = [BC4] & [BD4] & «:» & [BE4] & [BF4]
СС = [BG4] & [BD4]
Range(ВД).Sort Range(СС)
Rows(«31:42»).Delete Shift:=xlUp

NoP = Array(«B:B», «G:AK», «AO:AO», «AX:BA»)
For Each cn In NoP
Tf.Range(cn & » 6:30″).Value = Range(cn & » 6:30″).Value
Tf.Range(cn & » 43:» & Посл + 12).Value = _
Range(cn & » 31:» & Посл).Value
Next

Application.DisplayAlerts = 0
ActiveSheet.Delete
Application.DisplayAlerts = 1
Tf.Protect «1234»
Tf.Select
Application.ScreenUpdating = 1
End Sub

ЦитироватьЯ понимаю что из 2-х макросов (Сортировка_11 и Сортировка_2) нужно собрать третий

Можно составить третий, который по условию будет запускать один из 2-х:

Sub Сортировка2в1() rAll = [BB4] - [AZ4] + 1 СД = [AY4] & [AZ4] & ":" & [AY4] & [BB4] rFact = Application.WorksheetFunction.CountA(Range(СД)) If rFact < rAll Then Сортировка_11 Else: Сортировка_2 End If End Sub

Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

К сожалению этот вариант не подходит, т.к. макрос «Сортировка_2» сортирует полностью весь список, а не выделенный диапазон (указанный в ячейках), а если указать диапазон с разрывом начинается ругня на одну строку макроса(конечно может я не совсем корректно адаптировал к своему листу этот макрос). Мне кажется что надо на основе макроса «Сортировка_11» создавать «Сортировка_21» и что-то придумывать, прописывать условие: если строка, указанная в ячейке 1 меньше 31 и строка указанная в ячейке 2 больше 42, то проводить сортировку на подобие описанной в макросе «Сортировка_2″( с временным удалением строк 31:42….и т.д., но не диапазона В6:посл, а диапазона, указанного в ячейках «1» и «2»), иначе проводить сортировку, как описано в макросе «Сортировка_11». Прикладываю файл где ругается на строку макроса.

Профессиональные приемы работы в Microsoft Excel
►
Обмен опытом
►
Microsoft Excel
►
Сортировка данных на защищённых листах и в диапазонах с разрывом.

Источник

Как разрешить сортировку и фильтрацию заблокированных ячеек в защищенных листах?

Разрешить сортировку и фильтрацию на защищенном листе

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Как разрешить сортировку и фильтрацию заблокированных ячеек в защищенных листах?

Разрешить сортировку и фильтрацию на защищенном листе

Как разрешить сортировку и фильтрацию заблокированных ячеек в защищенных листах?

Разрешить сортировку и фильтрацию на защищенном листе

Сортировка данных на защищенных листах

Excel сортировка на защищенном листе

Сортировка данных на защищённых листах и в диапазонах с разрывом.

А вот еще интересные статьи: