Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Группировка данных в сводной таблице

Скачать файл, используемый в видеоуроке:

ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1(Group1) для выбранных элементов или Кв-л1(Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

1. Выделить имя группы;
2. Нажать клавишу F2 ;
3. Ввести новое имя группы.

РАЗГРУППИРОВКА ЭЛЕМЕНТОВ

1. Выделить группу элементов, которые требуется разгруппировать;
2. На вкладке Параметры(Options) в группе Группировать(Group) нажать кнопку Разгруппировать(Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать(Ungroup) ).

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

Примечания:

1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры - Сервис - Средства OLAP ) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

Так же см.:
[[Общие сведения о сводных таблицах]]
[[Сводная таблица из нескольких листов]]

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Наверное, многие сталкивались с такой проблемой, когда у Вас куча файлов , например фотки, которые как правило имеют следующее название: DSCF9163 или IMG_0065 или ещё какая-нибудь кракозябра =). А Вы хотите, что бы эти фотки назывались Новый Год!

Что же делать?

Переименовывать каждую фотографию по одной?

Хорошо, если их штук 10-20, а если 100-1000 и более. Знаете, что я Вам скажу: задолбаетесь Вы их переименовывать =)))

Переименовать группу файлов можно очень быстро, буквально за пару секунд, ну для учеников, это займет считанные минуты!

Так как ручками мы переименовывать большую группу фалов не хотим, значит - нам поможет какая-нибудь программа. И эта программа Total Commander. Думаю, каждый знает, что это за зверь, а кто не знает, переходите по ссылке, качайте и знакомьтесь.

А тут я всё же скажу, что Total Commander - это файловый менеджер с огромными возможностями!

Итак, приступим, у кого есть эта программа запускаем, у кого нету - качаем, устанавливаем и запускаем.

Для начала - выделяем группу файлов, которую нам надо переименовать , если надо выделить все файлы в папке нажимаем CTRL + A

Появится окошко:

Итак, мы хотели дать всем файлам имя «Новый Год». Так сделаем это. В поле «Маска для: имени файла», пишем имя, которое мы хотим видеть, в данном случае «Новый Год» и также добавим счётчик, что бы у нас не было одинаковых имён, для этого нажимаем на кнопку «[C] Счётчик».

Нажимаем «Выполнить», а после выполнения, закрываем это окно.

Ура! Всё получилось! Данный способ можно использовать абсолютно для любых файлов, но чаще всего он используется для переименования изображений.

Ну а теперь разберёмся с некоторыми основными настройками группового переименования файлов . Для начала полностью очистите поле «Маска для: имени файла» (для наглядности). Обратите внимание, что когда вы очистите поле, чистым станет и новое имя файла, то есть мы можем наглядно видеть, что будет происходить с именем.

И так кнопка «[N] имя» означает вставку в новое имя старого имени.

Эту кнопку вы можете использовать, если хотите в новом имени использовать старое. Идём дальше, « Дата» вставляет в имя файла, его дату создания.

Кнопка « время» работает аналогично « Дата», только вставляет не дату, а время создания файла.

« Диап.» вставляет не всё старое имя целиком, как «N», а только выбранную его часть (диапазон). Например, сделаем так, что бы от старого имени осталось только первые три буквы, для этого нажимаем на « Диап.», выделяем в появившемся окошке первые три символа и нажимаем «ок»

Мы видим, что в поле «Маска для: имени файла» написано , что означает, что в новом имени используется первые три символа старого (то есть с первого по третий символ). Если же вам надо использовать другой диапазон, то просто выделяете его в окне «Выберите символы для вставки».

Ну что ж теперь перейдём к кнопке «[C] Счётчик», по-моему, она здесь самая важная. Так как предыдущие кнопки по отдельности практически нельзя использовать, потому что имена новых файлов могут совпадать (кроме времени создания файла), то нам необходимо использовать ещё что-то чтобы имена были разными, в данном случае будут использоваться цифры.

Этот счётчик имеет некоторые настройки (смотрим раздел «Параметры счётчика [C]»). В поле «Начать с» вы задаёте число, с которого будет начинаться отсчёт, например 111

В поле «Шаг» можно задать через сколько цифр будет идти следующее число, например 3

В поле «Цифр» указываем количество цифр которое мы хотим видеть в числе, в данном случае, для наглядности возьмём побольше, например 5

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

Всё, вроде со всеми основными настройками разобрались. Как видите переименовать группу файлов сразу довольно просто, и я уверен, что для Вас это не составит большого труда.

Но, если у Вас всё же остались вопросы - милости прошу в комментарии! =) Удачи!

Файловый менеджер Total Commander весьма популярен среди пользователей программ экранного доступа, поскольку, наряду с богатым функционалом для работы с файлами и каталогами, обладает хорошей доступностью для вспомогательных технологий.В этой статье мы объясним и на конкретных примерах покажем, как при помощи Total Commander выполнять различные варианты группового переименования файлов.

Чтобы переименовать группу файлов, выделите их, либо нажимая клавишу Пробел на нужных файлах, либо выделив все файлы в папке с помощью команды Control+A, и нажмите Control+M. Откроется диалог "Групповое переименование файлов". Для переименования файлов в диалоге "Групповое переименование" используются специальные символы и команды. Все такие символы заключаются в квадратные скобки.

Внимание! Все служебные символы нужно вводить с соблюдением регистра. Например, команды [N] и [n] выполняют разные действия.

описание диалога и команд

В диалоге "Групповое переименование" имеется множество элементов, однако нам понадобятся только те, которые непосредственно участвуют в переименовании.

1. Поле "Имя", здесь указывается шаблон нового имени файла без расширения.
2. Поле "Расширение", здесь указывается расширение нового имени.
3. Поле "Найти", сюда можно вписать текст в имени файла, который нужно заменить на другой.
4. Поле "Заменить", здесь можно вписать текст, на который нужно заменить текст из поля "Найти".

Кнопка "Выполнить" запускает процесс переименования. Перед этой кнопкой находится список, в котором ещё до начала переименования отображаются результаты переименования в формате старое имя файла -> новое имя файла. Чтобы проверить правильно ли вы ввели условия переименования, прежде чем нажать кнопку "Выполнить", просмотрите этот список. Если вы всё ввели правильно, то после символа -> должны отображаться именно те имена файлов, которые вы хотите получить.

В поле "Имя" можно вписывать специальные символы и команды для формирования нового имени файла. рассмотрим основные из таких команд.

[N] - обозначает имя старого файла. Если вы впишите в поле "Имя" только [N], то новое имя файла будет совпадать со старым. То есть, команда [N] заменяет собой всё имя файла без расширения.

Первый символ старого имени файла. Аналогично команды или обозначают второй или пятый символ в старом имени файла.

Четыре символа из старого имени файла со второго по пятый.

Все символы имени файла, начиная со второго.

Пять символов имени файла, начиная со второго.

Пятый с конца символ в имени файла.

Символы имени файла, начиная со второго и до пятого с конца символа.

[E] - расширение файла. вписывается только в поле "Расширение", или в поле "Имя", если в имя нужно вставить расширение исходного файла.

Счётчик, начинающийся с цифры 01, изменяющийся на единицу при переходе к следующему файлу, и всегда имеющий номер состоящий из двух цифр. Аналогично, команда приписывает к новому имени файла счётчик уже из трёх цифр и с шагом равным двум. То есть 001, 003, 005 и так далее. Счётчик вписывается в новое имя файла там, где вы поставите эту команду в поле "Имя". Например, если написать трек или просто трек , то все выделенные в папке файлы будут переименованы в трек 01, трек 02 и так далее.

[F] - все слова после позиции этой команды в новом имени будут начинаться с заглавной буквы. Например, мы хотим, чтобы все названия треков в папке после номера имели слова с первыми заглавными буквами. тогда в поле "Имя" пишем [f][N] а в поле "Расширение" вписываем [E]. тогда новые файлы будут называться также как и старые, но каждое слово в их именах будет уже начинаться с заглавной буквы.

[d] - добавляет в новое имя текущую дату.

[t] - добавляет в новое имя текущее время.

можно вместо полной даты добавлять только год (команда [Y]), день (команда [D]) или месяц (команда [M]). Команда [y] вставляет год не в виде четырёх цифр, а только в виде двух последних цифр года.

На этом с основными командами можно закончить и перейти к конкретным примерам.

Пример 1

Пусть у нас в папке имеются файлы с расширением.html и мы хотим сменить у всех этих файлов расширение на.txt, чтобы править их в текстовом редакторе. Тогда, выделяем все файлы, нажимаем Control+M, в поле "Имя" вписываем [N], а в поле "Расширение" вписываем txt. проверяем результат в списке рядом с кнопкой "Выполнить" и нажимаем эту кнопку.

Пример 2

Допустим, мы скачали аудиокнигу в виде папки с mp3 файлами, но в имени каждого файла подписано "", и мы хотим убрать у всех файлов этот фрагмент. тогда, в диалоге группового переименования в поле "Имя" вписываем [N], в поле "Расширение" вписываем [E], в поле "Найти" пишем , а поле "Заменить" оставляем пустым. Обратите внимание, что символы левой и правой квадратной скобки нужно обязательно заключать в квадратные скобки, чтобы они не были расценены как начало и конец служебной команды. Проверяем результат в списке перед кнопкой "Выполнить" и нажимаем эту кнопку. Флажок "Подстановка" должен быть обязательно снят, иначе будет заменено на пустое имя всё имя файла, а не только нужный нам кусок.

Пример 3

Пусть у нас имеется папка с музыкальными треками имена которых имеют вид 01 - Song1.mp3, 02 - song2.mp3 и так далее. Мы хотим убрать символ - с последующим пробелом из имён всех файлов, чтобы осталось только 01 Song1.mp3, 02 Song2.mp3 и так далее. Тогда в поле "Имя" пишем [N], в поле "Расширение пишем [E], в поле "Найти" вписываем "- " без кавычек, а поле "Заменить" оставляем пустым. Можно оставить поля "Найти" и "Заменить" пустыми, а в поле "Имя" вписать счётчик и часть старого имени файла без первых пяти символов, в которые и попадает номер трека с тире и пробелом после него. То есть пишем так: . От старого имени файла будет взята только часть, начинающаяся с шестого символа, то есть само название трека.

Пример 4

Пусть у нас есть папка с музыкальными треками имена которых состоят из слов разделённых символом _ без пробелов, а мы хотим убрать изо всех названий символ _ и заменить его на пробел. Тогда Поля "Имя" и "Расширение" заполняем как в первых двух примерах, в поле "Найти" пишем _, а в поле"Заменить" вводим один пробел. В этом случае номера в начале треков останутся без изменения. То есть файлы вида 01_song_about_love.mp3 будут переименованы в 01 song about love.mp3.

Пример 5

Допустим, что мы хотим в папке с музыкальными треками из альбома после номера каждой композиции перед названием песни ещё добавить в каждый файл название альбома. Допустим наш альбом называется "Best of". Тогда в поле "Имя" вписываем: Best of. Здесь мы убираем из старого имени каждого файла первые 2 символа, которые содержат номер трека, а нумерацию приписываем сами. если в папке более 100 треков, то вместо пишем , а вместо пишем уже .

Пример 6

Допустим, что мы пишем курсовую и хотим, чтобы к названию каждого файла в папке с курсовой в конце имени был приписан год, чтобы мы знали по прошествии долгих лет, в каком году мы её написали. тогда в поле "Имя" пишем [N] [Y] Тогда, к примеру файл цели и методы.doc будет переименован в Цели и методы 2012.doc, а файл Заключение.doc в Заключение 2012.doc.

Пример 7

А вот, хороший пример на счётчики. Пусть у нас есть две папки, в папке "чётные" находятся только все отсканированные чётные страницы книги, а в папке "Нечётные" находятся только отсканированные нечётные страницы. Причём, и в той, и в другой папке файлы нумеруются одинаково Page001.jpg и так далее. мы хотим объединить отсканированные страницы из двух папок в одну так, чтобы все страницы шли строго друг за другом, чётная за нечётной. Тогда, в папке "Нечётные" выделяем все файлы и в поле "Имя" вписываем . Здесь мы отрезаем в конце имени каждого файла последние три символа с номером файла, но добавляем свой счётчик, который присваивает уже только нечётные номера. получаем файлы Page001.jpg, Page003.jpg и так далее.

В папке "Чётные" также выделяем все файлы, но, поскольку первой чётной страницей является страница под номером 2, то в поле "Имя" вписываем уже . После переименования получим в этой папке файлы Page002.jpg, Page004.jpg и так далее. Теперь, копируем все файлы из обеих папок в одну общую папку, и получаем правильно рассортированные отсканированные страницы.

Пример 8

Наконец, последний пример. Пусть у нас в папке находятся звуковые файлы названные 01-song1.mp3, 02-song2.mp3 и так далее. мы хотим, чтобы в имени каждого файла перед и после знака тире стоял пробел. если в папке не более 100 треков, то в поле "Имя" вписываем - или и получаем требуемый результат. Поскольку в имени каждого файла тире стоит всегда на третьем месте, то команда всегда будет давать в новом имени именно тире.

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

Чтобы изменить название поля или элемента сводной таблицы, необходимо выделить нужную ячейку и набрать новое имя, которое не должно повторять существующие в исходной таблице имена.

Изменить текст заголовка ячейки А3 на “Сумма продаж”.

· Выделить ячейку А3 сводной таблицы.

· С клавиатуры ввести новое имя.

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

Для формирования новой сводной таблицы достаточно изменить макет существующей.

Сформировать сводную таблицу, отражающую данные о продажах магазинам-закупщикам и доли каждого из них в
общей сумме закупок.

Для этого необходимо выполнить следующие действия.

· На панели инструментов Сводные таблицы (если данная панель инструментов на экране отсутствует, то выполнить команду Вид|Панели инструментов|Сводные таблицы) нажать кнопку со стрелкой Сводная таблица и выбрать Мастер .

· Нажать кнопку Макет .

· При помощи мыши перетащить поле “Наименование магазина” (4-я сверху) в область Строка , поле “Сумма” - в область Данные и сделать двойной щелчок левой кнопкой мыши по данному полю. В открывшемся окне в опции Имя , ввести с клавиатуры “Сумма продаж” и нажать ОК .

· При помощи мыши вторично перетащить поле “Сумма” в область Данные и сделать двойной щелчок левой кнопкой мыши по данному полю. В открывшемся окне, в опции Имя ввести с клавиатуры “Доля магазина”, в этом же окне нажать кнопку Дополнительно>> . В опции Дополнительные вычисления нажать стрелку вниз и выбрать Доля от суммы по столбцу . Нажать ОК .

· В окне макета нажать ОК .

Существующий лист . Нажать кнопку Готово .

· Просмотреть результат (рис.66)

Сформировать сводную таблицу, отражающую данные о средней сумме закупок магазинов по видам техники.

Порядок выполнения действий.

· Установить курсор в любую ячейку созданной сводной таблицы.

· На панели инструментов Сводные таблицы нажать кнопку со стрелкой Сводная таблица и выбрать Мастер .

· Нажать кнопку Макет .

· Для очистки макета оттащить поля в любое место окна.

· При помощи мыши перетащить поле “Наименование магазина” в область Столбец , поле “Вид продукции” - в область Строка , поле “Сумма” - в область Данные и сделать двойной щелчок левой кнопкой мыши по данной кнопке поля. В открывшемся окне в опции Операция выбрать Среднее и нажать ОК .

· В окне макета нажать ОК .

· Нажать кнопку Параметры .

· В открывшемся окне Параметры сводной таблицы в опции Формат удалить флажки Общая сумма по столбцам и Общая сумма по строкам . Нажать ОК .

· В открывшемся окне проверить, что включена опция Существующий лист . Нажать кнопку Готово .

· Просмотреть результат (рис.67).

Групповые операции в сводных таблицах

Создать сводную таблицу с данными о количестве проданных
товаров в определенных диапазонах цен.

Порядок выполнения действий следующий.

· Установить курсор в любую ячейку созданной сводной таблицы.

· На панели инструментов Сводные таблицы нажать кнопку со стрелкой Сводная таблица и выбрать Мастер .

· Нажать кнопку Макет .

· Для очистки макета оттащить поля в любое место окна.

· При помощи мыши перетащить поле “Цена” в область Строка , поле “Количество” - в область Данные и нажать ОК .

· В открывшемся окне проверить, что включена опция Существующий лист . Нажать кнопку Готово .

· Щелкнуть правой кнопкой мыши в ячейке “Цена” полученной сводной таблицы.

· В контекстном меню выбрать команду Группа и структура|Группировать .

· В открывшемся окне Группирование в опции С_шагом ввести c клавиатуры 300 (минимальная и максимальная цена товара - 50 и 800-устанавливается автоматически из списка) и нажать ОК .

· Просмотреть результат (рис.68).

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

Для работы с группой файлов лучше скопировать их в отдельную папку. Это существенно упростит процесс.

С помощью штатных средств Windows

Изменяем имена файлов

Рассмотрим самый простой случай: у нас есть n-ое количество изображений с ни о чём не говорящими названиями.

Приведём имена этих файлов к наглядному виду Picture (n) , где Picture будет названием, а n - порядковым номером. Это можно легко сделать прямо в «Проводнике».

Выделяем все картинки, которые хотим переименовать. Чтобы сделать это быстро, используем комбинацию клавиш Ctrl + A. Затем просто жмём F2 или кликаем правой кнопкой мыши на первом файле и выбираем «Переименовать». В качестве имени для выделенного изображения вводим слово Picture и нажимаем Enter.

Windows автоматически присвоит такое же имя всем последующим файлам и расставит все порядковые номера.

При необходимости вы можете отменить массовое переименование комбинацией клавиш Ctrl + Z.

Если захотите переименовать группу папок в «Проводнике», то это можно сделать точно так же.

Изменяем расширения файлов

Допустим, у нас есть несколько текстовых документов, которые после двойного клика открываются блокнотом. Но нам нужно, чтобы по умолчанию они открывались браузером. Для этого придётся изменить их расширение с.txt на.html. Операции такого рода можно быстро выполнять с помощью командной строки.

Нажимаем клавиши Windows + R, вводим в появившемся поле cmd и кликаем ОК - видим окно командной строки. Теперь вставляем в него путь к папке, в которой лежат наши файлы, сразу после команды cd : cd C:\Users\Max Volotsky\Desktop\Docs , и жмём Enter. Затем используем специальную команду, которая изменит их расширения: rename *.txt *.html , и снова нажимаем Enter.

Первым в команде указывается исходное расширение, вторым - новое. После ввода команды система мгновенно вносит изменения.

Чтобы вернуть всё как было, нужно повторно воспользоваться командой rename, поменяв местами расширения.

С помощью сторонних утилит

Никакие встроенные инструменты не сравнятся по функциональности со специальными программами, разработанными специально для пакетного переименования. В Сети можно найти множество таких бесплатных утилит, достаточно ввести в поиске bulk file rename.

Мы рассмотрим переименование на примере программы Bulk Rename Utility . С её помощью очень удобно изменять имена папок и файлов, а также расширения последних.

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

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

Запускаем приложение и через встроенный файловый менеджер переходим в нужную нам папку. Далее, уже знакомым сочетанием клавиш Ctrl + A выделяем все файлы. Нам нужно удалить первые 3 символа в начале имени каждого файла и дописать название исполнителя в самом начале. Для этого указываем необходимые параметры на панелях Remove и Add, после чего жмём Rename.

Теперь сделаем так, чтобы все расширения состояли из символов в нижнем регистре: на панели Extension выбираем вариант Lower и нажимаем Rename.

Как видите, в окне программы доступны и другие панели. К примеру, Numbering позволяет нумеровать документы различными способами, а с помощью Case можно менять регистр символов в именах файлов. Благодаря столбцу New Name во встроенном проводнике, который показывает результат переименования ещё до его применения, вы легко разберётесь в остальных инструментах Bulk Rename Utility.

Последнюю операцию с файлами и папками, выполненную в программе, можно легко отменить комбинаций клавиш Ctrl + Z.