Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

18 Октябрь 2018, 01:49 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Доступно и просто о работе в офисных пакетах
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Обработка групп ячеек в строке по значениям из соседней строки  (Прочитано 397 раз)
0 Пользователей и 1 Гость смотрят эту тему.
iyugov
Участник
**
Offline Offline

Пол: Мужской
Расположение: Тверь
Сообщений: 14



« Стартовое сообщение: 12 Май 2018, 01:17 »

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

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

Пример прилагается.

* example.ods (17.07 Кб - загружено 12 раз.)
Записан
JohnSUN
Капитана в тот день называли на "ты"
Гуру
*******
Offline Offline

Пол: Мужской
Расположение: Киев
Сообщений: 2 592


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #1: 12 Май 2018, 13:04 »

Ну, если честно, то строка с номерами групп не совсем "соседняя"  Смеющийся
Если "чисто поржать", то впиши в U4 формулу
Код:
=T(IF(SUMPRODUCT(COUNTIF(FREQUENCY(TRANSPOSE(IF(ISNUMBER($B4:$T4);$B$3:$T$3;""));ROW(INDIRECT("$A$1:$A$"&MAX($B$3:$T$3))));1))=MAX($B$3:$T$3);STYLE("Good");STYLE("Error")))
и растяни на весь список учеников (ширину колонки U можно сделать маленькой, миллиметра 4 - это только индикатор)

Если не поржать, а для реальной повседневной работы, то лучше макрофункцию использовать. Что-то вроде
Код:
Function Marks_is_valid(aGroups As Variant, aMarks As Variant) As String
Dim i As Long, aTmp As Variant, sRes1 As String, sRes2 As String 
Marks_is_valid = "*** Ошибка в вызове функции! ***"
If UBound(aGroups,1) <> 1 Then Exit Function
If UBound(aMarks,1) <> 1 Then Exit Function
If UBound(aGroups,2) <> UBound(aMarks,2) Then Exit Function
aTmp = Array()
For i = LBound(aGroups,2) To UBound(aGroups,2)
If not IsEmpty(aGroups(1,i)) Then FindOrAddVal(aGroups(1,i), aTmp, IsMark(aMarks(1,i)))
Next i
sRes1 = ""
sRes2 = ""
For i = LBound(aTmp) To UBound(aTmp)
If aTmp(i)(1) < 1 Then sRes1 = sRes1 & aTmp(i)(0) & " "
If aTmp(i)(1) > 1 Then sRes2 = sRes2 & aTmp(i)(0) & " "
Next i
Marks_is_valid = ""
If sRes1 <> "" Then sRes1 = "В " & sRes1 & " мало оценок "
If sRes2 <> "" Then sRes1 = sRes1 & "В " & sRes2 & " много оценок"
If sRes1 = "" Then
Marks_is_valid = "Всё ОК"
Else
Marks_is_valid = sRes1
EndIf
End Function

Sub FindOrAddVal(key As Variant, aData As Variant, add As Integer)
Dim l&, r&, m&, N&, i&
l=LBound(aData)
r=UBound(aData)+1
N=r
While (l<r)
m=l+Int((r-l)/2)
If aData(m)(0)<key Then
l=m+1
Else
r=m
EndIf
Wend
If r=N Then
ReDim Preserve aData(0 To N)
aData(N) = Array(key, add)
ElseIf  aData(r)(0)=key Then
aData(r)(1) = aData(r)(1) + add
Else
ReDim Preserve aData(0 To N)
For i = N-1 To r Step -1
aData(i+1)=aData(i)
Next i
aData(r) = Array(key, add)
EndIf
End Sub

Function IsMark(vTest As String) As Integer
IsMark = 0
If (Len(Trim(vTest))=1) And (Instr("12345",Trim(vTest))>0) Then IsMark = 1
End Function

Вызывать её в ячейке так:
Код:
=MARKS_IS_VALID($B$3:$T$3;$B4:$T4)
Здесь первый параметр это диапазон ячеек с номерами групп, а второй - строка для одного ученика (в данном случае из 4-ой строки).
К слову сказать, с такой функцией ты не ограничен одними только номерами групп - можешь обозначать их как хочешь, хоть буквами, хоть целыми словами
« Последнее редактирование: 12 Май 2018, 13:29 от JohnSUN » Записан

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
iyugov
Участник
**
Offline Offline

Пол: Мужской
Расположение: Тверь
Сообщений: 14



« Ответ #2: 12 Май 2018, 14:16 »

Да, строка не обязательно соседняя. Трудно было сформулировать тему.

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

По каким соображениям функция-макрос предпочтительна? Только из-за универсальности? Или она ещё и быстрее работать будет?
Записан
JohnSUN
Капитана в тот день называли на "ты"
Гуру
*******
Offline Offline

Пол: Мужской
Расположение: Киев
Сообщений: 2 592


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #3: 12 Май 2018, 14:53 »

Да нет, скорость что там, что там одинаково большая, глазом моргнуть не успеешь.
Просто на текст формулы глянь: его не то что понять - за один раз и прочитать-то сложно. А теперь представь, что у тебя ведомость человек на 350 учеников и на 25 предметов. Всё заполнил, формулу вписал и пересчитал - всё вроде бы нормально. И тут вспоминаешь, что нужно было еще два факультатива добавить, три отметки за поведение и ещё какую-то ерунду. И после этого формулу нужно будет изменить - колонок-то теперь другое количество. Как быстро сможешь найти все места, где нужно вносить изменения, и насколько правильно эти изменения с первого раза внесёшь?
Другое дело макрофункция - да, писать её немного дольше, чем конструировать формулу. Но у неё всего-то два параметра! И если нужно что-то изменить в адресах ячеек - то вот они, все перед глазами, ошибиться трудно
Записан

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
iyugov
Участник
**
Offline Offline

Пол: Мужской
Расположение: Тверь
Сообщений: 14



« Ответ #4: 13 Май 2018, 07:49 »

Пока разбираюсь и с формулой, и с макросом.

Вот это:
Код:
ROW(INDIRECT("$A$1:$A$"&MAX($B$3:$T$3))))
выдаёт 1 независимо от значения функции MAX. Можно заменить вызов ROW() единицей?
Записан
mikekaganski
Мастер
*****
Offline Offline

Пол: Мужской
Расположение: Хабаровск -> Москва
Сообщений: 1 063


« Ответ #5: 13 Май 2018, 08:36 »

Вот это:
Код:
ROW(INDIRECT("$A$1:$A$"&MAX($B$3:$T$3))))
выдаёт 1 независимо от значения функции MAX. Можно заменить вызов ROW() единицей?

... выдаёт в какой строке?
Записан

С уважением,
Михаил Каганский
JohnSUN
Капитана в тот день называли на "ты"
Гуру
*******
Offline Offline

Пол: Мужской
Расположение: Киев
Сообщений: 2 592


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #6: 13 Май 2018, 11:09 »

Вот это:
Код:
ROW(INDIRECT("$A$1:$A$"&MAX($B$3:$T$3))))
выдаёт 1 независимо от значения функции MAX. Можно заменить вызов ROW() единицей?
Можно, конечно можно! Только после такой замены формула перестанет работать  Смеющийся
Почему? А вот подсказка:
... выдаёт в какой строке?
Действительно - в ПЕРВОЙ строке всегда будет единица. А во второй - двойка, в третьей - тройка. Фокус в том, что вся эта конструкция, о которой ты спрашиваешь, возвращает не число, а массив. Длина этого массива - максимальное число в строке номеров групп. Сейчас у тебя две группы - 1 и 2 - получим массив из двух значений. Было бы, допустим, 1 и 4 - получили бы массив из четырёх последовательных значений {1|2|3|4}
Я постарался показать на картинке, как увидеть эти {1|2} для твоего примера.
Это массив индексов, второй параметр для функции FREQUENCY, которая подсчитает сколько чисел (оценок) попадает в каждую из групп.

Да, похоже, что "чисто поржать" не получилось - не все шутки одинаково смешны...
Ну, ты продолжай ковырять формулу - если разберёшь её до конца, то узнаешь о Calc'е немного нового. Типа, "всё что нас не убивает делает нас сильнее" (мне больше нравится вариант "Всё, что меня не убило, пусть прячется - теперь моя очередь"  Подмигивающий )


* ИспользованиеМастераФункций.png (36.37 Кб, 639x592 - просмотрено 12 раз.)
Записан

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!