How to find out if an Object is a collection or single value?

If you are using a data binding, you do not need to know. = will work in either case, so you don't need to ever switch to IN for the use case you outline above. I used the following code in Execute Anonymous to demonstrate:

Object searchTerm = new Set<String> { 'test', 'test2' };
SObjectField field = Account.Name;
SObjectType sObjectType = Account.sObjectType;

system.debug(Database.query(
    'SELECT Id FROM ' + sObjectType + ' WHERE ' + field + ' = :searchTerm'
));

I also used searchTerm = 'test'; and the query ran just fine in either case.

Another alternative might be this:

public static Boolean isCollection(Object input)
{
    Object test;
    try{
       test = (List<Object>)input;
       return true;
    }
    catch(System.TypeException ex){}
    return false;
}

This should work for any Object. Note that this does not work for Sets or Maps, which both return conversion errors regardless. You could also modify it to the following:

public static Boolean isCollection(Object input)
{
    Object test;
    try{
       test = (List<Object>)input;
       return true;
    }
    catch(System.TypeException ex)
    {
        String message = ex.getMessage();
        return message.contains('Set<') || message.contains('Map<');
    }
    return false;
}

If you really want to determine whether an Object instance is a collection of primitives, it would be a somewhat inelegant || chain:

public static Boolean isCollection(Object input)
{
    return input instanceof Set<String> || input instanceof List<String> ||
         input instanceof Set<Id> || input instanceof List<Id> ||
         input instanceof Set<Date> || input instanceof List<Date> ||
         input instanceof Set<Datetime> || input instanceof List<Datetime> ||
         input instanceof Set<Decimal> || input instanceof List<Decimal> ||
         input instanceof Set<Integer> || input instanceof List<Integer> ||
         input instanceof Set<Double> || input instanceof List<Double> ||
         input instanceof Set<Long> || input instanceof List<Long>;
}

You could add a few more instanceof checks, but it's unclear how sensical they would be, for example Set<Boolean>, List<Boolean>, Set<SObject>, List<SObject>, etc.