Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / исключение повторяющихся значений / 21 сообщений из 21, страница 1 из 1
21.08.2012, 23:41
    #37925390
Troymass
Troymass
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Помогите пожалуйста.
Необходима формула для счета значений по двум столбцам, с условием посчета одинаковых значений единожды. Например:
Петров 27
Сидоров 16
Петров 28
Петров 27
Сидоров 16
Сидоров 19
Иванов 29
Максимов 56
Сидоров 16

Результат должен быть:
Петров 2
Сидоров 2
Иванов 1
Максимов 1
Значений больше 1000
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2012, 01:54
    #37925442
lbppb
lbppb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Если речь об Excel и если я правильно понял задачу, то так.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2012, 03:35
    #37925489
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Вообще, поиск по форуму помогает...

И, как я понял, результат должен быть примерно таким...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2012, 22:44
    #37927154
Troymass
Troymass
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Прошу прощения, неправильно объяснил. Необходим счет ячеек в первом столбце по фамилиям. При совпадении значений 1 и 2 столбца то значение должно считать только один раз.
Например:
Петров 27
Петров 27
Петров 27
= 1

Сидоров 16
Сидоров 23
Сидоров 16
Сидоров 23
Сидоров 10
= 3
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2012, 23:31
    #37927184
Казанский
Казанский
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
1. В ст. С соединяем фамилию и число формулой.
2. Выделяем ст. А, с помощью Расширенного фильтра - Только уникальные записи создаем в ст. F список уникальных фамилий.
3. Выделяем ст. С, тем же методом создаем в ст. I список уникальных соединений фамилия+число.
4. В ст. G формулой получаем результат.

Если эта задача не разовая, то, конечно, макрос надо составить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.08.2012, 23:41
    #37927193
Казанский
Казанский
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Проще.
1. Выделяем ст. А, с помощью Расширенного фильтра - Только уникальные записи создаем в ст. F список уникальных фамилий.
2. Выделяем ст. А и В, с помощью Расширенного фильтра - Только уникальные записи создаем в ст. I и J список уникальных записей фамилия+число.
3. В ст. G простой формулой получаем результат.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 00:29
    #37927231
Troymass
Troymass
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Спасибо за помощь, но оба варианта не подходят. Фамилий всего 5 но значений в столбце б от 0 до 10000. Причем, допустим, Петров 16 появится только 1 раз, а Петров 120 появится 200 раз. Как говорил ранее значений, на данный момент, уже почти тысяча и каждый день добавляются. Результат необходим каждый день, а люди, которые работают с таблицей, с екселем практически незнакомы. Есть формула
=СУММ(ЕСЛИ(СЧЁТЕСЛИМН(N3:N14;N3:N14;O3:O14;O3:O14)="0";" ";1/СЧЁТЕСЛИМН(N3:N14;N3:N14;O3:O14;O3:O14))) но она считает общее количество, учитывая оригинальные значения, а мне нужно тоже самое только по каждой из пяти фамилий.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 00:47
    #37927240
lbppb
lbppb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Можно вот так выхрюкнуться, но не в одну формулу, правда до кучи и список уникальных фамилий, в общем разбирайтесь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 00:48
    #37927241
Казанский
Казанский
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
> Причем, допустим, Петров 16 появится только 1 раз, а Петров 120 появится 200 раз

Описанная выше методика даст Петров 2. Что не так?
Если фамилии известны заранее, то этап выделения уникальных фамилий можно пропустить.
Остается только выделить уникальные записи и посчитать повторение фамилии простой формулой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 00:54
    #37927243
Hugo121
Hugo121
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Я сделал с помощью макроса http://www.planetaexcel.ru/forum.php?thread_id=26105 таким образом:
1. Объединил =A1&"|"&B1, левее протянул единицы (даблкликом делаете в 5 сек.)
2. По этим столбцам отработал макрос, получил
Петров|27 2
Сидоров|16 3
и т.д.
3. Разбил уже этот первый столбец по разделителям, во втором снова протянул единицы (это нужно макросу, но его можете переписать, если лениво единицы тянуть)
4. Теперь уже по этим столбцам отработал макрос - готово.

Можно этот макрос переписать, чтоб сразу из исходных данных получался результат. Дерзайте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 00:55
    #37927245
lbppb
lbppb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
AndreTMВообще, поиск по форуму помогает...

И, как я понял, результат должен быть примерно таким...

Вообще вот так =COUNTIFS($A$1:$A$9;A1;$B$1:$B$9;B1) воспринимается проще, чем вот так =SUMPRODUCT(($A$1:$A$9=A1)*($B$1:$B$9=B1)*1), к тому же, а 1 в конце для чего?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 00:57
    #37927247
lbppb
lbppb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
lbppbМожно вот так выхрюкнуться, но не в одну формулу, правда до кучи и список уникальных фамилий, в общем разбирайтесь.

Чё-т плохо файл приложился, попытка 2.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 00:59
    #37927250
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
А, понял... Вам надо найти количество "однофамильцев" ...
А обязательно именно формулой? Поскольку вы результат всё равно получаете в другом месте листа - то почему бы не сделать, например, запрос через MSQuery?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 01:15
    #37927258
Казанский
Казанский
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Макрос со словарем коллекций
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
Sub bb()
Dim v(), x, i&
v = Range("A1", Cells(Rows.Count, "B").End(xlUp)).Value
On Error Resume Next
With CreateObject("scripting.dictionary")
    For i = 1 To UBound(v)
        If Not .exists(v(i, 1)) Then .Add v(i, 1), New Collection
        .Item(v(i, 1)).Add 0, CStr(v(i, 2))
    Next
    ReDim v(1 To .Count, 1 To 2)
    i = 0
    For Each x In .keys
        i = i + 1
        v(i, 1) = x
        v(i, 2) = .Item(x).Count
    Next
End With
Worksheets.Add(after:=ActiveSheet).[A1].Resize(i, 2).Value = v
End Sub
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 01:30
    #37927262
Hugo121
Hugo121
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Точно, есть ведь запросы.
Можно его в отдельном файле сохранить, настроенным на определённый файл (смотрите/меняйте в блокноте, сейчас заточено на C:\Temp\964122.xls)
Можно запускать даблкликом или открывать Экселем.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.08.2012, 01:33
    #37927263
lbppb
lbppb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Hugo121Точно, есть ведь запросы.
Можно его в отдельном файле сохранить, настроенным на определённый файл (смотрите/меняйте в блокноте, сейчас заточено на C:\Temp\964122.xls)
Можно запускать даблкликом или открывать Экселем.

Да, мне с запросом тоже понравилось, я признаться думал об этом, но, что запрос, что маккрос требуют разрешений или настроек безопасности, а у меня только средствами excel и все работает
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.08.2012, 18:45
    #37930839
Troymass
Troymass
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
AndreTMпочему бы не сделать, например, запрос через MSQuery?
Каким образом?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.08.2012, 18:47
    #37930840
Troymass
Troymass
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Hugo121Точно, есть ведь запросы.
Можно его в отдельном файле сохранить, настроенным на определённый файл (смотрите/меняйте в блокноте, сейчас заточено на C:\Temp\964122.xls)
Можно запускать даблкликом или открывать Экселем.
Не получаеться пишет ошибка.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.08.2012, 19:49
    #37930866
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
TroymassAndreTMпочему бы не сделать, например, запрос через MSQuery?Каким образом?Я же пример приложил. Только, скорее всего, вам придется пересоздать запрос уже в вашем местоположении файла - но сам текст SQL-запроса можете взять прямо из примера...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.08.2012, 20:11
    #37930876
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
Например, для таблицы данных на листе с именем "Лист1":
Фамилия НомерПетров 27Сидоров 16Петров 28Петров 27Сидоров 16Сидоров 19Иванов 29Максимов 56Сидоров 16
Делаем SQL-запрос:
Код: vbnet
1.
2.
3.
4.
SELECT Фамилия, count(*) as Количество
FROM (SELECT DISTINCT `Лист1$`.Фамилия, `Лист1$`.Номер
FROM `C:\2\964122\964122.xlsx`.`Лист1$` `Лист1$`)
GROUP BY Фамилия

И всё...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.08.2012, 23:03
    #37930976
Troymass
Troymass
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
исключение повторяющихся значений
[quot AndreTM]
Разобрался, все подошло. Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / исключение повторяющихся значений / 21 сообщений из 21, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 124ms
Закрыть
start [/forum/topic.php?fid=61&mobile=1&tid=2175442]:
 0ms
get settings:
 8ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 26ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 1ms
others: 13ms
total:  124ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]