Excel: вопрос - ответ : Закупщик

Закупщик56160 · █ 15.06.2013 09:15

Добрый день, коллеги, у меня возникла следующая проблема, меня на работе озадачили созданием файла по подсчёту производительности и % качества работы склада, в принципе нужные формулы я написал, но есть следующая проблема: у меня есть 2 файла: «статистика» (там происходит суммирование всех ошибок совершенных на складе на конкретную дату определённой командой) и фаил «ошибки» (куда вносятся все ошибки с ответственными лицами). Формулы из 1-го файла ссылаются на данные из второго.
Проблема в заключается в следующем, для предотвращения всякого рода подтасовок (т. к. от показателей будет зависеть премия) файлами пользуются разные люди в разное время, в результате, когда менеджер первым открывает фаил «статистика», но при этом закрыт фаил «ошибки», формула в столбце «сумма ошибок» - #ЗНАЧ!, и только при открытии фаила «ошибки» появляются данные. Можно как-нибудь наладить связь между 2-мя файлами, чтобы при закрытых «ошибках» в файле «статистика» всегда была достоверная информация. Пример с формулами во вложении, откройте первым фаил «статистика».
Спасибо.

tgm · █ 15.06.2013 18:17

Добрый день, Петр. Проблема в формуле СУММЕСЛИ,к сожалению она не работает с закрытой книгой. Есть обходной вариант -через формулу СУММПРОИЗВ.
=СУММПРОИЗВ(('C:\[ошибки.xlsx]Лист1'!$C2:$C1001=C2)*(('C:\[ошибки.xlsx]Лист1'!$D2:$D1001)))
В Вашем случае, она заменила всю формулу.
Ну и файл на всякий случай:

Закупщик56160 · █ 15.06.2013 18:41

Спасибо.

Закупщик56160 · █ 17.06.2013 11:15

А можно усложнить условие формулы при подсчёте ошибок, например, чтобы она реагировала на критерий «Да» и подсчитывала только те количества ошибок, напротив которого стоит этот критерий.
Спасибо.

Aleksandr H. · █ 17.06.2013 13:41

Цитата:

Пётр А можно усложнить условие формулы при подсчёте ошибок, например, чтобы она реагировала на критерий «Да» и подсчитывала только те количества ошибок, напротив которого стоит этот критерий.
Спасибо.

В файле "статистика" в столбце С:

Код:

=A2&B2&"да"

В файле "ошибки" в столбце D:

Код:

=A2&C2&B2

Закупщик56160 · █ 17.06.2013 14:06

Спасибо.

Aleksandr H. · █ 19.06.2013 12:26

Цитата:

sf13 Или (что то же самое):

Dim n As Range
For Each n In Selection
MyValue = n
MyValue = Replace(MyValue, " ", "")
n.NumberFormat = "@"
n = MyValue
Next:

Есть ли возможность как-ли оптимизировать єтот простой код, а именно ускорить время выполнения? Для массива 6294х4 время выполнения 12 мин :? . В то же время код для OpenOffice Scalc справляется с задачей за чуть больше 1 сек. :)

Код:

REM  *****  BASIC  *****
sub ToText
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "NumberFormatValue"
args1(0).Value = 100

dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(17) as new com.sun.star.beans.PropertyValue
args2(0).Name = "SearchItem.StyleFamily"
args2(0).Value = 2
args2(1).Name = "SearchItem.CellType"
args2(1).Value = 0
args2(2).Name = "SearchItem.RowDirection"
args2(2).Value = true
args2(3).Name = "SearchItem.AllTables"
args2(3).Value = false
args2(4).Name = "SearchItem.Backward"
args2(4).Value = false
args2(5).Name = "SearchItem.Pattern"
args2(5).Value = false
args2(6).Name = "SearchItem.Content"
args2(6).Value = false
args2(7).Name = "SearchItem.AsianOptions"
args2(7).Value = false
args2(8).Name = "SearchItem.AlgorithmType"
args2(8).Value = 0
args2(9).Name = "SearchItem.SearchFlags"
args2(9).Value = 71680
args2(10).Name = "SearchItem.SearchString"
args2(10).Value = " "
args2(11).Name = "SearchItem.ReplaceString"
args2(11).Value = ""
args2(12).Name = "SearchItem.Locale"
args2(12).Value = 255
args2(13).Name = "SearchItem.ChangedChars"
args2(13).Value = 2
args2(14).Name = "SearchItem.DeletedChars"
args2(14).Value = 2
args2(15).Name = "SearchItem.InsertedChars"
args2(15).Value = 2
args2(16).Name = "SearchItem.TransliterateFlags"
args2(16).Value = 1280
args2(17).Name = "SearchItem.Command"
args2(17).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args2())


end sub

Закупщик56160 · █ 22.06.2013 13:52

Добрый день, коллеги, получил задание от шефа сделать фаил по расчёту % качествапоставок (недопоставки/заказано). Трудность заключается в том, что не должны использоваться сводные таблицы, но критериев подсчёта целых 5:
1) Специалист 2) Акт 3)Сегмент 4) Поток 5) Гамма

Приоритетный критерий специалист, далее % качества считается по активностям, сегментам, потокам и гаммам. С помощью формул массива я посчитал % качества по всем параметрам.
Скажите, пожалуйста, реально ли вычислит % лишь по комбинации нескольких критериев, например: СПЕЦИАЛИСТ – АКТИВНОСТЬ –ПОТОК; СПЕЦИАЛИСТ-АКТИВНОСТЬ-СЕГМЕНТ-ГАММА.
Или без сводной таблицы не обойтись? Пример во вложении. Спасибо.

RazVal · █ 22.06.2013 14:26

Цитата:

Пётр получил задание от шефа сделать фаил по расчёту % качества поставок (недопоставки/заказано). Трудность заключается в том, что не должны использоваться сводные таблицы, но критериев подсчёта целых 5:
1) Специалист 2) Акт 3)Сегмент 4) Поток 5) Гамма

Приоритетный критерий специалист, далее % качества считается по активностям, сегментам, потокам и гаммам. С помощью формул массива я посчитал % качества по всем параметрам.
Скажите, пожалуйста, реально ли вычислит % лишь по комбинации нескольких критериев, например: СПЕЦИАЛИСТ – АКТИВНОСТЬ –ПОТОК; СПЕЦИАЛИСТ-АКТИВНОСТЬ-СЕГМЕНТ-ГАММА.
Или без сводной таблицы не обойтись?..

Не очень понятна задача - было бы проще, если бы нужный результат был бы изображён хотя бы с помощью сводных таблиц. Кстати, непонятно и почему нельзя через них?
Но, вообще, в своё время делал ручные сводные таблицы по нескольким параметрам с помощью функций СЦЕПИТЬ, СУММЕСЛИ и СЧЁТЕСЛИ. Этот пример можно посмотреть в .

Закупщик56160 · █ 22.06.2013 19:22

Спасибо, я сам разобрался :)

	статистика.xlsx (12.1 Кб, 150 просмотров)
	ошибки.xlsx (11.2 Кб, 149 просмотров)

	статистика.xlsx (12.0 Кб, 122 просмотров)
	ошибки.xlsx (11.4 Кб, 126 просмотров)