You need to extract the rar file (winzip should do it, or winrar if not) into the mod path directory. A quick step by step guide:
1) Launch game, go to options > mods > show mod path. It varies between XP/Vista.
2) Extract the mod to that path, and make sure "use folder names" is enabled (it should be by default).
3) Double check that it extracted properly, for example if a mod is called MyMod, the path needs to be ...\Ironclad Games\Sins of a Solar Empire\Mods\MyMod\, with various folders in MyMod, like GameInfo, String, Texture, etc based on the mod.
It should then show up in the Mods section in-game. Also, most big mods should include a small README with basic installation instructions if you want to double check.