I hear a lot of people talking about how crappy Explorers and Ironclads are, and they certainly arn't the most generally useful units around. Still, I think it's important to distinguish between units that are not very useful in certain games, and those that are not very useful in any game because better units tend to appear at the same time, or slightly before or after them.
Two prime examples of units that are highly useful under the right conditions, and for a fairly long period of time, are the Explorer and Ironclad. The Explorer is a dandy little guy in games where oceans make major landmasses unreachable until the mid-game, such as terra maps or island maps with spares floating around. They can be carried by caravals, and as such might be the only land units you can get across the ocean for a good 50 turns or so, and with woodsman and geurilla II they sure do explore fast.
Ironclads can also be pretty useful in games where costal waters are heavily contested, such as close islands or pangea-type maps whith lots of inlets. The difference between power 8 Firigates and power 12 Ironclads may not seem impressive, but that translates to about a 90% win rate for the Ironclad. If you stealth-build a little stack of these guys while your opponent waits for oil-based navies (which are probably a good 20-30 turns away) you'll rule those coasts for a some time.
Two prime examples of units that arn't useful because they appear around the same time as better units would be Musketmen and Machine-Guns.
Musketmen are a good unit when taken on thier own, but the problem is that you'll almost surely research chemistry next to get grenadiers (which are pretty freakin' awsome) after gunpowder, at least if you're under any sort of threat. As a result Musketmen dont' make a big footprint, which is a shame I think.
Machine-Guns are usually obsolete before you can even build them. In most games (well, just about any game I can think of) you are going to research assembly line before you get railroad. The choice seems pretty ovious as assembly line is at about the same area of the tech tree as railroad, and it provides you with Infantry and Factories/Coal Plants, which are all must-haves. Sure, railroad is pretty great too, but compaired to line it's hard to justify researching it first.
As a result you'll probably be able to build infantry before machine-guns (which doesn't really make that much sense). Sure, machine-guns have a decent edge in modified power over infantry, but not against any other units, and not enough to make up for the fact that they can't attack (in my opintion). Also, by this point tanks arn't that far away, which render machine-guns a complete waste of production. So in the end you are unlikely to get much milage out of machine-guns.
I feel like these sorts of problems would be worth Firaxis fixing, as I think just about all the units included in Civ4 were very useful in history, and I think that should be reflected in the game.