Консультации Портала - Консультация #185149

Задать вопрос Консультации Пользователи Форум

Задать вопрос экспертам

Консультация № 185149

11.01.2012, 23:28

67.56 руб.

12.01.2012, 20:32

0 13 3

Компьютеры Программное обеспечение

Уважаемые эксперты! Пожалуйста, ответьте на вопрос:
Имеется рапорт сделанный в excel.
В нем необходимо отсортировать данные по табельному номеру (по возрастанию).
Но при сортировке столбец Т строка 17, выдается сообщение, что (для этого нужно чтобы ячейки имели одинаковый размер).
Как исправить эту проблему?

Файл можно скачать по этой ссылке: https://rfpro.ru/upload/7236

Обсуждение

Неизвестный

12.01.2012, 00:02

общий

это ответ

Здравствуйте!
Действительно программа Excel не может отсортировать данные, если размеры ячеек не совпадают.
В вашем отчете это есть:столбцы с C до R отличаются от столбца T.
Эти столбцы изменить, как видно из отчёта, сложно и почти невозможно.

Необходимо или вручную это сортировать, или программно(написать отдельную программу, которая обработала бы эти данные).

Есть ещё другая возможность: необходимо все ячейки объединять(изменять столбцы с C до R).
(как пример:
берем столбец С, объединяем 17 и 18 строки, ставим свойство перенос текста осталось значение только б, если через пробел написать х, то при включенном свойстве перенос текста. Получим почти такой же вид как и было. х будет чёрным).
Если все столбцы так переделать, то вы сможете отсортировать, так как вам требуется.

В любом случае требуется большая переработка отчёта перед тем как сможете отфильтровать.

давно

Лысков Игорь Витальевич

Посетитель
7438
7205

12.01.2012, 00:17

общий

Как вариант:
1) перенести заголовок второй страницы вниз;
2) отсортировать
3) вернуть заголовок на место

Об авторе:
"Если вы заметили, что вы на стороне большинства, —
это верный признак того, что пора меняться." Марк Твен

давно

Лысков Игорь Витальевич

Посетитель
7438
7205

12.01.2012, 00:40

общий

А при чем тут столбцы C-R, когда надо отсортировать по столбцу T?

Неизвестный

12.01.2012, 05:24

общий

12.01.2012, 05:30

Как вариант сделать копию файла, в нем удалить столбцы с С по R (это которые не объединенные), удалить шапку. Да и про столбец Z не забудьте он тоже мешает. Потом упорядочить... Ну а потом уже копи-пастом вставить графики, вручную с совпадением табельных номеров, благо таблица небольшая (или это не вся таблица?), ну и шапку обратно вернуть.
Столбцы с C по R очень даже при чем, потому что их тоже надо упорядочить, но они имеют другой размер.

P.S. Для упрощения упорядочивания еще предлагаю, добавить столбец перед А (то есть самый первый) табельные номера скопировать в него, а потом после сортировки убрать

Неизвестный

12.01.2012, 06:23

общий

Адресаты:

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

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

Неизвестный

12.01.2012, 06:59

общий

Все зависит от того, насколько реален прилагаемый файл.
Если в Вашем файле реально содержится всего 29 сотрудников и сортировку необходимо выполнить всего один раз, то тогда конечно же проще всего скопировать этот файл в другой, почистить во втором файле все данные о сотрудниках, не трогая шапки. Затем внимательно копировать каждого сотрудника из первого файла во второй, следя за порядком табельных номеров.
Если же в Вашем файле реально содержится к примеру несколько сотен сотрудников и/или сортировку необходимо выполнять регулярно, то самый лучший вариант - это написать макрос для этой цели.

Неизвестный

12.01.2012, 08:03

общий

это ответ

Здравствуйте, Посетитель - 380882!
Вот результат поставленной Вами задачи
скачать файл 7236.xls [151.5 кб]

Касаемо вопроса, это сделать реально и достаточно не трудно, НО требуются навыки программирования на VBA (Visual Basic for Application), чем я собственно и воспользовался.
Теперь непосредственно касаемо Вашего воспроса, а точнее порядок выполнения таких сортировок:
1. Копируем первый лист и строку заголовка (но не всю а только ту что содержит А, Б, В, 1, 2, 3, ..., 28) на вновь созданный лист в файле результата я его назвал "времянка";
2. Копируем данные со второго листа (ВНИМАНИЕ! уже без строки заголовка см. пункт 1);
3. Запускаем скрипт (написанный мною, он содержится в файле, ссылку на который я дал, на случай если захотите пересортировать по другому столбцу) который называется "ПодготовитьДляСортировкиПоЛюбомуСтолбцуСДанными";
4. Сортируем по нужному столбцу;
5. Запускаем скрипт (написанный мною, он содержится в файле, ссылку на который я дал, на случай если захотите пересортировать по другому столбцу) который называется "ВернутьВИсходныйФорматДляОбратногоПереноса";
6. Копируем результат в следующем порядке строки (по нумерации MS Excel):
6.1. со 2 по 35 в первый лист (на исходном листе, он у Вас называется "рапорт1";
6.2. с 36 по последнюю (на текущий момент, иначе очередные 34 строки - по нумерации MS Excel) во второй лист (если строк станет более то на каждый из следующих листов) (на исходном листе, он у Вас называется "рапорт1".

После чего получаем лист "рапорт1" в том же формате, но отсортированный так как Вам нужно (в данный момент это сортировка по столбцу T)

PS. Не пугайтесь третего макроса в выложенном мною файле "fnNum2ColName" - это вспомогательная функция для двух других, она преобразует числовой номер столбца в буквенное его представление (например: 1 = A, 26 = Z и т.д.)

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

Best Regards Crazy_Knight

Неизвестный

12.01.2012, 08:05

общий

Для решения подобных проблем давно существуют макросы на VBA и многие ими во всю пользуются

Неизвестный

12.01.2012, 08:16

общий

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

Неизвестный

12.01.2012, 08:20

общий

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

давно

Megaloman

Модератор
137394
1850

12.01.2012, 17:03

общий

это ответ

Здравствуйте, Посетитель - 380882!
ИМХО средствами стандартной сортировки проблему не решить, так как у Вас
1. Данные в таблице разорваны шапкой, по каждой странице Вы требуете итоги.
2. Строки по структуре неоднородны - часть ячеек в строке являются объединёнными ячейки. По видимому, для сортировки это двусмысленная ситуация.
Вот текст макроса, решающего проблему.

Код:

TNum = "T"                           ' Здесь указываем столбец, по которому сортируем
Mass = Array(17, 50, 56, 79)                             ' Здесь указываем попарно строки с которых начинаются и кончаются данные на странице
Delta = 10000                                             ' Здесь указываем начальную строку для создания вспомогательной копии таблицы

N1 = LBound(Mass)
N2 = UBound(Mass)

M = 0
For i = N1 To N2 Step 2
   M = M + Mass(i + 1) - Mass(i) + 1
Next
M = M / 2 - 1

ReDim sIshod(M), sNew(M), mTab(M)

k = -1
For i = N1 To N2 Step 2
   For j = Mass(i) To Mass(i + 1) Step 2
        k = k + 1
        sIshod(k) = j
        sNew(k) = j
        mTab(k) = Range(TNum + CStr(j))
   Next
Next


For i = 0 To M
    For j = i To M
        If mTab(i) > mTab(j) Then
            k = mTab(i)
            mTab(i) = mTab(j)
            mTab(j) = k
            k = sIshod(i)
            sIshod(i) = sIshod(j)
            sIshod(j) = k
        End If
    Next
'    MsgBox CStr(mTab(i)) + "   " + CStr(sIshod(i))
Next

Rows(CStr(Mass(N1)) + ":" + CStr(Mass(N2))).Copy
Rows(CStr(Delta)).Insert Shift:=xlDown

For i = 0 To M
    R = Delta - Mass(0) + sIshod(i)
    Rows(CStr(R) + ":" + CStr(R + 1)).Copy
    Rows(CStr(sNew(i)) + ":" + CStr(sNew(i) + 1)).Select
    ActiveSheet.Paste
Next

Rows(CStr(Delta) + ":" + CStr(Delta - Mass(N1) + Mass(N2))).Delete ' Shift:=xlUp
Range("A1").Select

Для его использования создаёте макрос, вставляете туда текст кода, выполняете макрос.
В операторе Mass = Array(17, 50, 56, 79) указываете попарно строки начала и конца кусков с данными. У Вас на первой странице данные с 17 по 50 строку, на второй с 56 по 79
Таких кусков можете указывать сколько нужно. Я не делал никакой защиты на чётность - естественно, строк в кусках должно быть четное количество, так как по Вашей постановке объединяются ячейки попарно по высоте.
TNum = "T" - здесь указан столбец, кде табельные номера

Макрос работает след образом - Ваши данные копируются вниз со строки Delta = 10000
Затем в массиве вычисляется отсортированный порядок следования таб номеров.
Из копии в соответствии с вычисленным порядком в основную таблицу переносятся данные.
Копия таблицы удаляется.
В прикрепл файле - таблица с макросом

Прикрепленные файлы:

185149-269553.xls

скачать (105.00 кБ)

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Megaloman

Модератор
137394
1850

12.01.2012, 19:14

общий

12.01.2012, 19:21

Адресаты:

Цитата: Лысков Игорь Витальевич

Как вариант:
1) перенести заголовок второй страницы вниз;
2) отсортировать
3) вернуть заголовок на место

Не получится из-за объединения ячеек в строке. Получим сообщение, что (для этого нужно чтобы ячейки имели одинаковый размер).

Цитата: Лысков Игорь Витальевич

А при чем тут столбцы C-R, когда надо отсортировать по столбцу T?

При том, что надо отсортировать не только данные в столбце с табельными номерами, но чтобы и все остальные данные в строке с табельным номером были правильными.

Цитата: Алексей К.

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

Может, Вы и правы, но человек заплатил деньги, чтобы получить решение и ответить, что сделать этого нельзя как-то, по моему мнению (модераторы меня поправят?) неприлично.
Вообще, мне кажется, что изначально задача не очень и нужна - если эту таблицу формирует некий программный комплекс, то, по моему мнению, он мог бы это сделать и в отсортированном виде. Если руками - сделать шаблон, где таблица уже отсортирована по табельным номерам.

Об авторе:
Понеже не словес красных бог слушает, но дел наших хощет

давно

Лысков Игорь Витальевич

Посетитель
7438
7205

12.01.2012, 19:22

общий

Адресаты:

ок

Форма ответа

Отправка постов/ответов доступна только зарегистрированным и подтвержденным пользователям.

Если Вы уже зарегистрированы на Портале - войдите в систему, если Вы еще не регистрировались - пройдите простую процедуру регистрации.